Sort by last update only - get rid of old ones

G

Guest

I have a very large worksheet that has multiple SKU's sorted by SKU then
Edition. I would like to get rid of the old Editions and just keep the
current one. On the exampe below, SKU 10E01254 has 3 editions I want to get
rid of 1 and 2 and keep the 3rd. all sku have multiple editions from 2 to 19
editions but I just one to keep on one spreadsheet the latest one on all SKU's
SKU Edition Price
10E01254 01 $850.00
10E01254 02 $850.00
10E01254 03 $840.00 Keep this

12L13567 01 $725.00
12L13567 02 $725.00
12L13567 03 $725.00
12L13567 04 $760.00
12L13567 05 $950.00 Keep this

12B01781 01 $160.00
12B01781 02 $180.00 Keep this
 
G

Guest

If you have MS Access, then import your data from Excel into Access. I put
your example data into an Access table named dbase.

Then create a blank query and go into SQL view. paste this query text and
run it

SELECT t1.sku, t1.edition, t1.price
FROM dbase t1 inner join
(
select sku, max(edition) as edition2 from dbase group by sku
) t2
on t1.sku = t2.sku and t1.edition = t2.edition2

export the results back to Excel
 
D

Dave Peterson

You could sort your data with a primary key of SKU number (ascending order) and
a secondary key of edition (but descending order)

So your data would look like:
10E01254 3 $840.00
10E01254 2 $850.00
10E01254 1 $850.00
12B01781 2 $180.00
12B01781 1 $160.00
12L13567 5 $950.00
12L13567 4 $760.00
12L13567 3 $725.00
12L13567 2 $725.00
12L13567 1 $725.00

Then in D1, put Keep (it's the first record that we want to keep)
In D2, put:
=if(a2=a1,"","Keep")
and drag down as far as you need.

Your test data looked like:
10E01254 3 $840.00 Keep
10E01254 2 $850.00
10E01254 1 $850.00
12B01781 2 $180.00 keep
12B01781 1 $160.00
12L13567 5 $950.00 keep
12L13567 4 $760.00
12L13567 3 $725.00
12L13567 2 $725.00
12L13567 1 $725.00

Then you can filter on column D to show the blanks and delete those visible
rows.

And then delete column D.
 
G

Guest

Thank you so much it helped

Dave Peterson said:
You could sort your data with a primary key of SKU number (ascending order) and
a secondary key of edition (but descending order)

So your data would look like:
10E01254 3 $840.00
10E01254 2 $850.00
10E01254 1 $850.00
12B01781 2 $180.00
12B01781 1 $160.00
12L13567 5 $950.00
12L13567 4 $760.00
12L13567 3 $725.00
12L13567 2 $725.00
12L13567 1 $725.00

Then in D1, put Keep (it's the first record that we want to keep)
In D2, put:
=if(a2=a1,"","Keep")
and drag down as far as you need.

Your test data looked like:
10E01254 3 $840.00 Keep
10E01254 2 $850.00
10E01254 1 $850.00
12B01781 2 $180.00 keep
12B01781 1 $160.00
12L13567 5 $950.00 keep
12L13567 4 $760.00
12L13567 3 $725.00
12L13567 2 $725.00
12L13567 1 $725.00

Then you can filter on column D to show the blanks and delete those visible
rows.

And then delete column D.
Click to expand...
 
G

Guest

Thank you so much. It helped

Duke Carey said:
If you have MS Access, then import your data from Excel into Access. I put
your example data into an Access table named dbase.

Then create a blank query and go into SQL view. paste this query text and
run it

SELECT t1.sku, t1.edition, t1.price
FROM dbase t1 inner join
(
select sku, max(edition) as edition2 from dbase group by sku
) t2
on t1.sku = t2.sku and t1.edition = t2.edition2

export the results back to Excel
Click to expand...
 
Top