Forum Discussion
Combining data in a new table
I have a table with products, prices and then quantity by customer - I would like to create a new table with the total price of the products by customer. I know I can create a million vlookups of the product quantity multiplied by a vlookup of the price but I know there must be a better way. So for example Column A is paper, staples, pen, paper clips Column B is prices $2.00, $0.50, $1,20, $0.25 then the following columns are customers - so customer X has 5 paper and 10 pens and the following customers are in the following columns. I want the new table to show customer X with $10, $0, $12.00 etc (so lookup customer quantity and multiply by price). I just need the suggestion on how to build this. Vlookups are too long - is there a command or function I should research that will help me make this? Thank you
- Although I think Power Query is more appropriate here. Add a MS Excel file (without sensitive data) to your project. Knowing the Excel version also be an advantage. So you could get a much more concrete solution than the one I sent. Thank you for your understanding and patience Nikolino I know I don't know anything (Socrates)
4 Replies
- Although I think Power Query is more appropriate here. Add a MS Excel file (without sensitive data) to your project. Knowing the Excel version also be an advantage. So you could get a much more concrete solution than the one I sent. Thank you for your understanding and patience Nikolino I know I don't know anything (Socrates)
NikolinoDE I think you are 100% correct - so I have created a sample table as the information is confidential - so the first column is the product - then the price per product, the shipping per product and the extra per product - then all the customers by quantity - what I need is to create a new table with Customer 1 - bananas cost, bananas shipping, bananas extra cost, then oranges cost, oranges shipping, oranges extra (so I have the actual prices per category by customer rather than numbers) -thank you
Hope I understood the translation correctly. Please look at the inserted file, is the result in the direction you want?
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
A noble offer from Mr.Sergei Baklan
can only recommend it :)))
https://techcommunity.microsoft.com/t5/excel/look-up-in-table-based-on-4-values/m-p/1839794#M79568
To continue tinkering yourself 🙂
XLOOKUP( D14, ($A$2:$A$9&$B$2:$B$9&$C$2:$C$9=A14&B14&C14)*$D$2:$D$9, $F$2:$F$9, "no such", -1
Hope it helped you
Nikolino
I know I don't know anything (Socrates)
