#1   Report Post  
Posted to microsoft.public.excel.misc
SJT SJT is offline
external usenet poster
 
Posts: 71
Default Unique Entries

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Unique Entries

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Unique Entries

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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
SJT SJT is offline
external usenet poster
 
Posts: 71
Default Unique Entries

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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default Unique Entries

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.



  #6   Report Post  
Posted to microsoft.public.excel.misc
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Unique Entries

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.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Unique Entries

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.



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Unique Entries

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.



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,688
Default Unique Entries

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.





  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default Unique Entries

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.



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 340
Default Unique Entries

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count unique entries... ChuckF Excel Worksheet Functions 5 October 12th 06 05:48 AM
Count Unique Entries SouthCarolina Excel Worksheet Functions 4 April 14th 06 11:44 PM
Best way to get a list of unique entries in a field [email protected] Excel Worksheet Functions 2 January 19th 06 11:30 PM
count duplicate (or, inversely, unique) entries, but based on a condition markx Excel Worksheet Functions 3 March 8th 05 07:57 PM
Counting unique entries in column A but only if specific values appear in columns B and C markx Excel Worksheet Functions 1 February 10th 05 12:52 PM


All times are GMT +1. The time now is 08:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"