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.

 

[zapppsr]

My bad: Forgot to include the header in the range... now just need to get rif of the num error.

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?

 

[zapppsr]

It was looking perfect:

But then I added "train" to the list and truck is gone:

How come?

 

[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

<COLGROUP><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula H8 =+H7 H15 =+H14 H22 =+H21 <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Array Formulas Cell Formula I7 {=IF(ISERROR(INDEX($H$7:$H$42,MATCH(0,COUNTIF($I$6:I6,$H$7:$H$42),0))),"",INDEX($H$7:$H$42,MATCH(0,COUNTIF($I$6:I6,$H$7:$H$42),0)))} <THEAD> </THEAD><TBODY> </TBODY> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<TBODY>
</TBODY>

 

[zapppsr]

Tried, even with CTRL SHIFT ENTER, was not able to do it. Had to correct a missing argument at the ISERROR part.
Returned blank.

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

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H8 =+H7 H15 =+H14 H22 =+H21 <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula I7 {=IF(ISERROR(INDEX($H$7:$H$42,MATCH(0,COUNTIF($I$6:I6,$H$7:$H$42),0))),"",INDEX($H$7:$H$42,MATCH(0,COUNTIF($I$6:I6,$H$7:$H$42),0)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[zapppsr]

Tried, even with CTRL SHIFT ENTER, was not able to do it. Had to correct a missing argument at the ISERROR part.
Returned blank.

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

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H8 =+H7 H15 =+H14 H22 =+H21 <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula I7 {=IF(ISERROR(INDEX($H$7:$H$42,MATCH(0,COUNTIF($I$6:I6,$H$7:$H$42),0))),"",INDEX($H$7:$H$42,MATCH(0,COUNTIF($I$6:I6,$H$7:$H$42),0)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[taurean]

Hi,

I see that you have got the logic of formula correctly. The formula, as it is now, doesn't have error handling. You will have to adjust all ranges correctly. If you have Excel 2007+ then wrap the formula with:
=IFERROR(MainFormula,"")

My guess, is you added data but you didn't update the ranges i.e.
before inputting 'train' the ranges were A1:C25 and they have now become say A1:C27 so it is missing out on the last 2 rows data. Please verify this. Simplest trick is to do FIND and REPLACE to replace changed address value.

Otherwise you may upload a workbook as it is now. You can use a free file hosting site like box.net and provide a hyperlink here. We can then take a look.

 

[zapppsr]

As you can see back there in the picture, I didn't expand the range, just typed train where there was truck.

I'm already using =IFERROR in the last one, since the #NUM error is not showing.

I have reviewd the ranges many times... Don't know what else to do.

I will post the spreadsheet, but the formulas will be in portuguese, don't know how it will show...

https://dl.dropbox.com/u/33763248/fakepivot.xlsx

Hi,

I see that you have got the logic of formula correctly. The formula, as it is now, doesn't have error handling. You will have to adjust all ranges correctly. If you have Excel 2007+ then wrap the formula with:
=IFERROR(MainFormula,"")

My guess, is you added data but you didn't update the ranges i.e.
before inputting 'train' the ranges were A1:C25 and they have now become say A1:C27 so it is missing out on the last 2 rows data. Please verify this. Simplest trick is to do FIND and REPLACE to replace changed address value.

Otherwise you may upload a workbook as it is now. You can use a free file hosting site like box.net and provide a hyperlink here. We can then take a look.

 

[mikerickson]

If "Vehicle" is in A1, you could put this CSE formula in C2 and drag down.

=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$1000&"",A:A&"",0)=ROW($A$2:$A$1000), ROW($A$2:$A$1000), 9999),ROWS($1:1)))&""

If the first column of Table 1 is a named range (without blanks), this (quicker) formula could be used.
=INDEX(A:A, SMALL(IF(MATCH(NamedRange, A:A,0)=ROW(NamedRange), ROW(NamedRange), 9999),ROWS($1:1)))&""

 

[taurean]

My formula refers to the row as its in Excel and not in your table
so either:
=IFERROR(INDEX($C$1:$C$29,SMALL(IF(FREQUENCY(MATCH($C$6:$C$29,$C$6:$C$29,0),MATCH($C$6:$C$29,$C$6:$C$29,0))>0,ROW($C$6:$C$30)),ROWS($R$6:$R6))),"")
or
=IFERROR(INDEX($C$5:$C$29,SMALL(IF(FREQUENCY(MATCH($C$6:$C$29,$C$6:$C$29,0),MATCH($C$6:$C$29,$C$6:$C$29,0))>0,ROW($C$6:$C$30)),ROWS($R$6:$R6))-4),"")
See the red adjusted parts. The first arrangement uses the calculated row reference as correct one or the second adjusts to relative reference (Your header row is no. 5 so we subtract 4 to adjust row reference) Previously, it was plain coincidence that it was picking 4 of them.

 

[zapppsr]

You are a Moster taurean. It works!

Thank you and mikerickson too. You guys rock. Thanks all who helped too.

I will have to work a little on the last part ROWS($R$6:$R6))-4) because I'm using named ranges. But now I have something working.

My formula refers to the row as its in Excel and not in your table
so either:
=IFERROR(INDEX($C$1:$C$29,SMALL(IF(FREQUENCY(MATCH($C$6:$C$29,$C$6:$C$29,0),MATCH($C$6:$C$29,$C$6:$C$29,0))>0,ROW($C$6:$C$30)),ROWS($R$6:$R6))),"")
or
=IFERROR(INDEX($C$5:$C$29,SMALL(IF(FREQUENCY(MATCH($C$6:$C$29,$C$6:$C$29,0),MATCH($C$6:$C$29,$C$6:$C$29,0))>0,ROW($C$6:$C$30)),ROWS($R$6:$R6))-4),"")
See the red adjusted parts. The first arrangement uses the calculated row reference as correct one or the second adjusts to relative reference (Your header row is no. 5 so we subtract 4 to adjust row reference) Previously, it was plain coincidence that it was picking 4 of them.