Remove duplicates but leave the lowest value

[WT2008]

Hi,

I have a Excel 2010 spreadsheet with duplicated item numbers in one column with varying values in another.

Something like this:

Column A Column B
4350-R 4.50
4350-R 4.50
4350-R 5.50
4350-R 5.70
4850-B 4.15
4850-B 4.15
4850-B 4.15
4850-B 5.85
4850-B 5.85
4850-B 5.95
4850-B 6.15

I want to delete all duplicates in column A but leaving the row with the lowest value in column B. Even if the values 4.50 and 4.15 are duplicated, one only row for each is left.

In the above example I need this.

Column A Column B
4350-R 4.50
4850-B 4.15

Is there a way of doing this? I'm having 240,000 rows of data!

Any help appreciated.

[MarvinP]

Hi WT,

Not a problem. I've given the steps in the attached. I think I can do this problem in about 2 minutes, with all those rows. No VBA needed just follow the steps.

[WT2008]

Hi Marvin,
I appreciate your response. It worked like a charm, very good.

It will be so easy if the results wanted change a little bit? For example if I wanted the results like this:

4350-R 4.50
4350-R 5.50
4350-R 5.70
4850-B 4.15
4850-B 5.85
4850-B 5.95
4850-B 6.15

I know the remove duplicates from excel would do that, but in my case values on Col B could be the same for multiple items on Col A. For ex. there could be item 5420-B with 4.50 value on B.

Thanks!

[AlKey]

Or

In C1 and copy down

=IF(MIN($B$1:$B$11)=B1,"",COUNTIF(A$1:B1,B1))

then in A14 use this array formula and pull it across and down

=IFERROR(INDEX(A:A,SMALL(IF(($C$1:$C$11<>"")*($C$1:$C$11=1),ROW($C$1:$C$11)),ROWS(A$1:A1))),"")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Row\Col	A	B	C
1	4850-B	4.15
2	4850-B	4.15
3	4850-B	4.15
4	4350-R	4.5	1
5	4350-R	4.5	2
6	4350-R	5.5	1
7	4350-R	5.7	1
8	4850-B	5.85	1
9	4850-B	5.85	2
10	4850-B	5.95	1
11	4850-B	6.15	1
12
13
14	4350-R	4.5
15	4350-R	5.5
16	4350-R	5.7
17	4850-B	5.85
18	4850-B	5.95
19	4850-B	6.15

[MarvinP]

Hi WT,

I think my method does exactly what you want. It will remove the duplicate using the CountIF formulas in my example above.

[WT2008]

Hi WT,

I think my method does exactly what you want. It will remove the duplicate using the CountIF formulas in my example above.

Yes Marvin, your method worked as expected.

I just changed a little bit the criteria so another approach is needed. That is achieved with the AlKey workaround.

Thank you to both of you!

[WT2008]

Or

In C1 and copy down

=IF(MIN($B$1:$B$11)=B1,"",COUNTIF(A$1:B1,B1))

Thank you AlKey, this approach do exactly the criteria. Thank you!

[AlKey]

Appreciate your feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).