CONCATENATE a column with values equal to X

[smd71092]

Hello all, I want to figure out how to have one cell that can concatenate all values that are equal to a column header above. Hard to explain accurately but I have attached a sample workbook.

I will be given a list of items in B2. I will need to neatly organize these into sentences in Row 3 based on their availability in Column B. I want to search the entire column B for a pre-specified set of rows (those rows corresponding to the items in B2 that I can manually update to match any given items). I want a concise or at least simple to follow formula that can comb through Column B and concatenate all responses that match the column headers in D4-H4.

This is a formula I tried in D3 that I thought would work but ultimately didn't: =CONCATENATE(IF({B4,B8,B11,B12,B19,B20,B21,B22,B23,B24,B25,B26,B27,B28}=D4,{B4,B8,B11,B12,B19,B20,B21,B22,B23,B24,B25,B26,B27,B28},""))

I want to be able to change the list B4,B8,B11,etc. to look for the specific items I want in the list. There is no pattern to the items or their names so I don't think there is a way to do this without inputting each cell manually which is fine, I just want them to be grouped as they are in D3-H3.

Appreciate any help!

[Special-K]

Why doesnt High Top sneakers and Shovels and Socks appear in D3 as well since they are also out of stock?
Same goes for Mid Top Sneakers, lotion, Universal remotes in low stock?
Havent checked the other columns

[smd71092]

I only want to return the values for those items listed in Cell B2 (low top sneakers, lotion, shampoo, etc.), not every item listed. Not sure if that is possible but that's what I am hoping for.

[smd71092]

If you are able to come up with a formula that does it for the entire column, that would be great as well. I can at least play around with it.

[Pete_UK]

Put this formula in C4:

=IF(B4="","-",IF(ISNUMBER(SEARCH(A4,$B$2)),LEFT(B4)&"_"&COUNTIF(B$4:B4,B4),"-"))

then copy down to the bottom of your data. Then you can have this formula in D3:

=IF(COUNTIF($C:$C,"*"&LEFT(D$4)&"*"),SUBSTITUTE(IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&1,$C:$C,0))&", ","")&IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&2,$C:$C,0))&", ","")&IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&3,$C:$C,0))&", ","")&IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&4,$C:$C,0))&", ","")&IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&5,$C:$C,0))&", ","")&IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&6,$C:$C,0))&", ","")&"x",", x","")&" are "&D$4,"")

Copy this across into E3:H3

Hope this helps.

Pete

[smd71092]

Shoot! This formula works perfectly for the exact data I have provided so thank you very much for that. I like the Column C especially since it helps get rid of the stuff I don't need to see for a particular set of items. However I forgot to mention that there are a lot more items in Column A than the 25 I have listed.

Also, it seems to only work for up to 6 items that may simultaneously be out of stock for example and it stops to work on the 7th. Is there anyway to concatenate the items in Column C more efficiently without having to iterate the same formula over 1000 times? I have updated the spreadsheet to show what I mean (Sample workbook v2). I have added jeans to the search as out of stock but it does not show up in D3.

[Pete_UK]

How many items in each category are you likely to have (i.e. maximum)? The formula in D3 will need to be adjusted to accommodate that number. Here's the formula with line-breaks inserted so that you can see the set-up more clearly:

=IF(COUNTIF($C:$C,"*"&LEFT(D$4)&"*"),SUBSTITUTE(
IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&1,$C:$C,0))&", ","")
&IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&2,$C:$C,0))&", ","")
&IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&3,$C:$C,0))&", ","")
&IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&4,$C:$C,0))&", ","")
&IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&5,$C:$C,0))&", ","")
&IFERROR(INDEX($A:$A,MATCH(LEFT(D$4)&"_"&6,$C:$C,0))&", ","")
&"x",", x","")&" are "&D$4,"")

Can you see how the middle parts are virtually identical, but just differ by the numbers in red? Those terms will have to be repeated but with 7, 8, 9, 10 etc. to accommodate more items.

Hope this helps.

Pete

[Pete_UK]

You could also change the formula in C4 to this:

=IF(B4="","-",IF(ISNUMBER(SEARCH(A4,$B$2)),B4&"_"&SUMPRODUCT((B$4:B4=B4)*(ISNUMBER(SEARCH($A$4:$A4,$B$2)))),"-"))

so that the numbers after the "Out of Stock" etc. only increment when the item is in the list in B2. This will now show Jeans in D3 without having to change the formula, as it is now the 4th Out of Stock item which is in the list.

Hope this helps.

Pete

[smd71092]

You could also change the formula in C4 to this:

=IF(B4="","-",IF(ISNUMBER(SEARCH(A4,$B$2)),B4&"_"&SUMPRODUCT((B$4:B4=B4)*(ISNUMBER(SEARCH($A$4:$A4,$B$2)))),"-"))

so that the numbers after the "Out of Stock" etc. only increment when the item is in the list in B2. This will now show Jeans in D3 without having to change the formula, as it is now the 4th Out of Stock item which is in the list.

Hope this helps.

Pete

I didn't necessarily use everything you provided but the helper in Column C you had initially provided was fantastic and led to what I think is a finished product!

Someone else added another helper in Column J and it looks like I just need to adjust the index and match ranges to the end of the data set and it should work. I added Forks to the data set and to B2 (the set of items to search for) and made it "Full Stock" as a test and it works. Thank you so much Pete! Without your help I would not have been able to complete this. I truly appreciate it. Is there a way to thank you on this forum? Give you credit or something for a correct answer.

[Pete_UK]

Glad to be of help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile), and you can add a little private note to that if you like.

Pete