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
762
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 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!