Help with vlookup

Question

Hi&nbsp;
&nbsp;
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 information from row to column . so I need 4 identical part numbers and information for these parts . I can do the Vlookup but if i try to copy the data down the sheet it skips every other 3 part numbers ,&nbsp;
&nbsp;
hopefully i have explained my issue and any help greatly appreciated&nbsp;
&nbsp;

sergeibaklan · Answer

Could be like
=VLOOKUP(A2,'PRODUCT DIMS '!$B$3:$W$862,CHOOSE(MOD(ROW()-1-1,4)+1,9,11,13,5),FALSE)
&nbsp;

jon cooke · Answer

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 .&nbsp;
&nbsp;
so if i have an identifier in column B of Tab 1

A4DEA

AC1420

AC1449

AC1475

AC1510

&nbsp;
Then Tab 2 should look like this as I copy the cells down&nbsp;

A4DEA

AC1420

AC1449

But if I copy AC1449 down it will jump 4 rows and return AC1522 rather than AC1475&nbsp;

sergeibaklan · Answer

Hi Jon,
&nbsp;
Okay, I'll try. However, the easiest way is to use Power Query (aka Get&amp;Transform in 2016) to receive result in few clicks. The only point is to convert the range in Tab1 into the table.
&nbsp;
Query done with User Interface only is
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product.............", type text}, {"Column1", type text}, {"Description...................", type text}, {"Sin/Inn", Int64.Type}, {"Sin/Outer", Int64.Type}, {"Weight", type number}, {"Sin/Pallet", Int64.Type}, {"Volume", type number}, {"OUTERS LENGTH", Int64.Type}, {"Column2", type number}, {"OUTERS WIDTH", Int64.Type}, {"Column3", type number}, {"OUTERS HEIGHT", Int64.Type}, {"height", type number}, {"INNERS LENGTH", Int64.Type}, {"INNERS WIDTH", Int64.Type}, {"INNERS HEIGHT", Int64.Type}, {"OUTER TI", Int64.Type}, {"OUTER HI", Int64.Type}, {"INNER TI", Int64.Type}, {"INNER HI", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Product............."] &lt;&gt; null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Product.............", "Weight", "Column2", "Column3", "height"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Product.............", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Product..............1", "Product..............2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Product..............1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Weight", "CartonWeight"}, {"Column2", "CartonLength"}, {"Column3", "CartonWidght"}, {"height", "CartonHeight"}, {"Product..............2", "Product"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Product", "CartonLength", "CartonWidght", "CartonHeight", "CartonWeight"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Product"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "CustomFieldID"}, {"Value", "CustomValue"}})
in
    #"Renamed Columns1"
and in attached.
&nbsp;

jon cooke · Answer

Wow , that is amazing , Now all i have to do is learn how you do it but . Thank you so much . I will now work backwards and learn from this&nbsp;&nbsp;

sergeibaklan · Answer

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.
&nbsp;
Next block in attached.

Forum Discussion

Help with vlookup

5 Replies

Resources