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 here is a picture of a sample workbook. Imgur: The most awesome images on the Internet

The data is setup in a certain way so I want to account for that. I want to search the entire column B for a pre-specified set of rows (I have identified them here as Item1, Item2, etc.). 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-G4.

Formula I thought would work: =CONCATENATE(IF({B4,B8,B11,B12,B17,B18,B19,B20,B21}=D4,{B4,B8,B11,B12,B17,B18,B19,B20,B21},"")

I want to be able to change the list B4,B8,B11,etc. to look for the specific items I want in the list that aren't contiguous but the D4-G4 would remain in place and the formula should be right above D4-G4 if that matters since there is a bunch of other stuff below it.

Appreciate any help!

 

[Rick Rothstein]

Can you explain the items whose names show as "blah" with meet the match criteria but are not included in your concatenated list? How do we differentiate been an item that is to be included and ones that are not to be included?

Here is something I have posted in the past (in particular, note the underlined text) which might help you in answering my questions above as well as future questions you may ask...

Please Note
-------------------
For future questions you may ask, please do not simplify your question for us... doing so will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[smd71092]

Can you explain the items whose names show as "blah" with meet the match criteria but are not included in your concatenated list? How do we differentiate been an item that is to be included and ones that are not to be included?

Here is something I have posted in the past (in particular, note the underlined text) which might help you in answering my questions above as well as future questions you may ask...

Please Note
-------------------
For future questions you may ask, please do not simplify your question for us... doing so will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

Hi sorry, I thought there was enough information available but I apologize since I was not as clear about the "blah" items. There is no easy way to distinguish between them so that's why I didn't mind if I had to input each item individually into the formula (see my attempted formula where I input B4,B8,B11,etc.), and there is only a specific set of items that I want to look at. I just want a formula that I can drag to the right that will essentially group that exact set of items locked in ((B4,B8,B11,etc. which I will manually input into the formula ) according to the values in D4-G4. Hope that clarifies a bit and thank you for bringing this to my attention.

 

[smd71092]

Anyone able to provide some advice on this? I have an updated and unaltered example if this helps. Sorry for trying to simplify it before but this is sanitized just enough to hopefully leave enough information. Imgur: The most awesome images on the Internet

Here are some updated instructions to reflect the updated image.

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). I want a concise or at least simple to follow formula that can comb through Column B and concatenate all items 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. in the formula above 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. I am fine with that, I just want them to be grouped as they are in D3-H3. Hope this makes sense this time.

Thanks again!

 

[Rick Rothstein]

I think this UDF (user defined function) will do what you want...

[table="width: 500"]
[tr]
	[td]Function ItemAvailability(Availability As String) As String
   Dim R As Long, Data As Variant
   Data = Range("A4", Cells(Rows.Count, "B").End(xlUp))
   For R = 1 To UBound(Data)
     If LCase(Data(R, 2)) = LCase(Availability) And InStr(1, Range("B2").Value, Data(R, 1), vbTextCompare) Then ItemAvailability = ItemAvailability & ", " & Data(R, 1)
   Next
   ItemAvailability = UCase(Mid(ItemAvailability, 3, 1)) & LCase(Mid(ItemAvailability, 4)) & " are " & Availability
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ItemAvailability just like it was a built-in Excel function. For example, put this formula in cell D3 (the argument D4 is the cell containing the availability text to search on) and copy it across to cell H3...

=ItemAvailability(D4)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[smd71092]

Sorry I am unable to use macros on any of my files due to security concerns from clients.

If an alternative using just formulas isn't available I guess I can transfer the file to my personal laptop to use the macro, then remove the macro and resend it to my workstation. Thank you for your help so far.

 

[bosco_yip]

Hi,

A formula way to concatenate data with helper column.

1] Based on your layout, add one un-used helper Column J

2] In Helper J4, formula copy down to J28 :

=A4&IFERROR(", "&INDEX(J5:J$30,MATCH(B4,B5:B$30,0)),"")

3] Concatenate result D3, formula copy across to H3 :

=INDEX($J$4:$J$28,MATCH(D4,$B$4:$B$28,0))

Regards
Bosco