CODEWITHSUNDEEP
excelconcatenation
Julia
Julia

Reputation: 1

Concatenate a number of columns based on a range in Excel

All,

I am trying to concatenate a range (range 1) in case any cell in the range contains the string "yes" . In case range1 has multiple hits (multiple cells with "yes"; I would like to show all hits within one output cell (best: comma seperated).

So far I tried index and match, but this will only return the first value found. I would like to have all hits within one cell, comma seperated. 

=IFERROR(INDEX(range1;MATCH(Yes"&"*";range1;0));"")

Range1 consists of 8 coloumns and one row. Only cells with the word "yes" should be concatenated.

Any idea?

Thanks Julia

Excel version 2013

Upvotes: 0

Views: 674

Answers (1)

virtualdvid
virtualdvid

Reputation: 2411

With formula I found this:

Using into CONCATENATE an IF(ISNUMBER(SEARCH("yes",A2)),A2&", "," ") for each cell in your range1. It will be something like:

=TRIM(CONCATENATE(IF(ISNUMBER(SEARCH("yes",A2)),A2&", "," "),IF(ISNUMBER(SEARCH("yes",B2)),B2&", "," "),IF(ISNUMBER(SEARCH("yes",C2)),C2&", "),IF(ISNUMBER(SEARCH("yes",D2)),D2&", "," "),IF(ISNUMBER(SEARCH("yes",E2)),E2&", "," "),IF(ISNUMBER(SEARCH("yes",F2)),F2&", "," "),IF(ISNUMBER(SEARCH("yes",G2)),G2&", "," "),IF(ISNUMBER(SEARCH("yes",H2)),H2&", "," ")))

Upvotes: 1

Related Questions