Excel is a handy tool for data manipulations and comparison of multiple data sources. When combined with
VBA, Excel becomes an even more powerful tool for automating cumbersome jobs which otherwise may take up a long time for the user. One such example is comparing the values of
cells across multiple columns. Using
macro one can
customize the code to look for
specific values in ascending or descending order across the
columns. Once the criteria specified by the user matches the relevant
cells, the can either be highlighted or deleted as per the need of the user in this office software.
Issue
I have two columns A and B
A has items that have been priced
B has item prices (no dollar sign)
Is it possible to have a macro that goes down the column to 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.....
Solution
It will delete the duplicate row (when the item and price are the 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
Note
Thanks to
rizvisa1 for this tip on the forum.