I have a table in C3:V344. Is there a way to create a list of unique values
in the table. For example if my table was like this:

a d w
c v a
c h c


I would like to create a list like this:


a
c
d
h
v
w


Thank you in advance.

Try Data Pivot Table Multiple Consolidation Ranges with the
options:

- Select to create 0 page fields as these are not needed.
- Select an extra row and column in the range i.e. B2:V344.

(the contents of the extra row/column are not important - can just be
blank).
Click finish and drag row/column out of table and drag Value to the
column position:

Count of Value
Value Total
a 2
c 3
d 1
h 1
v 1
w 1
Grand Total 9

This can be refreshed whenever the data changes.


On May 16, 6:19 pm, carl wrote:
I have a table in C3:V344. Is there a way to create a list of unique values
in the table. For example if my table was like this:

a d w
c v a
c h c

I would like to create a list like this:

a
c
d
h
v
w

Thank you in advance.

Try Data Pivot Table Multiple Consolidation Ranges with the
options:

- Select to create 0 page fields as these are not needed.
- Select an extra row and column in the range i.e. B2:V344.

(the contents of the extra row/column are not important - can just be
blank).
Click finish and drag row/column out of table and drag Value to the
column position:

Count of Value
Value Total
a 2
c 3
d 1
h 1
v 1
w 1
Grand Total 9

This can be refreshed whenever the data changes.


On May 16, 6:19 pm, carl wrote:
I have a table in C3:V344. Is there a way to create a list of unique values
in the table. For example if my table was like this:

a d w
c v a
c h c

I would like to create a list like this:

a
c
d
h
v
w

Thank you in advance.

You can also try data/sort & filter/advance and then choose copy to another
location and place a check mark on Unique records only. (from office 07)

"Lori" wrote in message
oups.com...
Try Data Pivot Table Multiple Consolidation Ranges with the
options:

- Select to create 0 page fields as these are not needed.
- Select an extra row and column in the range i.e. B2:V344.

(the contents of the extra row/column are not important - can just be
blank).
Click finish and drag row/column out of table and drag Value to the
column position:

Count of Value
Value Total
a 2
c 3
d 1
h 1
v 1
w 1
Grand Total 9

This can be refreshed whenever the data changes.


On May 16, 6:19 pm, carl wrote:
I have a table in C3:V344. Is there a way to create a list of unique
values
in the table. For example if my table was like this:

a d w
c v a
c h c

I would like to create a list like this:

a
c
d
h
v
w

Thank you in advance.

Try Data Pivot Table Multiple Consolidation Ranges with the
options:

- Select to create 0 page fields as these are not needed.
- Select an extra row and column in the range i.e. B2:V344.

(the contents of the extra row/column are not important - can just be
blank).
Click finish and drag row/column out of table and drag Value to the
column position:

Count of Value
Value Total
a 2
c 3
d 1
h 1
v 1
w 1
Grand Total 9

This can be refreshed whenever the data changes.


On May 16, 6:19 pm, carl wrote:
I have a table in C3:V344. Is there a way to create a list of unique values
in the table. For example if my table was like this:

a d w
c v a
c h c

I would like to create a list like this:

a
c
d
h
v
w

Thank you in advance.

You could use advanced filter to create a unique list. Insert a new
worksheet and put a heading in A1. Then copy C3:C344 from the other
sheet into the new sheet from A2 onwards. Copy your other values in
turn from columns up to V below the preceeding values in column A of
the new sheet.

Then highlight all the data (including the heading) in column A and
click on Data | Filter | Advanced Filter. In the pop-up presented to
you select Unique Records Only, and Copy to New Location (specify $C
$1), then click OK. Your unique list will then appear in column C, and
you can delete columns A and B in the new sheet.

Hope this helps.

Pete

On May 16, 6:19 pm, carl wrote:
I have a table in C3:V344. Is there a way to create a list of unique values
in the table. For example if my table was like this:

a d w
c v a
c h c

I would like to create a list like this:

a
c
d
h
v
w

Thank you in advance.

carl wrote...
I have a table in C3:V344. Is there a way to create a list of unique values
in the table. For example if my table was like this:
....

Name your range TBL.

I would like to create a list like this:

a
c
d
h
v
w

If the topmost result cell were X3, try these formulas.

X3:
=INDEX(TBL,1,1)

X4 [array formula]:
=IF(SUM(COUNTIF(TBL,X$3:X3))<ROWS(TBL)*COLUMNS(TBL ),
INDEX($1:$65536,INT(MIN(IF(COUNTIF(X$3:X3,TBL)=0,
1000*ROW(TBL)+COLUMN(TBL)))/1000),
MOD(MIN(IF(COUNTIF(X$3:X3,TBL)=0,
1000*ROW(TBL)+COLUMN(TBL))),1000)),"")

Hello,

I found it convenient to use a UDF for this:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd

I've just picked up the end of this thread so am interpreting the problem
just from the subject line.

But on 2007 (not sure about XP) there's a Remove Duplicates option on the
Data Ribbon - that gives you a unique list.




"Bernd" wrote in message
oups.com...
Hello,

I found it convenient to use a UDF for this:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd