Assumin data in ws2 is sorted on column A

Using a staging column in ws1 (columng G) with formula:
=COUNTIF(WS2!$A$1:$A$7,A1)

Using a staging empty row above data in ws1

Formula in ws1 B2 thru F4:
=IF(COLUMN()-COLUMN($A2)$G2;"",INDEX(ws2!$B$1:$B$7,SUM($G$1:$G 1)+COLUMN()-C
OLUMN($A2)))

See example: http://cjoint.com/?dytEdPOBqD

HTH
--
AP

"sa02000" a écrit
dans le message de
...

I have two worksheets -- Lets say ws1, ws2.

Data in ws1 looks like

column1
data1
data2
data3


Data in ws2 looks like

column1 column2
data1 value1
data1 value2
data1 value3
data2 value4
data2 value5
data3 value6
data3 value7
data3 value8
data3 value9

So now I would like to copy all the values in column2 of ws2
corresponding to each data row in ws1 and I would like to take
transpose while pasting these in ws1. So, the finished data looks like
this below....

Final data in ws1 looks like..
column1 column2 column3 column 4 column 5
data1 value1 value2 value3
data2 value4 value5
data3 value6 value7 value8 value9

I can do this manually by sorting ws2 by first column and copying the
rows for same data in column1 and then do paste special and transpose
data....but I have to do this for a list that is thousands of rows
long....

Any help is very appriciated.

Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile:
http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=526157