Can you automatically filter for unique records?
 

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?

Can you automatically filter for unique records?
 

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?

Can you automatically filter for unique records?
 

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?

Can you automatically filter for unique records?
 

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?