• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combine columns

Mikevr

New Member
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
 

Attachments

  • 8B492D07-FCA8-4DEF-B3EC-3A86EC38E388.jpeg
    8B492D07-FCA8-4DEF-B3EC-3A86EC38E388.jpeg
    33 KB · Views: 11
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.
 

Attachments

  • Sample 1.xlsx
    17.3 KB · Views: 7
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/qtyB1286E79-1D18-447A-A7A1-DA3E944FA4B2.jpeg
 
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.
 

Attachments

  • Sample 2.xlsx
    9.2 KB · Views: 6
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.
 

Attachments

  • Copy of Sample 2.xlsx
    18.4 KB · Views: 8
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] )
 
Back
Top