Find and compare duplicate values in an excel column, compare matching sums and print smallest (excluding blanks)

user_19771038 0
Viewed 96

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 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 do it by factoring all the option IDs in a formula to compare and print results. Additionally, there are some other values in the ID column, such as x(not an option, should be excluded) and blank cells.

2 / O1 
4 / O2 
1 / O1 
1 / x
4 / O3 
2 / -
5 / O2
excel-formula excel
2 Answers

Perhaps, you may try as shown below, formulas used applicable to MS365 users only

FORMULA_SOLUTION

• Formula used in cell C1

=LET(array,FILTER(A1:B7,ISNUMBER(B1:B7*1)*(B1:B7<>"")),
id,INDEX(array,,2),
u,UNIQUE(id),
sumf,BYROW(u,LAMBDA(x,SUMIFS(A1:A7,B1:B7,x))),
FILTER(CHOOSE({1,2},sumf,u),sumf=MIN(sumf)))

And when writing this formula if you have enabled the Beta Channel from Office Insiders then you can use the HSTACK() in place of CHOOSE()

enter image description here

• Formula used in cell C1

=LET(array,FILTER(A1:B7,ISNUMBER(B1:B7*1)*(B1:B7<>"")),
id,INDEX(array,,2),
u,UNIQUE(id),
sumf,BYROW(u,LAMBDA(x,SUMIFS(A1:A7,B1:B7,x))),
FILTER(HSTACK(sumf,u),sumf=MIN(sumf)))

Following Excel functions used to derive the Output,

  • LET() -- Used to assigns names to calculation results,

  • FILTER() -- Used twice, in the beginning to create an array of results which excludes the non numeric as well as the blanks, and at last to get the required output,

  • ISNUMBER() -- Used within FILTER() to get numeric values, returns TRUE,

  • INDEX() -- Used to extract the ID's from the array,

  • UNIQUE() -- Used to get the unique values from the id,

  • BYROW() & LAMBDA()-- Used to apply a LAMBDA() to each row and to return an array of the sum of unique values,

  • SUMIFS() -- Used with in LAMBDA() to get the sum

  • CHOOSE() / HSTACK() -- Used to return the array formed by appending each of the array arguments in a column-wise fashion,

  • MIN() -- Used to return the min of sum values.

EDIT

FORMULA_SOLUTION

• Formula used in cell B13

=TRANSPOSE(LET(array,FILTER($A$1:B11,(LEFT(B1:B11)="O")*(B1:B11<>"")),
id,INDEX(array,,COLUMN(B1)),
u,UNIQUE(id),
sumf,BYROW(u,LAMBDA(x,SUMIFS($A$1:$A$11,B1:B11,x))),
FILTER(HSTACK(sumf,u),sumf=MIN(sumf))))

Or,

=TRANSPOSE(LET(array,FILTER($A$1:B11,(LEFT(B1:B11)="O")*(B1:B11<>"")),
id,INDEX(array,,COLUMN(B1)),
u,UNIQUE(id),
sumf,BYROW(u,LAMBDA(x,SUMIFS($A$1:$A$11,B1:B11,x))),
FILTER(CHOOSE({1,2},sumf,u),sumf=MIN(sumf))))

And now drag right for the rest!

user_81625200

You could try:

enter image description here

Formula in D1:

=LET(X,CHOOSE({1,2},SUMIFS(A1:A7,B1:B7,B1:B7),B1:B7),INDEX(SORT(FILTER(X,LEFT(INDEX(X,,2))="O")),1))
user_97581940
Related