| Product | Price |
|---|---|
| book | 10 |
| toy | 25 |
| bag | 40 |
| Order_ID | line items | Total |
|---|---|---|
| 3003 | book - red,toy - red,bag - blue | |
| 3004 | toy - blue | |
| 3005 | bag - yellow,toy - red |
I have the two tables above. The first one is a product list and the 2nd one order list. I need to calculate order total. What is the good formula for doing this?
The only way I can think of is to define column "line items" as named range line_items and then make some mid-way sheet such as
| - | - | - |
|---|---|---|
| book (=ARRAYFORMULA(REGEXEXTRACT(SPLIT(index(line_items,row(),1),","),"\S "))) | toy | bag |
| toy (=ARRAYFORMULA(REGEXEXTRACT(SPLIT(index(line_items,row(),1),","),"\S "))) | ||
| bag (=ARRAYFORMULA(REGEXEXTRACT(SPLIT(index(line_items,row(),1),","),"\S "))) | toy |
and
| Total | - | - | - |
|---|---|---|---|
| =SUM(B2:2) | 10(using VLOOKUP to get price) | 25 | 40 |
| =SUM(B3:3) | 25 | ||
| =SUM(B4:4) | 40 | 25 |
then I can get the total from the 2nd mid-way sheet.
Is there any better way to do this using formula only? Maybe using query?
CodePudding user response：
Here is a fully self contained formula that will do this for you:
=ArrayFormula(MMULT(n(ARRAYFORMULA(iferror(VLOOKUP(iferror(arrayformula(left(split(B8:B10,","), arrayformula(find(" - ",split(B8:B10,","))-1)))),$A$1:$B$4, 2,)))),(transpose(COLUMN(indirect("A1:"&ADDRESS(1,COLUMNS(iferror(arrayformula(left(split(B8:B10,","), arrayformula(find(" - ",split(B8:B10,","))-1))))))))^0))))
You just need to change and define the array of orders, and the array for the lookup table. In my formula, $A$1:$B$4 is the lookup table, and B8:B10 is the array of orders.
The very first thing I did was split the items by comma:
split(B8:B10,",")
This split them up to be in the format of [item] - [color], each in their own cell horizontally.
Then, I had to get the actual item from this. To do this, I used the FIND() formula.
find(" - ",split(B8:B10,","))-1
This gives me which character a certain string starts at. I subtracted one to get the last character of the item name.
I then combined this with the LEFT() function.
=iferror(arrayformula(left(split(B8:B10,","), arrayformula(find(" - ",split(B8:B10,","))-1))))
This takes a given number of characters from a string starting on the left hand side. By combining this with the FIND() function, I am able to extract the exact number of characters that the item name has. I also turned it into an array formula, and added IFERROR to get rid of the unnecessary errors where a blank item wasn't found.
From here, I added it to a VLOOKUP() function.
ARRAYFORMULA(iferror(VLOOKUP(iferror(arrayformula(left(split(B8:B10,","), arrayformula(find(" - ",split(B8:B10,","))-1)))),$A$1:$B$4, 2,)))`
If you notice, the same equation from above is the first parameter within the VLOOKUP. The second parameter is the lookup table, and the third is the index of the lookup table that you want to return (ie 2 for the second column of the table, the prices).
Finally, I used this formula as a template in order to calculate a sum down a row of values as an array formula:
=ArrayFormula(MMULT(n([value]),(transpose(COLUMN([range])^0))))
For [value] I substituted in the full VLOOKUP equation from above. This would be the array of values. Then, for [range], I had to use a dynamic equation because the number of columns cannot be strictly defined (there may be more or less items in an order). To do this, I used an INDIRECT formula:
indirect("A1:"&ADDRESS(1,[columns])))
I replaced [columns] with COLUMNS(iferror(arrayformula(left(split(B8:B10,","), arrayformula(find(" - ",split(B8:B10,","))-1))))), which is just counting the number of columns the item array takes up. The inner equation is the same as a previous one.
All together, this completes the equation.
CodePudding user response：
Assuming that the 'Order ID' table is in A1:B4 and the the 'Product' table is in E1:F4, the following will work if placed in G1 (or any cell to the right or below). In addition, it should work for any number of orders or products, provided that the format of the 'line items' column exactly follows that in your example (i.e. items separated by commas, redundant colour information for each item separated from the item name by " - "):
=ArrayFormula(query({index(query(split(flatten(A2:A&"|"&trim(regexreplace(split(B2:B,",")," - \w ",))),"|"),"where Col2 is not null"),,1),vlookup(index(query(split(flatten(A2:A&"|"&trim(regexreplace(split(B2:B,",")," - \w ",))),"|"),"where Col2 is not null"),,2),E2:F,2,FALSE)},"select Col1,sum(Col2) group by Col1 label Col1 'Order ID', sum(Col2) 'Order total'"))