How to Return a Comma Separated Value List to single Excel Cell based on Condition from Range
I have a table I need to look through and return the first row where every corresponding value in the second row is "Yes"
In my case this would mean: "TA", and "RA"
+-----+----+-----+
| TA | SA | RA |
+-----+----+-----+
| Yes | No | Yes |
+-----+----+-----+
Is there a way to combine these values into a single Excel cell separated by commas?
So that a single cell will contain "TA, RA"
What formula I've used is store all values into an array and then have that array write to adjacent cells all values with yes and then combine them with substitute function.
=IFERROR(INDEX($A$1:$C$1,1,IFERROR(SMALL(IF($A1:$A3="Yes",COLUMN($A1:$A3)),ROW(1:1)), "")), "")
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&D1)," ",", ")
This will turn:
+----+----+
| TA | RA |
+----+----+
into:
+--------+
| TA, RA |
+--------+
Is there a quicker/cleaner way to build this function to return all the values that match to "Yes" into a single cell separated by commas?
excel excel-formula
asked Nov 26 '18 at 22:26
William HumphriesWilliam Humphries
81110
add a comment |
I have a table I need to look through and return the first row where every corresponding value in the second row is "Yes"
In my case this would mean: "TA", and "RA"
+-----+----+-----+
| TA | SA | RA |
+-----+----+-----+
| Yes | No | Yes |
+-----+----+-----+
Is there a way to combine these values into a single Excel cell separated by commas?
So that a single cell will contain "TA, RA"
What formula I've used is store all values into an array and then have that array write to adjacent cells all values with yes and then combine them with substitute function.
=IFERROR(INDEX($A$1:$C$1,1,IFERROR(SMALL(IF($A1:$A3="Yes",COLUMN($A1:$A3)),ROW(1:1)), "")), "")
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&D1)," ",", ")
This will turn:
+----+----+
| TA | RA |
+----+----+
into:
+--------+
| TA, RA |
+--------+
Is there a quicker/cleaner way to build this function to return all the values that match to "Yes" into a single cell separated by commas?
excel excel-formula
asked Nov 26 '18 at 22:26
William HumphriesWilliam Humphries
81110
add a comment |
I have a table I need to look through and return the first row where every corresponding value in the second row is "Yes"
In my case this would mean: "TA", and "RA"
+-----+----+-----+
| TA | SA | RA |
+-----+----+-----+
| Yes | No | Yes |
+-----+----+-----+
Is there a way to combine these values into a single Excel cell separated by commas?
So that a single cell will contain "TA, RA"
What formula I've used is store all values into an array and then have that array write to adjacent cells all values with yes and then combine them with substitute function.
=IFERROR(INDEX($A$1:$C$1,1,IFERROR(SMALL(IF($A1:$A3="Yes",COLUMN($A1:$A3)),ROW(1:1)), "")), "")
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&D1)," ",", ")
This will turn:
+----+----+
| TA | RA |
+----+----+
into:
+--------+
| TA, RA |
+--------+
Is there a quicker/cleaner way to build this function to return all the values that match to "Yes" into a single cell separated by commas?
excel excel-formula
asked Nov 26 '18 at 22:26
William HumphriesWilliam Humphries
81110
I have a table I need to look through and return the first row where every corresponding value in the second row is "Yes"
In my case this would mean: "TA", and "RA"
+-----+----+-----+
| TA | SA | RA |
+-----+----+-----+
| Yes | No | Yes |
+-----+----+-----+
Is there a way to combine these values into a single Excel cell separated by commas?
So that a single cell will contain "TA, RA"
What formula I've used is store all values into an array and then have that array write to adjacent cells all values with yes and then combine them with substitute function.
=IFERROR(INDEX($A$1:$C$1,1,IFERROR(SMALL(IF($A1:$A3="Yes",COLUMN($A1:$A3)),ROW(1:1)), "")), "")
=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&D1)," ",", ")
This will turn:
+----+----+
| TA | RA |
+----+----+
into:
+--------+
| TA, RA |
+--------+
Is there a quicker/cleaner way to build this function to return all the values that match to "Yes" into a single cell separated by commas?
excel excel-formula
excel excel-formula
asked Nov 26 '18 at 22:26
William HumphriesWilliam Humphries
81110
asked Nov 26 '18 at 22:26
William HumphriesWilliam Humphries
81110
asked Nov 26 '18 at 22:26
William HumphriesWilliam Humphries
81110
asked Nov 26 '18 at 22:26
William HumphriesWilliam Humphries
81110
asked Nov 26 '18 at 22:26
William HumphriesWilliam Humphries
81110
81110
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Are you against a custom function?
Function LISTTHEYESES(valuerange As Range)
Dim cell As Range, resultstring As String
For Each cell In valuerange
If cell.Offset(1, 0).Value = "Yes" Then
If resultstring <> "" Then
resultstring = resultstring & ", " & cell.Value
Else
resultstring = cell.Value
End If
End If
Next cell
LISTTHEYESES = resultstring
End Function
answered Nov 26 '18 at 22:40
dwironydwirony
4,08431233
row1 = roles.Row row2 = valuerange.Row diff = row1 - row2; I added this to change the offset to reuse the function down the rows of data and subbed in for offset
– William Humphries
Nov 27 '18 at 16:17
add a comment |
If you have Excel 2016+, you can use the TEXTJOIN function in an array formula.
=TEXTJOIN(",",TRUE,IF(2:2="Yes",$1:$1,""))
To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.
answered Nov 27 '18 at 1:16
Ron RosenfeldRon Rosenfeld
23.4k41636
It appears I need a subscription to Office 365 to use this function even though it appears to provide a simple solution
– William Humphries
Nov 27 '18 at 14:59
@WilliamHumphries Yes. The initial stand-alone version of Excel 2016 did not have this function. I assume Excel 2019 has it, but don't know for sure.
– Ron Rosenfeld
Nov 27 '18 at 18:54
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53490050%2fhow-to-return-a-comma-separated-value-list-to-single-excel-cell-based-on-conditi%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Are you against a custom function?
Function LISTTHEYESES(valuerange As Range)
Dim cell As Range, resultstring As String
For Each cell In valuerange
If cell.Offset(1, 0).Value = "Yes" Then
If resultstring <> "" Then
resultstring = resultstring & ", " & cell.Value
Else
resultstring = cell.Value
End If
End If
Next cell
LISTTHEYESES = resultstring
End Function
answered Nov 26 '18 at 22:40
dwironydwirony
4,08431233
row1 = roles.Row row2 = valuerange.Row diff = row1 - row2; I added this to change the offset to reuse the function down the rows of data and subbed in for offset
– William Humphries
Nov 27 '18 at 16:17
add a comment |
Are you against a custom function?
Function LISTTHEYESES(valuerange As Range)
Dim cell As Range, resultstring As String
For Each cell In valuerange
If cell.Offset(1, 0).Value = "Yes" Then
If resultstring <> "" Then
resultstring = resultstring & ", " & cell.Value
Else
resultstring = cell.Value
End If
End If
Next cell
LISTTHEYESES = resultstring
End Function
answered Nov 26 '18 at 22:40
dwironydwirony
4,08431233
row1 = roles.Row row2 = valuerange.Row diff = row1 - row2; I added this to change the offset to reuse the function down the rows of data and subbed in for offset
– William Humphries
Nov 27 '18 at 16:17
add a comment |
Are you against a custom function?
Function LISTTHEYESES(valuerange As Range)
Dim cell As Range, resultstring As String
For Each cell In valuerange
If cell.Offset(1, 0).Value = "Yes" Then
If resultstring <> "" Then
resultstring = resultstring & ", " & cell.Value
Else
resultstring = cell.Value
End If
End If
Next cell
LISTTHEYESES = resultstring
End Function
answered Nov 26 '18 at 22:40
dwironydwirony
4,08431233
Are you against a custom function?
Function LISTTHEYESES(valuerange As Range)
Dim cell As Range, resultstring As String
For Each cell In valuerange
If cell.Offset(1, 0).Value = "Yes" Then
If resultstring <> "" Then
resultstring = resultstring & ", " & cell.Value
Else
resultstring = cell.Value
End If
End If
Next cell
LISTTHEYESES = resultstring
End Function
answered Nov 26 '18 at 22:40
dwironydwirony
4,08431233
answered Nov 26 '18 at 22:40
dwironydwirony
4,08431233
answered Nov 26 '18 at 22:40
dwironydwirony
4,08431233
answered Nov 26 '18 at 22:40
dwironydwirony
4,08431233
4,08431233
row1 = roles.Row row2 = valuerange.Row diff = row1 - row2; I added this to change the offset to reuse the function down the rows of data and subbed in for offset
– William Humphries
Nov 27 '18 at 16:17
add a comment |
row1 = roles.Row row2 = valuerange.Row diff = row1 - row2; I added this to change the offset to reuse the function down the rows of data and subbed in for offset
– William Humphries
Nov 27 '18 at 16:17
row1 = roles.Row row2 = valuerange.Row diff = row1 - row2; I added this to change the offset to reuse the function down the rows of data and subbed in for offset
– William Humphries
Nov 27 '18 at 16:17
row1 = roles.Row row2 = valuerange.Row diff = row1 - row2; I added this to change the offset to reuse the function down the rows of data and subbed in for offset
– William Humphries
Nov 27 '18 at 16:17
add a comment |
If you have Excel 2016+, you can use the TEXTJOIN function in an array formula.
=TEXTJOIN(",",TRUE,IF(2:2="Yes",$1:$1,""))
To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.
answered Nov 27 '18 at 1:16
Ron RosenfeldRon Rosenfeld
23.4k41636
It appears I need a subscription to Office 365 to use this function even though it appears to provide a simple solution
– William Humphries
Nov 27 '18 at 14:59
@WilliamHumphries Yes. The initial stand-alone version of Excel 2016 did not have this function. I assume Excel 2019 has it, but don't know for sure.
– Ron Rosenfeld
Nov 27 '18 at 18:54
add a comment |
If you have Excel 2016+, you can use the TEXTJOIN function in an array formula.
=TEXTJOIN(",",TRUE,IF(2:2="Yes",$1:$1,""))
To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.
answered Nov 27 '18 at 1:16
Ron RosenfeldRon Rosenfeld
23.4k41636
It appears I need a subscription to Office 365 to use this function even though it appears to provide a simple solution
– William Humphries
Nov 27 '18 at 14:59
@WilliamHumphries Yes. The initial stand-alone version of Excel 2016 did not have this function. I assume Excel 2019 has it, but don't know for sure.
– Ron Rosenfeld
Nov 27 '18 at 18:54
add a comment |
If you have Excel 2016+, you can use the TEXTJOIN function in an array formula.
=TEXTJOIN(",",TRUE,IF(2:2="Yes",$1:$1,""))
To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.
answered Nov 27 '18 at 1:16
Ron RosenfeldRon Rosenfeld
23.4k41636
If you have Excel 2016+, you can use the TEXTJOIN function in an array formula.
=TEXTJOIN(",",TRUE,IF(2:2="Yes",$1:$1,""))
To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.
answered Nov 27 '18 at 1:16
Ron RosenfeldRon Rosenfeld
23.4k41636
answered Nov 27 '18 at 1:16
Ron RosenfeldRon Rosenfeld
23.4k41636
answered Nov 27 '18 at 1:16
Ron RosenfeldRon Rosenfeld
23.4k41636
answered Nov 27 '18 at 1:16
Ron RosenfeldRon Rosenfeld
23.4k41636
23.4k41636
It appears I need a subscription to Office 365 to use this function even though it appears to provide a simple solution
– William Humphries
Nov 27 '18 at 14:59
@WilliamHumphries Yes. The initial stand-alone version of Excel 2016 did not have this function. I assume Excel 2019 has it, but don't know for sure.
– Ron Rosenfeld
Nov 27 '18 at 18:54
add a comment |
It appears I need a subscription to Office 365 to use this function even though it appears to provide a simple solution
– William Humphries
Nov 27 '18 at 14:59
@WilliamHumphries Yes. The initial stand-alone version of Excel 2016 did not have this function. I assume Excel 2019 has it, but don't know for sure.
– Ron Rosenfeld
Nov 27 '18 at 18:54
It appears I need a subscription to Office 365 to use this function even though it appears to provide a simple solution
– William Humphries
Nov 27 '18 at 14:59
It appears I need a subscription to Office 365 to use this function even though it appears to provide a simple solution
– William Humphries
Nov 27 '18 at 14:59
@WilliamHumphries Yes. The initial stand-alone version of Excel 2016 did not have this function. I assume Excel 2019 has it, but don't know for sure.
– Ron Rosenfeld
Nov 27 '18 at 18:54
@WilliamHumphries Yes. The initial stand-alone version of Excel 2016 did not have this function. I assume Excel 2019 has it, but don't know for sure.
– Ron Rosenfeld
Nov 27 '18 at 18:54
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53490050%2fhow-to-return-a-comma-separated-value-list-to-single-excel-cell-based-on-conditi%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
