SOLVED

# VLOOKUP returning sum of values with common value in one row

Copper 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 Google A4 Paper 12000 20000 10001000 Google 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 Google 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!

13 Replies

# Re: VLOOKUP returning sum of values with common value in one row

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

# Re: VLOOKUP returning sum of values with common value in one row

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

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

# Re: VLOOKUP returning sum of values with common value in one row

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

# Re: VLOOKUP returning sum of values with common value in one row

A solution with PQ.

# Re: VLOOKUP returning sum of values with common value in one row

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!

# Re: VLOOKUP returning sum of values with common value in one row

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

# Re: VLOOKUP returning sum of values with common value in one row

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

# Re: VLOOKUP returning sum of values with common value in one row

Since Excel 2016 PQ is a part of Excel.

Most things can be done via the UI.

# Re: VLOOKUP returning sum of values with common value in one row

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.

Thank you once again!

# Re: VLOOKUP returning sum of values with common value in one row

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.

# Re: VLOOKUP returning sum of values with common value in one row

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

Thanks

best response confirmed by jimmytan9353 (Copper Contributor)
Solution

# Re: VLOOKUP returning sum of values with common value in one row

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

# Re: VLOOKUP returning sum of values with common value in one row

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

# Re: VLOOKUP returning sum of values with common value in one row

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