return multiple header cell values into one cell concatenated based on a cell value

**anchuri_chaitanya** · 09-11-2016, 03:22 PM

Dear Experts / Members,

I have a data set where in based on a cell value it should return the multiple headers values into one cell concatenated with comma. I have gone through many posts, but couldn't able to find what i required. Please find attached the image on my requirement for better understanding.

Untitled.png

Thanks in advance

**azumi** · 09-11-2016, 03:50 PM

The results can't be done in 1 cell, if multiple cell yes possible

=IFERROR(INDEX($A$1:$D$1;SMALL(IF($A$2:$D$2=$E2;COLUMN($A$1:$D$1)-COLUMN($A$1)+1);COLUMNS($A$1:A1)));"")

put this in cell F2 and push F@ button or doble click to open the formula, and then press all together CTRL+SHIFT+ENTER button, do not ENTER alone, if succed will show curly bracket "{}" that enclosed the formula, last step copied cross and down the formula as necessary

**anchuri_chaitanya** · 09-11-2016, 11:03 PM

Hi Azumi,

Thanks for the formula, its great.
I understand that as per your statement its not possible in one cell, but, my concern is to get in one cell. IF using the given formula and dragging to down cannot make my work. There were many rows like row B to its down. I have given just one row example. Could you please help any alternate. I tried dragging to right instead of down if it happens, then thought i can concatenate those cells into one as per the output, but no luck in dragging to right.

**Phuocam** · 09-12-2016, 12:48 AM

Try this ...

Please Login or Register  to view this content.

Use: =MyConCat(A2:D2,E2,$A$1:$D$1)

**anchuri_chaitanya** · 09-12-2016, 02:11 AM

Hi Phuocam,

The code is awesome. Worked really perfect. Thanks a lot.
Small query, instead of the coding is there a way by formula.

**JeteMc** · 09-13-2016, 06:20 PM

Formula solution. Use Azumi's array entered formula to create helper columns (H2:K2 in attached file) which may be hidden for aesthetic purposes. Paste the following formula in F2:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

**Teethless mama** · 09-13-2016, 10:27 PM

Try this:

F2: =SUBSTITUTE(TRIM(IF(A2=E2,A$1&" ","")&IF(B2=E2,B$1&" ","")&IF(C2=E2,C$1&" ","")&IF(D2=E2,D$1,""))," ",", ")

return multiple header cell values into one cell concatenated based on a cell value

LinkBack

Thread Tools

Rate This Thread

Display

return multiple header cell values into one cell concatenated based on a cell value

Re: return multiple header cell values into one cell concatenated based on a cell value

Re: return multiple header cell values into one cell concatenated based on a cell value

Re: return multiple header cell values into one cell concatenated based on a cell value

Re: return multiple header cell values into one cell concatenated based on a cell value

Re: return multiple header cell values into one cell concatenated based on a cell value

Re: return multiple header cell values into one cell concatenated based on a cell value

Thread Information

Users Browsing this Thread

Similar Threads

Return Cell Value based on Values in Multiple Columns

Lookup and return multiple values concatenated into one cell

[SOLVED] Value of cell based on concatenated values

Lookup multiple values concatenated into one cell without duplicates

[SOLVED] Match multiple substrings in a cell against a lookup table and return concatenated values

[SOLVED] How to display multiple values concatenated in 1 cell without double entry of result value

Excel 2007 : Formula that will return a value based on the values in multiple cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions