VLOOKUP returning sum of values with common value in one row

[jimmytan9353]

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!

Trading.xls 

[#2]

@jimmytan9353 

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

Please explain these results.

[#3]

@Detlef_Lewin 

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

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

[#4]

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

[#5]

@jimmytan9353 

A solution with PQ.

[#6]

@OliverScheurich 

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!

Excel 

[#7]

@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 

[#8]

@jimmytan9353 

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

[#9]

@jimmytan9353 

Since Excel 2016 PQ is a part of Excel.

Most things can be done via the UI.

[#10]

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

[#11]

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

[#12]

@Detlef_Lewin 

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

Thanks 

[#13 · Accepted solution]

@jimmytan9353 

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

[#14]

Thank you! It works! Really appreciate this.
Hope you have a nice day ahead 😄