Forum Discussion
Jon Cooke
Mar 15, 2018Copper Contributor
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...
Jon Cooke
Mar 15, 2018Copper Contributor
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
SergeiBaklan
Mar 15, 2018Diamond Contributor
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.