Concatenate cell values based on condition

**alexandruc** · 04-06-2012, 02:05 AM

Hi,

I need to concatenate values of cells from another sheet based on a certain condition.

I found on this forum the following macro:

Please Login or Register  to view this content.

so I set up in the centralizing sheet this formula that on drag down can concatenate values from different sheets:

=Conc(INDIRECT("'"&ROW()-2&"'!$A$"&MATCH("Key Word";INDIRECT("'"&MAX(ROW()-2)&"'!$A$1:$A$200");0)+1):INDIRECT("'"&ROW()-2&"'!$A$130");", ")

I use ROW()-2 to target the sheet so that by dragging the formula down it will get data from the next sheet (formula is placed on row 3).
I use MATCH to get the starting value of the Range from witch the values will be concatenated (starting position may be different from sheet to sheet).

What I don't know how to do, is to concatenate only the values that have in an adjoining cell (2nd cell from value to be concatenated) a certain value.

I attached a sample document, that better explains what I need to do.
sample.xlsm

I would appreciate any help.

Thank you

**Paul** · 04-06-2012, 04:50 AM

Hi Alexandruc,

Adjust your code slightly to:

Please Login or Register  to view this content.

And then use this updated formula (change in red):

=Conc(INDIRECT("'"&ROW()-2&"'!$A$"&MATCH("key word",INDIRECT("'"&MAX(ROW()-2)&"'!$A$1:$A$200"),0)+1):INDIRECT("'"&ROW()-2&"'!$A$130"),"get",", ")

I changed your variable "cell" to "ce". Just one of those coding things - try not to reference actual objects even though a cell is considered a range variable. Just my 2 cents.