Forum Discussion
jimmytan9353
Mar 14, 2024Copper Contributor
VLOOKUP returning sum of values with common value in one row
Hi all, my ERP spits out this report format, which I need to summarize into filterable report. We are a trading company, so we track our purchase and sales back-to-back in any single transaction. Each transaction has a common value called "SO PR" in first column.
SO PR | Sold-To | Billing Material | Billing Quantity | Billing Amount | Supplier | Invoice Material | Invoice Quantity | Invoice Amount |
10001000 | A4 Paper | 12000 | 20000 | |||||
10001000 | Walmart | A4 Paper | 12000 | 10000 | ||||
10001001 | Microsoft | Safety Boots | 3000 | 5000 | ||||
10001001 | Microsoft | Safety Boots | 3000 | 5000 | ||||
10001001 | Microsoft | Target | Safety Boots | 3000 | 2000 | |||
10001001 | Microsoft | Target | Safety Boots | 3000 | 2000 |
I used VLOOKUP, but couldn't work due to empty cells. Furthermore, I need to sum if there are more than just one value in specific rows. This is my expected results:
SO PR | Sold-To | Billing Material | Billing Quantity | Billing Amount | Supplier | Invoice Material | Invoice Quantity | Invoice Amount |
10001000 | A4 Paper | 12000 | 20000 | Walmart | A4 Paper | 12000 | 10000 | |
10001001 | Microsoft | Safety Boots | 6000 | 10000 | Target | Safety Boots | 6000 | 4000 |
Will be glad for any help. Thanks!
- OliverScheurichGold Contributor
=LET(rng,B7:J20,
detailed_info,DROP(rng,,2),
so_pr,CHOOSECOLS(rng,1),
unique_so_pr,UNIQUE(so_pr),
HSTACK(
UNIQUE(TAKE(rng,,2)),
UNIQUE(DROP(REDUCE("",SEQUENCE(ROWS(unique_so_pr)),
LAMBDA(a,b,VSTACK(a,
REDUCE("",SEQUENCE(COLUMNS(DROP(rng,,2))),
LAMBDA(u,v,
LET(z,FILTER(CHOOSECOLS(FILTER(detailed_info,so_pr=INDEX(unique_so_pr,b)),v),LEN(CHOOSECOLS(FILTER(detailed_info,so_pr=INDEX(unique_so_pr,b)),v))>0),
HSTACK(u,IF(ISNUMBER(z),SUM(z),z))))))))
,1,1))))
With Office 365 or Excel for the web this formula could be an alternative.
- jimmytan9353Copper Contributor
Thanks a bunch! But when I try to replicate on real case, it doesn't work.
Raw Data:
SO PR Sold-To Billing Material Billing Quantity Billing Amount Supplier Invoice Material Invoice Quantity Invoice Amount 110350588 Superman Krypton 20,600.000 23,297.18 0.000 0.00 110350588 0.000 0.00 Captain America Krypton 20,600.000 4,845.38 110350591 Batman Krypton 23,900.000 31,308.89 0.000 0.00 110350591 0.000 0.00 Captain America Krypton 23,900.000 7,157.84 110350596 Ironman Xenon 22,600.000 25,074.38 0.000 0.00 110350596 0.000 0.00 Captain America Xenon 22,600.000 6,768.51 Results:
SO PR Sold-To Billing Material Billing Quantity Billing Amount Supplier Invoice Material Invoice Quantity Invoice Amount 110350588 Superman Krypton 20600 23297.18 Captain America Krypton 20600 4845.38 110350588 #N/A 20600 23297.18 #N/A #N/A 20600 4845.38 110350591 Batman Krypton 23900 31308.89 Captain America Krypton 23900 7157.84 110350591 #N/A 23900 31308.89 #N/A #N/A 23900 7157.84 110350596 Ironman Xenon 22600 25074.38 Captain America Xenon 22600 6768.51 110350596 #N/A 22600 25074.38 #N/A #N/A 22600 6768.51 Here's the file. Appreciate your kindness to investigate. Thanks!
- OliverScheurichGold Contributor
You are welcome! Do you work with google sheets? The formula works in Office 365 or Excel for the web.
- Detlef_LewinSilver Contributor
I have a solution but there are differences in C18, G17 and G18.
Please explain these results.
- jimmytan9353Copper Contributor
Sorry, I forgot to replace the 'real' names with examples.
I've updated the expected results. Please have another look. Thanks!
- Detlef_LewinSilver Contributor