Comparing cells and in multiple columns

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
I have two columns A and B
A has items that have been priced
B has items price (no dollar sign)

Is it possible to have a macro go down column compare the prices for the same item in column A and delete the lowest price

example:

What I have:
car 100
car 200
car 300
boat 50
boat 60
boat 500

What I want:
car 300
boat 500

The list is much much longer than this.....

Thanks in advance!

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It will delete duplicate row (when item and price is same) and will delete the lower prices


If ActiveSheet.AutoFilterMode Then
Cells.Select
Selection.AutoFilter
End If

Cells.Select
Selection.Sort _
Key1:=Range("a2"), Order1:=xlAscending, _
Key2:=Range("b2"), Order2:=xlDescending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

lrow = 2
Do While (Cells(lrow, 1) <> "")

If Cells(lrow, 1) = Cells(lrow + 1, 1) Then
Rows(lrow + 1).Delete

Else
lrow = lrow + 1
End If
Loop
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month