Need script for filtering data

  • Thread starter Thread starter FUZION
  • Start date Start date
F

FUZION

Hey guys,

I have a large excel file with over 50,000 item numbers in it. I
column A I have the item number. In column E I have the Manufacturer
and in column H I have the sale price. What I am trying to figure ou
how to do is to filter the duplicate item numbers that have the sam
manufacturer, and pick delete the one with the highest price. Fo
example, WEV co128 $1.24 wev co128 $2.27 It would leave the lowes
price one alone and delete the highest priced one. Is this eve
possible to do through excel? Any help is appreciated
 
Sort on columns A, E, H ascending

then in an adjacent column (assume starting in M2 for example)

=if(And(A2=A1,E2=E1),"Delete","Keep")

In M1 put in a heading entry like Marker
then select M2 (with the formula) and in the lower right corner, double
click on the little black box to have it fill down the column. then do
End(xldown) to make sure it filled all the way down (no blank cells in the
column to the left - drag fill if necessary)

if you are satisfied that this is giving your the correct results,

Then apply an autofilter to this column( select the column and do
(Data=>Filter=>Autofilter)

In the dropdown in M1/Marker heading select Delete

Then go to the name box (left side on the formula bar) and type in
2:65000, then do Edit=>Delete

Now go to Data=>filter and select Autofilter to turn off the filter. The
high priced duplicates should be gone.
 
Back
Top