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...
=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.
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!
You are welcome! Do you work with google sheets? The formula works in Office 365 or Excel for the web.
