Combine the Sumif function with concatenate [closed]

Is there a way to concatenate a string of text cells if, lets say, the value above each cell is "yes"?

For example:

``````yes    yes
dog    cat   bird   hen
``````

In this case, the concatenated string result would be:

dog, cat

closed as unclear what you're asking by fixer1234, DavidPostill♦, mdpc, nKn, G-ManDec 16 '16 at 7:33

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

• Your comment on the answer makes the question unclear. Where, exactly, are the yes's? Are these pairs of rows, and the 1st in each pair has yes for the cells in the 2nd row of the pair you want to concatenate, or row 1 of the array has the yes's, and you want to concatenate associated cells from each subsequent row? – fixer1234 Dec 9 '16 at 17:14

This would require an IF statement, which is a valid expression in an Excel formula.

The syntax is:

``````IF( condition, [value_if_true], [value_if_false] )
``````

The formula would probably look like this:

``````IF(ISNUMBER(SEARCH("Yes",A1)), CONCATENATE(A2, " ", B2, " ", C2))
``````

Note: this only checks for one Yes in cell A1 and then concatenates all of the cells. If you wish to concatenate only the cells under the ones with "Yes", you'll need to extend this into a macro or use a couple dependent formulas in different cells.

References:

UPDATE: For concerns about macro use in large number of values

A macro would work for that. If the number of cells in your data rows can vary and you need to cycle through each cell with information, there are multiple ways of determining the last cell in a row (or column) with data and then feeding that back elsewhere in the macro.

Given Yes/blank in Row A and data in Row B, I'm thinking you could do this in three steps:

1. Get the last cell with "Yes" in Row A: Use Find on the range of Row A.

2. Get the value from the Find in step 1 and use this to loop from Cell A1 to cell A(value from Find) and record the cell numbers for each "Yes" found.

3. Take those numbers and concatenate each cell in row B with those numbers from step 2.

You could shorten this to two steps by merging steps 2 and 3, just pulling and concatenating the values from B each time you find a Yes in A.

Unfortunately, I'm not a genius in VBA, and have only actually written out effective macros based on weeks of effort and lots of forum posts, so I'm not even going to try to write this out. But each component step should be easy enough to research and then assembly can be according to your own preferences.

• Thank you for your response. A macro will not work as I will have an infinite amount of rows that the desired formula will be copied to. What I need is an equation that does exactly what you state "I wish to concatenate only the cells that have the value "yes" above them" – Dan Stewart Dec 9 '16 at 13:20
• A macro would still work even given a large (and/or growing) number of used cells, see the edited answer: – music2myear Dec 9 '16 at 17:00