Forum Discussion
Solved
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. Eac...
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!
https://docs.google.com/spreadsheets/d/1GRkYbPr1C1AdLK8NvPiw_Rxl2Fe8zVGE/edit?usp=sharing&ouid=103441876597334769631&rtpof=true&sd=true
You are welcome! Do you work with google sheets? The formula works in Office 365 or Excel for the web.