Find and compare duplicate values in an excel column, compare matching sums and print smallest

user_19771038 0
Viewed 35

I have an excel problem need help with. I have two columns in excel like below, one with numeric values and the second with some sort of option IDs. I am trying to compare the sum of the matching options and print the smallest value and option ID. So a formulas should print 3 and O1 in the below case. The complexity I have here is that I don't know how many options eventually there will be. So I can't really go about it by factoring all the option IDs in a formula to compare and print results.

2 / O1 
4 / O2 
1 / O1 
4 / O3 
5 / O2
excel
1 Answers

With Office 365 we can use a LET with UNIQUE, SUMIF and FILTER:

=LET(num,A1:A5,
    id,B1:B5,
    unq,UNIQUE(id),
    smf,SUMIFS(num,id,unq),
    FILTER(CHOOSE({1,2},smf,unq),smf=MIN(smf)))

enter image description here

If one has it the CHOOSE can be replaced with HSTACK:

=LET(num,A1:A5,
    id,B1:B5,
    unq,UNIQUE(id),
    smf,SUMIFS(num,id,unq),
    FILTER(HSTACK(smf,unq),smf=MIN(smf)))

enter image description here

user_48515900
Related