Here's the situation:

I have a workseet that contains data on defective parts. The pertinent
columns are item# (Col C), defect code (Col D), and number of defective parts
(Col E).
I would like to be able to copy/paste this data from an external source
(some crappy locally-created database).
After pasting the data, I would like to have the list automatically filtered
for unique defect codes, put that list onto another sheet where I can then do
a SUMIF for each defect code and come up with a Top 10 list.
Then the same thing for unique part#'s.

So, is there a way to automate the unique records filter?

I think that may be more work than necesary.

Have you considered a pivot table?
After importing the data to Excel,
the pivot table could easily list
the count of unique items AND list the Top 10

See these websites for instructions:

If you're already familiar with pivot tables, start he

Pivot Table count of unique items:
http://www.contextures.com/xlPivot07.html#Unique

Show top items:
http://www.contextures.com/xlPivot05.html#TopItems

Otherwise, for Pivot Table basics:
http://peltiertech.com/Excel/Pivots/pivottables.htm


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"hankinator" wrote:

Here's the situation:

I have a workseet that contains data on defective parts. The pertinent
columns are item# (Col C), defect code (Col D), and number of defective parts
(Col E).
I would like to be able to copy/paste this data from an external source
(some crappy locally-created database).
After pasting the data, I would like to have the list automatically filtered
for unique defect codes, put that list onto another sheet where I can then do
a SUMIF for each defect code and come up with a Top 10 list.
Then the same thing for unique part#'s.

So, is there a way to automate the unique records filter?

I'm only vaguely familiar with pivot tables, but I've heard that they are
quite useful. I will check the links you provided.

Thanks!

"Ron Coderre" wrote:

I think that may be more work than necesary.

Have you considered a pivot table?
After importing the data to Excel,
the pivot table could easily list
the count of unique items AND list the Top 10

See these websites for instructions:

If you're already familiar with pivot tables, start he

Pivot Table count of unique items:
http://www.contextures.com/xlPivot07.html#Unique

Show top items:
http://www.contextures.com/xlPivot05.html#TopItems

Otherwise, for Pivot Table basics:
http://peltiertech.com/Excel/Pivots/pivottables.htm


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"hankinator" wrote:

Here's the situation:

I have a workseet that contains data on defective parts. The pertinent
columns are item# (Col C), defect code (Col D), and number of defective parts
(Col E).
I would like to be able to copy/paste this data from an external source
(some crappy locally-created database).
After pasting the data, I would like to have the list automatically filtered
for unique defect codes, put that list onto another sheet where I can then do
a SUMIF for each defect code and come up with a Top 10 list.
Then the same thing for unique part#'s.

So, is there a way to automate the unique records filter?

Check out Debra's site also for how to copy unique items to another sheet using
Advanced Filter.

http://www.contextures.on.ca/xladvfi...html#ExtractWs


Gord Dibben MS Excel MVP

On Wed, 9 Aug 2006 10:33:02 -0700, hankinator
wrote:

I'm only vaguely familiar with pivot tables, but I've heard that they are
quite useful. I will check the links you provided.

Thanks!

"Ron Coderre" wrote:

I think that may be more work than necesary.

Have you considered a pivot table?
After importing the data to Excel,
the pivot table could easily list
the count of unique items AND list the Top 10

See these websites for instructions:

If you're already familiar with pivot tables, start he

Pivot Table count of unique items:
http://www.contextures.com/xlPivot07.html#Unique

Show top items:
http://www.contextures.com/xlPivot05.html#TopItems

Otherwise, for Pivot Table basics:
http://peltiertech.com/Excel/Pivots/pivottables.htm


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"hankinator" wrote:

Here's the situation:

I have a workseet that contains data on defective parts. The pertinent
columns are item# (Col C), defect code (Col D), and number of defective parts
(Col E).
I would like to be able to copy/paste this data from an external source
(some crappy locally-created database).
After pasting the data, I would like to have the list automatically filtered
for unique defect codes, put that list onto another sheet where I can then do
a SUMIF for each defect code and come up with a Top 10 list.
Then the same thing for unique part#'s.

So, is there a way to automate the unique records filter?