Forum Discussion

jimmytan9353's avatar
jimmytan9353
Copper Contributor
Mar 14, 2024

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 PRSold-ToBilling MaterialBilling QuantityBilling AmountSupplierInvoice MaterialInvoice QuantityInvoice Amount
10001000GoogleA4 Paper1200020000    
10001000Google   WalmartA4 Paper1200010000
10001001MicrosoftSafety Boots30005000    
10001001MicrosoftSafety Boots30005000    
10001001Microsoft   TargetSafety Boots30002000
10001001Microsoft   TargetSafety Boots30002000

 

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 PRSold-ToBilling MaterialBilling QuantityBilling AmountSupplierInvoice MaterialInvoice QuantityInvoice Amount
10001000GoogleA4 Paper1200020000WalmartA4 Paper1200010000
10001001MicrosoftSafety Boots600010000TargetSafety Boots60004000

 

Will be glad for any help. Thanks!

 

Trading.xls 

 

 

  • jimmytan9353 

    =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.

    • jimmytan9353's avatar
      jimmytan9353
      Copper Contributor

      OliverScheurich 

       

      Thanks a bunch! But when I try to replicate on real case, it doesn't work.

       

      Raw Data:

       

      SO PRSold-ToBilling MaterialBilling QuantityBilling AmountSupplierInvoice MaterialInvoice QuantityInvoice Amount
      110350588SupermanKrypton20,600.00023,297.18  0.0000.00
      110350588  0.0000.00Captain AmericaKrypton20,600.0004,845.38
      110350591BatmanKrypton23,900.00031,308.89  0.0000.00
      110350591  0.0000.00Captain AmericaKrypton23,900.0007,157.84
      110350596IronmanXenon22,600.00025,074.38  0.0000.00
      110350596  0.0000.00Captain AmericaXenon22,600.0006,768.51

       

       

      Results:

       

      SO PRSold-ToBilling MaterialBilling QuantityBilling AmountSupplierInvoice MaterialInvoice QuantityInvoice Amount
      110350588SupermanKrypton2060023297.18Captain AmericaKrypton206004845.38
      110350588 #N/A2060023297.18#N/A#N/A206004845.38
      110350591BatmanKrypton2390031308.89Captain AmericaKrypton239007157.84
      110350591 #N/A2390031308.89#N/A#N/A239007157.84
      110350596IronmanXenon2260025074.38Captain AmericaXenon226006768.51
      110350596 #N/A2260025074.38#N/A#N/A226006768.51

       

      Here's the file. Appreciate your kindness to investigate. Thanks!

      Excel 

       

       

       

Resources