conditional concatenate multiple values from row above

[mholland]

so, I have what I imagine to be a simple and [I'm thinking what must be a] frequent problem ... I have two rows, one on top of the other ... top row is types of fruit [one per cell], lower row is simply "Y" or "N" depending on whether is like the fruit or not.

what i want to do is, on a different tab in the same spreadsheet, in single cell, display all the fruits I like, separated by a comma [apples, pears, peaches].

I need to do this by formula, rather than by macro, just to make it more difficult ... I have tried hlookups, index/match, sumif, advanced filter, and I still can't get it. I think there is something in the concept of an array entered something like "if(lower_range='y', upper_range,)", but i can't get any further than the first fruit i like.

thanks in advance, any help appreciated, Matt

 

[T. Valko]

Download and install the free add-in Morefunc.xll from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternative download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

This method is limited to a max of 255 characters that the formula will return!

Using the MCONCAT function from the add-in...

A1:E1 = fruit names
A2:E2 = Y or N

Array entered**

=SUBSTITUTE(TRIM(MCONCAT(IF(A2:E2="Y",A1:E1,"")&" "))," ",",")

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[Andy B]

The following method is not as elegant but does not require a download. There is a still limit on characters which varies with the data but is over 1000.

It requires a helper row, in the example below copy the formula in a4 across (it does not matter if it is copied beyond the end of the data).
Then reference cell A4 in your second sheet.

Excel Workbook
	A	B	C	D	E
1	Apple	Pear	Grape	Orange	Banana
2	Y	Y	N	N	Y
3
4	Apple,Pear,Banana	Pear,Banana	Banana	Banana	Banana
Sheet1

Excel Workbook
	A
1	Apple,Pear,Banana
Sheet2

 

[mholland]

love it, love it ... much thanks ... I'm going to go with a little variant on the helper row doo-dah, as I can't download [spready being run by hundreds of people on locked down pc's]

so in my final view, each column in the helper row will look for a 'Y', and add the fruit to the contents of the previous helper row cell ... repeat this formula right to left, then link to my final cell to present the fully concat'ed string.

you're both stars, well done indeed.

 

[barry houdini]

You could also avoid any helper cells by concatenating several IFs, e.g.

=SUBSTITUTE(IF(A2="Y",", "&A1,"")&IF(B2="Y",", "&B1,"")&IF(C2="Y",", "&C1,"")&IF(D2="Y",", "&D1,"")&IF(E2="Y",", "&E1,""),", ","",1)

 

[Andy B]

I'm going to go with a little variant on the helper row doo-dah

Be careful if you try to simplify the formula I gave, the formula I gave ensures there is no comma at the end of the final string, including when the rightmost fruits contain a 'N'. If all you do is check for 'Y' you will get the end comma or zero's!

 

[mholland]

Thanks AndyB ... I reread your formula, and realised you had already produced exactly what I thought I was going to do ... apologies.

I still think there is something possible using arrays, but this 'helper row-if then' solution will be more than adequate

thanks again all