Compare Customers

[gebo84]

Hi,

i have two tables which are sorted by our top spending customers, each table for a different financial year.
i want to be able to compare the two tables by having some sort of formula look for the customer in both tables and compare their spend by +/-
for example,

i want Company X in column E to look for the same company in column A then do a calculation from their spends which are in columns F & B respectively, then to return a value of +/-

is this possible?

 

[JEC]

Put your two table ranges in the first VSTACK function in the formula below

=LET(x,VSTACK(A2:B4,E2:F4),u,UNIQUE(INDEX(x,,1)),SORT(HSTACK(u,DROP(REDUCE("",u,LAMBDA(a,b,VSTACK(a,SUM(FILTER(INDEX(x,,2),INDEX(x,,1)=b))))),1)),2,-1))

 

[gebo84]

Put your two table ranges in the first VSTACK function in the formula below

=LET(x,VSTACK(A2:B4,E2:F4),u,UNIQUE(INDEX(x,,1)),SORT(HSTACK(u,DROP(REDUCE("",u,LAMBDA(a,b,VSTACK(a,SUM(FILTER(INDEX(x,,2),INDEX(x,,1)=b))))),1)),2,-1))

Hi,

thank you! it almost works, the final result seems to be adding the two values together rather than minus one from the other

 

[JEC]

Could you post sample data? Using XL2BB

 

[gebo84]

XL2BB

Sorry i cant get that to work,
i've posted an image to show you the sheet. Column I is where the current formula is returning the values

 

[Fluff]

Do you want to subtract col F from col B?

 

[gebo84]

Do you want to subtract col F from col B?

Yes please

 

[Fluff]

Ok, how about

=LET(v,VSTACK(A6:A100,E6:E100),u,UNIQUE(FILTER(v,v<>"")),SORT(HSTACK(u,SUMIFS(B:B,A:A,u)-SUMIFS(F:F,E:E,u))))

 

[gebo84]

Perfect thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.