Extracting unique values from column

[qfkeon]

I have a list of several thousand part numbers in a column in Excel. Many of the numbers duplicate in this list. Is there a formula that will read the column and copy each number once to a different column so I can get a clean list? Thanks.

 

[AnotherHiggins]

I have the following steps wrapped up into a macro with a button on a custom tool bar assigned to it.
[ul]
[li]Select the column containing the part numbers[/li]
[li]Go to Data > Filter > Advanced Filter[/li]
[li]Tic the box beside Unique Values Only[/li]
[li]Copy[/li]
[li]Navigate to a new sheet[/li]
[li]Paste[/li]
[/ul]
You are now looking at a list of all part numbers, each listed only once.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Hi gfkeon:

Or

I can do the filering operation on the sheet where I want to eventually have the filtered records and refer the range to be filtered on the other sheet.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Hi,

I use Data > Advanced Filter as John has outlined so weel for you.

But, as a matter of choice, I most often use Data > Get External Data... to get unique lists of items.

Check out faq68-5829 VERY POWERFUL!!!

I use this technique in the process of "drilling down" into data; ie

1. get a unique list of Part Numbers

2. given a Selected Part Number, get a unique list of Cost Centers that the Part passes thru

3. given a unique Cost Center, get a unique list of MachineIDs that are in the selected Cost center that the Part Number passes thru....

This can ALL be done without VBA code, using native Excel functionality.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[qfkeon]

Thanks guys. I never used the filter before. I was looking around in the reference formulas for something to use. I'm all set. I just filtered and copied the cells into a new column.