Forum Discussion
Help with vlookup
Hi
I'm not sure how to explain what i'm trying to achieve or the correct terminology so google has not been my friend . So I need to take date from Tab one that is in rows and pivot some infor...
Thanks for that , although I may not have explained myself , on the second tab i want to be able to copy the Identifier and the 4 rows of data and then copy the same data for each row .
so if i have an identifier in column B of Tab 1
| A4DEA |
| AC1420 |
| AC1449 |
| AC1475 |
| AC1510 |
Then Tab 2 should look like this as I copy the cells down
| A4DEA |
| A4DEA |
| A4DEA |
| A4DEA |
| AC1420 |
| AC1420 |
| AC1420 |
| AC1420 |
| AC1449 |
| AC1449 |
| AC1449 |
| AC1449 |
But if I copy AC1449 down it will jump 4 rows and return AC1522 rather than AC1475
With formulas only we may put in second row
for the identifier
=OFFSET('PRODUCT DIMS '!$B$3,INT((ROW()-1-0.1)/4),0)
for ID
=CHOOSE(MOD(ROW()-2,4)+1,"CartonLength","CartonWidth","CartonHeight","CartonWeight")
for Value
=OFFSET('PRODUCT DIMS '!$B$3,INT((ROW()-1-0.1)/4),CHOOSE(MOD(ROW()-1-1,4)+1,9,11,13,5)-1)
and drag all 3 cells down.
Formulas could be adjusted a bit with cosmetic and making them less dependent from starting row.
Next block in attached.