SOLVED

VLOOKUP returning sum of values with common value in one row

Copper Contributor

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 

 

 

13 Replies

@jimmytan9353 

I have a solution but there are differences in C18, G17 and G18.

Please explain these results.

 

@Detlef Lewin 

 

Sorry, I forgot to replace the 'real' names with examples.

I've updated the expected results. Please have another look. Thanks!

 

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

sum.png

@jimmytan9353 

A solution with PQ.

 

@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 

 

 

 

@Detlef Lewin 

 

Hey, thanks! Is PQ an add-on? I think it's quite a steep learning curve, but I'll give it a try.

thank you once more :cool:

 

 

@jimmytan9353 

You are welcome! Do you work with google sheets? The formula works in Office 365 or Excel for the web.

@jimmytan9353 

Since Excel 2016 PQ is a part of Excel.

Most things can be done via the UI.

@Detlef Lewin 

 

Would you be able to help me again with the PQ in the attached file? I have realized there are some differences in my ERP format.

 

Trading V2 

 

Thank you once again! 

@jimmytan9353 

1. You did not refresh the query.

2. I used the column "Sold-To" for matching. But now there are empty cells.

3. Explain the value in I21.

 

@Detlef Lewin 

 

I have corrected J21, can you please have another look? 

Thanks :happyface:

best response confirmed by jimmytan9353 (Copper Contributor)
Solution

@jimmytan9353 

I skipped the "Sold-To" field for the matching.

 

Thank you! It works! Really appreciate this.
Hope you have a nice day ahead :D
1 best response

Accepted Solutions
best response confirmed by jimmytan9353 (Copper Contributor)
Solution

@jimmytan9353 

I skipped the "Sold-To" field for the matching.

 

View solution in original post