Unique Entries

[SJT]

On Sheet 1 of my worksheet in column A I have a list of names to which I add
every week. Many of these are duplicates. On Sheet 2, I would like to
maintain an ongoing list of unique entries of column A from Sheet 1. Since I
would like to be able to look at both the table that has the duplicates and
also a table w/ the unique names (which will have different information), a
filter of unique entries on Sheet 1 is not an ideal solution. How can I
accomplish this?


Example:

Sheet 1

Column A
ABC Company
XYZ Company
123 Company
ABC Company
XYZ Company

Results of Sheet 2

Column A
ABC Company
XYZ Company
123 Company


Thank you in advance for your assistance.

 

[ExcelChampion]

You can still do the Adavanced Filter but copy to another location. This
will give you the unique items list.

HTH!

 

[JMB]

This appears to work, assuming the table is in A1:A5 and this formula is
entered in cell F1, copied down until you get #NUM errors

=INDEX($A$1:$A$5,SMALL(IF(FREQUENCY(MATCH($A$1:$A$5,$A$1:$A$5,0),MATCH($A$1:$A$5,$A$1:$A$5,0))>0,ROW($A$1:$A$5)-ROW($A$1)+1,""),ROWS(F$1:F1)))

If you want blank cells instead of #NUM, the
=IF(ROWS(F$1:F1)>SUM(($A$1:$A$5<>"")/COUNTIF($A$1:$A$5,$A$1:$A$5&"")),"",INDEX($A$1:$A$5,SMALL(IF(FREQUENCY(MATCH($A$1:$A$5,$A$1:$A$5,0),MATCH($A$1:$A$5,$A$1:$A$5,0))>0,ROW($A$1:$A$5)-ROW($A$1)+1,""),ROWS(F$1:F1))))

both formulae are array entered (Cntrl+Shift+Enter).

 

[SJT]

Thank you.

This appears to work, assuming the table is in A1:A5 and this formula is
entered in cell F1, copied down until you get #NUM errors

=INDEX($A$1:$A$5,SMALL(IF(FREQUENCY(MATCH($A$1:$A$5,$A$1:$A$5,0),MATCH($A$1:$A$5,$A$1:$A$5,0))>0,ROW($A$1:$A$5)-ROW($A$1)+1,""),ROWS(F$1:F1)))

If you want blank cells instead of #NUM, then
=IF(ROWS(F$1:F1)>SUM(($A$1:$A$5<>"")/COUNTIF($A$1:$A$5,$A$1:$A$5&"")),"",INDEX($A$1:$A$5,SMALL(IF(FREQUENCY(MATCH($A$1:$A$5,$A$1:$A$5,0),MATCH($A$1:$A$5,$A$1:$A$5,0))>0,ROW($A$1:$A$5)-ROW($A$1)+1,""),ROWS(F$1:F1))))

both formulae are array entered (Cntrl+Shift+Enter).

 

[Teethless mama]

Try this:
=IF(ISERR(SMALL(IF(COUNTIF($A$1:$A1,$A1)=1,ROW(INDIRECT("1:"&ROWS($A$1:$A$5)))),ROWS($1:1))),"",INDEX($A$1:$A$5,SMALL(IF(COUNTIF($A$1:$A1,$A1)=1,ROW(INDIRECT("1:"&ROWS($A$1:$A$5)))),ROWS($1:1))))

ctrl>shift>enter (not just enter)

 

[JMB]

I believe you can get the same result with

=IF(COUNTIF($A$1:$A1,$A1)=1,$A1,"")

copied down

 

[Biff]

Try that on this data set. A1:A10

1,1,1,<empty>,Jim,3,4,6,6,5

You can get the same results using this:

=IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",A1))

Biff

 

[Biff]

Here's another one that accounts for blank/empty cells (with error trap).

Array entered:

=IF(ROWS($1:1)<=SUM((A$1:A$10<>"")/COUNTIF(A$1:A$10,A$1:A$10&"")),INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>"",IF(ROW(A$1:A$10)-ROW(A$1)+1=MATCH(A$1:A$10,A$1:A$10,0),ROW(A$1:A$10)-ROW(A$1)+1)),ROW(1:1))),"")

Biff

 

[Biff]

Slight tweak to accommodate possible row insertions:

=IF(ROWS($1:1)<=SUM((A$1:A$10<>"")/COUNTIF(A$1:A$10,A$1:A$10&"")),INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>"",IF(ROW(A$1:A$10)-ROW(A$1)+1=MATCH(A$1:A$10,A$1:A$10,0),ROW(A$1:A$10)-ROW(A$1)+1)),ROWS($1:1))),"")

Changed:

............ROW(1:1))),"")

To:

............ROWS($1:1))),"")

Biff

 

[Ron Coderre]

Try this:

With
Sheet1, cells A1:A10 contaiining a list of names (some duplicated)

Then
On Sheet2
D1: NameList

Enter this ARRAY FORMULA:
D2:
=IF(SUM((Sheet1!$A$1:$A$10<>"")*ISERROR(MATCH(Sheet1!$A$1:$A$10,$C$1:C1,0)))<>0,INDEX(Sheet1!$A$1:$A$10,MATCH(1,--ISERROR(IF(ISBLANK(Sheet1!$A$1:$A$10),0,MATCH(Sheet1!$A$1:$A$10,$C$1:$C1,0))),0),1),"")

Or...if you want the list on Sheet1..
use this shorter version of the formula
D2:
=IF(SUM(($A$1:$A$10<>"")*ISERROR(MATCH($A$1:$A$10,$D$11,0)))<>0,INDEX($A$1:$A$10,MATCH(1,--ISERROR(IF(ISBLANK($A$1:$A$10),0,MATCH($A$1:$A$10,$D$1:$D1,0))),0),1),"")

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Copy D2
Paste into D3 and down as far as you need

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

XL2002, WinXP

 

[Lori]

Why not use a pivot table on sheet 2 with Column A as the data range?
Simply click the refresh button if the data changes.
This can also gives you a frequency count if you drag it to the data
field:

Column A Count of Column A
ABC Company 2
XYZ Company 2
123 Company 1