Combine columns

[Mikevr]

Col A is the material #
Col B -G is the warehouse number and the data is the quantity sold
i want to come up with
Col A material #
Col B warehouse number
Col C qty sold
how do I do this?
thanks

 

[Pavan Kumar Sistla]

Dear Mikevr,

Please use Power Query to transform the data. If I understand right, the columns from B to G have Warehouse numbers below which you have quantities. So, go to Data and click From Table/Range which will take you to Power Query window and from there in Transform menu, you can unpivot other columns by selecting columns from B to G and then load as a table. I have attached herewith a sample excel for your easier reference please.

Thanks,
Pavan.

 

[Mikevr]

Thanks! That worked
now I have that data in yellow ( from the power query) and I need to vlookup it to the data in Col A-C how do I do that to incorporate the different whose/qty

 

[Pavan Kumar Sistla]

You are welcome. Please find the attached sample 2. You have to concatenate Material no. & Whose number to get a unique identifier with which you have to lookup for the value from the yellow table. Please refer to the attached excel.

Thanks,
Pavan.

 

[GraH - Guido]

Strange, first using PQ and then .... do a VLOOKUP? Why not do the merge in PQ on both columns?
worked further on the sample given by Pavan.

 

[Peter Bartholomew]

It is also possible to perform multi-column lookups using
= LOOKUP( 1, 1 / ( Table2[Material Number] = [@MaterialNumber] ) / ( Table2[Whouse]=[@Whouse] ), Table2[Value] )
or more recently
= XLOOKUP( 1, ( Table2[Material Number]=[@MaterialNumber] ) * ( Table2[Whouse]=[@Whouse] ), Table2[Value] )