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

zapppsr

zapppsr

Board Regular
Joined
Aug 19, 2010
Messages
189
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:

fakepivot.jpg


Thanks in advance. I know I can count on you guys.
 
Sort by date Sort by votes
My bad: Forgot to include the header in the range... now just need to get rif of the num error.

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

I got here so far:

fp.jpg


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

Am I missing something?
Click to expand...
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
It was looking perfect:

ok.jpg


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

train.jpg


How come?
 
Upvote 0
try this
Excel 2003
HI
Unique
4
6
12
17
18

<COLGROUP><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]6[/TD]
[TD="bgcolor: #99ccff, align: center"]Responsibility Code[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

</TBODY>
Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H8[/TH]
[TD="align: left"]=+H7[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H15[/TH]
[TD="align: left"]=+H14[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H22[/TH]
[TD="align: left"]=+H21[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]I7[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
</TBODY>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Tried, even with CTRL SHIFT ENTER, was not able to do it. Had to correct a missing argument at the ISERROR part.
Returned blank.

texasalynn said:
try this
Excel 2003
HI
Unique
4
6
12
17
18

<tbody>
[TD="align: center"]6[/TD]
[TD="bgcolor: #99ccff, align: center"]Responsibility Code[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H8[/TH]
[TD="align: left"]=+H7[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H15[/TH]
[TD="align: left"]=+H14[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H22[/TH]
[TD="align: left"]=+H21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]I7[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Tried, even with CTRL SHIFT ENTER, was not able to do it. Had to correct a missing argument at the ISERROR part.
Returned blank.

texasalynn said:
try this
Excel 2003
HI
Unique
4
6
12
17
18

<tbody>
[TD="align: center"]6[/TD]
[TD="bgcolor: #99ccff, align: center"]Responsibility Code[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H8[/TH]
[TD="align: left"]=+H7[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H15[/TH]
[TD="align: left"]=+H14[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]H22[/TH]
[TD="align: left"]=+H21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]I7[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
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.
 
Upvote 0
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

taurean said:
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.
Click to expand...
 
Upvote 0
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)))&""
 
Upvote 0
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.
 
Upvote 0
You are a Moster taurean. It works!

final.jpg


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.


taurean said:
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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Unique & Filter from multiple tables into a single list
Replies
9
Views
319
K1600
K
B
  • Solved
Creating Unique list from Customer Order Amount, Customer Name and Order Identifier using the Unique function
2
Replies
11
Views
407
etaf
etaf
P
  • Solved
Filtering text data based on unique names and then pasting all the data into one single cell for each name
Replies
4
Views
213
p49
P
D
  • Question Question
Consolidating Data for Budget maybe use Pivot Table
Replies
0
Views
68
DCD1872
D
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
427
MARZIOTULLIO
M

Forum statistics

Threads
1,225,399
Messages
6,184,748
Members
453,254
Latest member
topeb

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top