Merge Rows depending on value of the first column
 

Hi All,

i have worksheet with the following format

1111 abc
1111 xyz
1111 x12
1234 qwe

I need help to merge column b values depending on column A.If two rows
have the same value in column a then i would like to append the value
of the cells in column b to the first row as shown below.
So i need the above data to appear as

1111 abc xyz x12
1234 qwe

thanks
sp123


--
sp123
------------------------------------------------------------------------
sp123's Profile: http://www.excelforum.com/member.php...o&userid=31163
View this thread: http://www.excelforum.com/showthread...hreadid=534373

Merge Rows depending on value of the first column
 

Assume your data (with headers) looks like this:
Qty
Lib1
An 100A
An 101B
An 102C
An 103D
An 104E
Bn 105F
Cn 106G
Cn 107H
Cn 108J
Dn 109K

Create the Lib2 list of unique items in Lib1 with
Data Filter Advanced Filter Copy to another location
Unique Records only

Lib2
An 104E 103D 102C 101B 100A
Bn 105F
Cn 108J 107H 106G
Dn 109K

Name Lib1, Lib2, and Qty.
The first cell of Qty contains a space.
All formulas are in R1C1 ref style.
Create these addittional names:
bins Refers To: =ROW(INDEX(C1,1):INDEX(C1,ROWS(Lib1)))
reps Refers To: =MAX(COUNTIF(Lib1,Lib1))
colu Refers To: =COLUMN(INDEX(R1,1):INDEX(R1,reps))
Select the 5 cells next to Lib2, An
(5=reps=max repetitions in Lib1)
and enter this array formula:
=INDEX(Qty,LARGE(TRANSPOSE(IF(Lib1=Lib2 R,bins,0)),colu)+1)
With the fill handle, drag this set of cells down to fill the array.
Select A1 ref style to translate all to your preference.