Large Table with Duplicate Values - Minimum Value Needed

[kma27]

I've got a large excel sheet with duplicate values in Column A and dollar values in column B.

I need to delete the duplicate values in column A, so that only the minimum dollar value is left in Column B.

Example:


A B

1 5
1 6
2 6
2 7
3 1
3 6
3 5
4 2
4 3

The answers should read:

1 - 5
2 - 6
3 - 1
4 - 2

Please help!

Thanks

[NickyC]

try this

sort by col A (1st sort key) and col B (2nd sort key) both ascending

in cell C2, type the formula

Please Login or Register  to view this content.

and copy down. This will identify duplicates in col A, excluding the first value

copy and paste special the values in col C so the formulas are converted to values

sort columns A:C on column C, and delete all rows for which the value in column C is TRUE

[rylo]

Hi

Assuming your example data is in range A1:B9
C1: =IF(COUNTIF($A$1:A1,A1)=1,ROW(),"") Copy down to C9
E1: =IF(ROW()>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()))) Copy down to E9
F1: =IF(E1="","",MIN(IF($A$1:$A$9=E1,$B$1:$B$9,""))) This is array entered (ctrl, shift, enter). Copy down to F9

HTH

rylo