List of unique text values from a list with duplicates

[canoeron]

I'm having difficulty creating a list that does not include any duplicated value. The list of unique values, needs to be in a different sheet and I need to retain the original list with duplicate values.

Here's' the original list. Col-c is simply identifying if the value ind Col-D is the first occurrence and uses the formula =countif($d$8:$d8,d8)=1 in C8 which is copied down.

c d


TRUE 20030300RGATES
TRUE 20030300LWEISS
TRUE 20030300TWELCH
TRUE 20030300CCHEN
TRUE 20030300EKNUTH
TRUE 20030300JBLOESER
FALSE 20030300LWEISS
FALSE 20030300RGATES
TRUE 20030300SFLETCHER
TRUE 20030300SMCCOVEY
FALSE 20030300TWELCH

I want to end up with the list below in a different sheet.

20030300RGATES
20030300LWEISS
20030300TWELCH
20030300CCHEN
20030300EKNUTH
20030300JBLOESER
20030300SFLETCHER
20030300SMCCOVEY

Thanks for any help.

[Sam Capricci]

you don't mention why you can't use Filter >> Advanced >> copy to another location >> unique records only?

[etaf]

try
=IFERROR(INDEX(Sheet1!$A$2:$A$50, MATCH(0, COUNTIF($A$1:A1, Sheet1!$A$2:$A$60), 0)),"")

enter as an array formula using

control+shift+enter

assuming the data is in column A sheet1 range row 2 - 60
and the data is extracted into column A row 2 sheet2

[FDibbins]

On the next sheet, put this in A2, copied down....
=IFERROR(INDEX(Sheet3!$D$1:$D$11,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet3!$D$1:$D$11),0,0),0)),"")

[AlKey]

Regular formula

=IFERROR(INDEX($D$2:$D$12,MATCH(0,INDEX(COUNTIF($E$1:E1,$D$2:$D$12),,),)),"")

	D	E
1
2	20030300RGATES	20030300RGATES
3	20030300LWEISS	20030300LWEISS
4	20030300TWELCH	20030300TWELCH
5	20030300CCHEN	20030300CCHEN
6	20030300EKNUTH	20030300EKNUTH
7	20030300JBLOESER	20030300JBLOESER
8	20030300LWEISS	20030300SFLETCHER
9	20030300RGATES	20030300SMCCOVEY
10	20030300SFLETCHER
11	20030300SMCCOVEY
12	20030300TWELCH

[Tony Valko]

Try this...

Data:

Sheet1

	A
1	Header
2	20030300RGATES
3	20030300LWEISS
4	20030300TWELCH
5	20030300CCHEN
6	20030300EKNUTH
7	20030300JBLOESER
8	20030300LWEISS
9	20030300RGATES
10	20030300SFLETCHER
11	20030300SMCCOVEY
12	20030300TWELCH

Results:

Sheet2

	A
1	Header
2	20030300RGATES
3	20030300LWEISS
4	20030300TWELCH
5	20030300CCHEN
6	20030300EKNUTH
7	20030300JBLOESER
8	20030300SFLETCHER
9	20030300SMCCOVEY
10
11

This array formula** entered in A2 and copied down until you get blanks:

=IFERROR(INDEX(Sheet1!A$2:A$12,MATCH(0,COUNTIF(A$1:A1,Sheet1!A$2:A$12),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[canoeron]

The list is the result of formulas. Advanced filter copies the formula but, it would work if there were a way to copy unique values. Also, I simplified my example by only providing a single column. In actuality of have a list for each month and would need to only add the value to the list if it isn't already in the consolidated list.

[canoeron]

Tony,

Thanks. This solved the problem I was trying to solve.

The problem was actually the interim step to solving this for a list of multiple columns. (ie Jan, Feb, Mar, Apr, etc.)

Ron

[FDibbins]

Im happy you got your question solved Im just curious - as far as I can see, all the formulas offered were essentially the same, what made you pick Tony's "

Mine =IFERROR(INDEX(Sheet3!$D$1:$D$11,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet3!$D$1:$D$11),0,0),0)),"")
Alkey =IFERROR(INDEX($D$2:$D$12,MATCH(0,INDEX(COUNTIF($E$1:E1,$D$2:$D$12),,),)),"")
Tony =IFERROR(INDEX(Sheet1!A$2:A$12,MATCH(0,COUNTIF(A$1:A1,Sheet1!A$2:A$12),0)),"")

[Tony Valko]

It's possible to extract the unique entries from a 2 dimensional range but it's very complicated.

There's an example here:

http://www.excelforum.com/excel-form...le-column.html

[canoeron]

I don't exactly know why. I guess Tony's just happened to be the first one I came to when I scrolled up the screen. I don't use the board often and didn't even realize the other replies at the time. However, now that look back, I think the visual table Tony provided, (as well as AlKey), is very useful to see the formulas references.

[canoeron]

Thanks Tony, I will look into it.

[ThirtyTwo]

this solution aint technically excel, but...............

ever tried new google sheets ?

https://docs.google.com/spreadsheets...EjtYvcNNB_bGcE

In Sheet1, Column D it has a list of values

In Sheet2, Column D, Range("D1") it has a very simple formula

Please Login or Register  to view this content.

As values change or are added to Sheet1 column D, the values in Sheet2 will automatically change

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[FDibbins]

Sorry, I neglected to add etaf's formula in my list, that too, is pretty much the same