How to map cells to a reference then copy adjacent ones

[Dream]

Hi all,
I appreciate your help on the following. Please refer to teh example at the
bottom.
in the first sheet i have the following,
A 1 20
B 3 30 60
C 5 10 20

in the second sheet i have,
1 2 3 4 5 6 7 8 9

i want excel to distribute the information above into the second sheet, so
that the result will be in sheet 2 to be as
1 2 3 4 5 6 7 8
9
project A 20
project B 30 60
project C 10 20

please note that the idea is that for each project in sheet1, copy the
entire row to sheet2 starting from the location where the numbers in the
second column match the numbers in sheet2.

For example, for project A, the number is 1, so copy the entire row (in this
case 20) to the location under 1 in sheet2.

for project B, go to coloumn 3 in sheet 2, and copy the entire row (in this
case 2 cells 30 and 60) into adjacent cells in sheet2 starting at column 3,
so that 30 will reside in column3, and 60 will reside in the column next to
it which is column 4...and so forth...

Thanks in advance

 

[Jacob Skaria]

Hi Dream

Try the below

With Sheet1 as below

Col A Col B Col C Col D Col E Col F
Project A 1 20 30
Project B 3 30 60 70 80
Project C 5 10 20


and Sheet 2 as below

Col A Col B Col C Col D Col E Col F ...
1 2 3 4 5...
Project A 20 30
Project B 30 60 70...
Project C 10...
Project D

--Sheet2 Row 1 extends upto (Col J upto 9)
--The index or project name in both sheets should be exactly same. In your
example it is 'A' and 'Project A'. Both should be same as above.
--In Sheet2 cell B2 apply the below formula and copy across to Col J and
copy down as required

=IF(ISNA(VLOOKUP($A2,Sheet1!$A:$K,2,0)),"",IF(VLOOKUP($A2,Sheet1!$A:$K,2,0)>B$1,"",IF(VLOOKUP($A2,Sheet1!$A:$K,2,0)=B$1,VLOOKUP($A2,Sheet1!$A:$K,3,0),IF(VLOOKUP($A2,Sheet1!$A:$K,B$1-VLOOKUP($A2,Sheet1!$A:$K,2,0)+3,0)=0,"",VLOOKUP($A2,Sheet1!$A:$K,B$1-VLOOKUP($A2,Sheet1!$A:$K,2,0)+3,0)))))


If this post helps click Yes

 

[Dream]

Thanks alot Jacob this really works!
But how can I apply it to more than 9 columns...becuase I am trying to make
it extend to across around 100 columns or more...Thanks in advanvce

 

[Jacob Skaria]

You can. Change the array reference in the formula Sheet1!$A:$K to
whatever..Say Sheet1!$A1:$CV100. Try and feedback

If this post helps click Yes

 

[Dream]

It worked! Thanks Jacob, you are the best

You can. Change the array reference in the formula Sheet1!$A:$K to
whatever..Say Sheet1!$A1:$CV100. Try and feedback

If this post helps click Yes