Menu
  1. Accueil
  2. How To
  3. Software
  4. Office Software
  5. Excel
Thanks
0
Thanks

A few words of thanks would be greatly appreciated.

Excel - Comparing cells across multiple columns

Latest update on by Jean-François Pillou .
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.
Thanks
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Comparing cells across multiple columns », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

1 Comment