Building a list of unique values from a list, without using a Pivot Table.

[zapppsr]

I need to extract the unique values from a list (where they are repeated), and build a list of those unique values.

The formula must run the vehicle column on table 1 and get the unique values and build a list of them on table 2. I'm aware that I will have to define a number of unique types I will want... For instance, my table 2 will have 12 fields because I know there are not more then 12 types of vehicles.

Below, an exemple of the data and desired result:

Thanks in advance. I know I can count on you guys.

 

[texasalynn]

take a look at the auto filter advanced function, to copy to another location unique values

 

[kpark91]

Since you know there are 12 different types of vehicles, can't you just hardcode it in?
For example,
In D1, you can type
=IF(ISERROR(MATCH("Car", $A:$A, 0)),"","Car")

and in D2, you can type
=IF(ISERROR(MATCH("Bus", $A:$A, 0)),"","Bus")

Otherwise, I can't think of a way to use only built-in formulas to get unique values automatically.
But then again, MrExcel MVPs always manage to surprise me

 

[zapppsr]

No copying allowed. I want the list tu update automatically.

 

[zapppsr]

In your formula, you mention the names of the vehicles. Consider the name of vehicles unknown. I don't know what is in table 1, so I cant use the names in the formula.

Since you know there are 12 different types of vehicles, can't you just hardcode it in?
For example,
In D1, you can type
=IF(ISERROR(MATCH("Car", $A:$A, 0)),"","Car")

and in D2, you can type
=IF(ISERROR(MATCH("Bus", $A:$A, 0)),"","Bus")

Otherwise, I can't think of a way to use only built-in formulas to get unique values automatically.
But then again, MrExcel MVPs always manage to surprise me

 

[CraigWM]

Perhaps Chandoo's method can help.

http://chandoo.org/wp/2008/11/06/unique-duplicate-missing-items-excel-help/

 

[kpark91]

Hmmm, I know how to get dupes when you can allow spaces inbetween.

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

but then you would have to copy down for the entire column, which means it would look like

1	1
1
1
2	2
2
3	3
3

<tbody>
</tbody>

 

[taurean]

Assumptions:
Your table1 is in A1:A25 including headers.

Formula:
=INDEX($A$1:$A$25,SMALL(IF(FREQUENCY(MATCH($A$2:$A$25,$A$2:$A$25,0),MATCH($A$2:$A$25,$A$2:$A$25,0))>0,ROW($A$2:$A$26)),ROWS($E$2:$E2)))
This is an ARRAY formula so it needs to be CSE'd (CONTROL + SHIFT + ENTER keys to be used in place of plain ENTER). Please pay attention to RED marked part, this is needed as FREQUENCY generates an extra element!!!

 

[texasalynn]

try this

Excel 2003
	H	I
6	Responsibility Code	Unique
7	4	4
8	4	6
9	4	12
10	4	17
11	4	18
12	6
13	6
14	12
15	12
16	12
17	12
18	12
19	12
20	12
21	17
22	17
23	17
24	18
25	18
26	18
27	18
Sheet1

 

[zapppsr]

Thank you very much for your effort and all who are helping. Your formula is very intelligent.

I got here so far:

I adjusted the references carefully to match this data including the red cell. Used CTRL ENTER.

Am I missing something?

Assumptions:
Your table1 is in A1:A25 including headers.

Formula:
=INDEX($A$1:$A$25,SMALL(IF(FREQUENCY(MATCH($A$2:$A$25,$A$2:$A$25,0),MATCH($A$2:$A$25,$A$2:$A$25,0))>0,ROW($A$2:$A$26)),ROWS($E$2:$E2)))
This is an ARRAY formula so it needs to be CSE'd (CONTROL + SHIFT + ENTER keys to be used in place of plain ENTER). Please pay attention to RED marked part, this is needed as FREQUENCY generates an extra element!!!