Creating A Unique List of Values From A Table

C

carl

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

Lori

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

Lori

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

Mike G

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

Lori

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

Pete_UK

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
 
H

Harlan Grove

carl said:
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:
Click to expand...
....

Name your range TBL.
I would like to create a list like this:

a
c
d
h
v
w
Click to expand...

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)),"")
 
R

RFJ

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