Comparing cells and in multiple columns

Solved/Closed
Reza - Feb 1, 2010 at 10:40 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Feb 1, 2010 at 10:55 AM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Feb 1, 2010 at 10:55 AM
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