Multiple entries combined in one cell

**Barnapkin** · 02-06-2015, 12:04 AM

I am trying to lookup a value and return all of the assocuated information into one cell.
Each returned piece of data should be seperated by a comma.
I have gotten close to what I want, but it does not put every entry into the cell.
IF(COUNTIF(A:A,$C1)>=ROWS($1:1),INDEX(B:B,SMALL(IF(A:A=$C1,ROW($1:$999)),ROW())),"")
How do I concatenate each of the responses.
I have been using Ctrl+Shift+Enter to enter the cormulas
I am not looking for a VBA response.

As you can see belowI only get the value in cell B1 entered into cell D1.
I would like the answer to look like what is in cell D11

When copied into cell C2, I don't get the single response Thanks

MultipleCells.PNG

Test_Example.xlsx

**:) Sixthsense :)** · 02-06-2015, 12:20 AM

You can't achieve it using inbuilt worksheet functions.

You have to use UDF or Macro to get the expected result

**FDibbins** · 02-06-2015, 12:47 AM

THis will do what you want, but it is a bit messy...

	A	B	C	D	E	F	G	H	I
1	P2228	Fill	P2228-1	P2228	Fill, This, Way		Fill	This	Way
2	P2228	This	P2228-2	P2229	Thanks		Thanks
3	P2228	Way	P2228-3
4	P2229	Thanks	P2229-1

C1=A1&"-"&COUNTIF($A$1:A1,A1)
copied down
D1:D2 are your entries, I can give you a formula to extract them if needed

E1=SUBSTITUTE(TRIM(G1&" "&H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1&" "&N1&" "&O1)," ",", ")
copied down
G1=IFERROR(INDEX($B$1:$B$4,MATCH($D1&"-"&COLUMN(A1),$C$1:$C$4,0)),"")
copy this down as needed, and across (I had it copied 9 times)

Columns C and G onwards can be hidden if needced