Text from many row to one column

[felzao]

How to convert text from row to column.

for number is no problem. i Used VLOOKUP and SUMPRODUCT i.e VLOOKUP(F2,$F$2:$H$60,2,0) and (SUMPRODUCT(--(MAIN!$Q$6:$Q$500=F2),--((MAIN!$N$6:$N$500))))

Any suggestion how to solve my probem

example:

AAA JOHN
BBB ANDREW
CCC ALBERT
BBB XAVIER
AAA ABDUL
DDD KADIR

the answer is...

AAA JOHN, ABDUL
BBB ANDREW, XAVIER
CCC ALBERT
DDD KADIR

tks

[Richard Buttrey]

How to convert text from row to column.

for number is no problem. i Used VLOOKUP and SUMPRODUCT i.e VLOOKUP(F2,$F$2:$H$60,2,0) and (SUMPRODUCT(--(MAIN!$Q$6:$Q$500=F2),--((MAIN!$N$6:$N$500))))

Any suggestion how to solve my probem

example:

AAA JOHN
BBB ANDREW
CCC ALBERT
BBB XAVIER
AAA ABDUL
DDD KADIR

the answer is...

AAA JOHN, ABDUL
BBB ANDREW, XAVIER
CCC ALBERT
DDD KADIR

tks

One way use two helper columns. With data above in A2:B7, first sort it ascending with col. A as the key. Then in C2 enter

Please Login or Register  to view this content.

and in D2 enter

Please Login or Register  to view this content.

Copy C2:D2 down your list.
Extract a unique list of values from col A in A10:A13, then use a VLOOKUP in B10 and copy down to B13.

Please Login or Register  to view this content.

There are probably other function methods and a VBA macro would easily do it.

Rgds

[felzao]

One way use two helper columns. With data above in A2:B7, first sort it ascending with col. A as the key. Then in C2 enter

Please Login or Register  to view this content.

and in D2 enter

Please Login or Register  to view this content.

Copy C2:D2 down your list.
Extract a unique list of values from col A in A10:A13, then use a VLOOKUP in B10 and copy down to B13.

Please Login or Register  to view this content.

There are probably other function methods and a VBA macro would easily do it.

Rgds

thanks Richard..u re good man..love u..my problems is solved but i forgot to mentioned earlier..at the same senario how to create text to become one not duplicate.

AAA JOHN
BBB ANDREW
CCC ALBERT
BBB ANDREW
AAA ABDUL
DDD KADIR

the answer is...

AAA JOHN, ABDUL
BBB ANDREW
CCC ALBERT
DDD KADIR


BBB -- the answer is ANDREW i/o ANDREW, ANDREW

[Richard Buttrey]

thanks Richard..u re good man..love u..my problems is solved but i forgot to mentioned earlier..at the same senario how to create text to become one not duplicate.

AAA JOHN
BBB ANDREW
CCC ALBERT
BBB ANDREW
AAA ABDUL
DDD KADIR

the answer is...

AAA JOHN, ABDUL
BBB ANDREW
CCC ALBERT
DDD KADIR


BBB -- the answer is ANDREW i/o ANDREW, ANDREW

Hi,

Just replace the previous formula I gave you for C2 with this modification:

Please Login or Register  to view this content.

Then continue as before.

Rgds

[felzao]

Thanks for your answer... but my data can't get the result if B have many..
the details as belows... Please help me.. plse open the attachment





C =IF((A2<>A1),IF(B2=B1,C1&", "&A2,A2),"")
D = IF(COUNTIF(B2:B$20,B1)>1,"",C2)
F (answer) = =VLOOKUP(E2,$B$2:$D$20,3]

[felzao]

Anybody Can Help Me....