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.

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

HTH! :)

"SJT" wrote:

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.

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).


"SJT" wrote:

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.

Thank you.

"JMB" wrote:

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).


"SJT" wrote:

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.

Try this:
=IF(ISERR(SMALL(IF(COUNTIF($A$1:$A1,$A1)=1,ROW(IND IRECT("1:"&ROWS($A$1:$A$5)))),ROWS($1:1))),"",INDE X($A$1:$A$5,SMALL(IF(COUNTIF($A$1:$A1,$A1)=1,ROW(I NDIRECT("1:"&ROWS($A$1:$A$5)))),ROWS($1:1))))

ctrlshiftenter (not just enter)


"SJT" wrote:

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.

I believe you can get the same result with

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

copied down


"Teethless mama" wrote:

Try this:
=IF(ISERR(SMALL(IF(COUNTIF($A$1:$A1,$A1)=1,ROW(IND IRECT("1:"&ROWS($A$1:$A$5)))),ROWS($1:1))),"",INDE X($A$1:$A$5,SMALL(IF(COUNTIF($A$1:$A1,$A1)=1,ROW(I NDIRECT("1:"&ROWS($A$1:$A$5)))),ROWS($1:1))))

ctrlshiftenter (not just enter)


"SJT" wrote:

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.

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

"Teethless mama" wrote in message
...
Try this:
=IF(ISERR(SMALL(IF(COUNTIF($A$1:$A1,$A1)=1,ROW(IND IRECT("1:"&ROWS($A$1:$A$5)))),ROWS($1:1))),"",INDE X($A$1:$A$5,SMALL(IF(COUNTIF($A$1:$A1,$A1)=1,ROW(I NDIRECT("1:"&ROWS($A$1:$A$5)))),ROWS($1:1))))

ctrlshiftenter (not just enter)


"SJT" wrote:

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.

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,SMAL L(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

"SJT" wrote in message
...
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.

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,SMAL L(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

"Biff" wrote in message
...
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,SMAL L(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

"SJT" wrote in message
...
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.

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(Shee t1!$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(Shee t1!$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$1:D1,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


"SJT" wrote:

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.

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


SJT wrote:

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.