Create unique list from duplicates

[Raeven]

The solution for this is probably a quite simple array formula wit
COUNTIF, but I cannot seem to get it exactly right. All I want to d
is take a list with many duplicates and list each duplicate once, in
separate list, without modifying the first list.

For example:

Column 1
1
2
8
5
9
8
5
4
8

would generate:

Column 2
1
2
8
5
9
4

Thanks in advance,
Stephani

 

[Guest]

Hi Stephanie,

What you are looking for, I believe, is Data > Filter > Advanced Filter.

Select "Copy to another location" then click the "Unique records only" then select the Copy to location.

Hope this helps.


--
Thanks

Adam

====================
Windows XP Pro + Office 2003 Pro

 

[Domenic]

Hi,

You can easily do this using Advanced Filter.

Date > Filter > Advanced Filter
Choose "Copy to another location"
Enter your "List range" and "Copy to" location
Check "Unique records only"
Click Ok

If you specifically want an array formula to do this, then assumin
that your data is in Sheet 1 and starts in A2, enter the followin
formula in A2 in Sheet2 and copy down until you get #N/A:

=INDEX(Sheet1!$A$2:$A$10,MATCH(0,COUNTIF(Sheet2!$A$1:A1,Sheet1!$A$2:$A$10),0))

entered using CTRL+SHIFT+ENTER.

Hope this helps!

 

[Gord Dibben]

Stephanie

You can use Data>Filter>Advanced Filter.

"Copy to a new location" and "Unique records only"

Filtering Tutorial at Debra Dalgleish's site.......
http://www.contextures.on.ca/tiptech.html

Also check out Debra's info on Data Validation........
http://www.contextures.on.ca/xlDataVal01.html

More on Duplicates marking, preventing at Chip Pearson's site....
http://www.cpearson.com/excel/topic.htm

Scroll down to "D" section and select Duplicate topics.

Gord Dibben Excel MVP

 

[Raeven]

Thanks! Both of these methods worked very well. I ended up using th
array formula because I want it to update automatically as I add dat
to the first sheet.

Stephani