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

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

Thanks Ardus. It is a good solution although only problem I have is that
although ws1 and ws2 are sorted by column1 but they are not the same
list....ws1 does not have some "data" values that exist in ws2. Also,
ws1 is sorted using other columns to be able to use this data....... so
using this formula the rows are mismatched and return wrong value. And
an active formula like this will give wrong results when sorting ws1
any other way. Any other solution or workaround to this?

Thanks, Jay


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

Hi, sa

Your original post stated that ws1 contained data!

Now, you need a VBA macro.

Cheers,
--
AP

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

Thanks Ardus. It is a good solution although only problem I have is that
although ws1 and ws2 are sorted by column1 but they are not the same
list....ws1 does not have some "data" values that exist in ws2. Also,
ws1 is sorted using other columns to be able to use this data....... so
using this formula the rows are mismatched and return wrong value. And
an active formula like this will give wrong results when sorting ws1
any other way. Any other solution or workaround to this?

Thanks, Jay


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