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]

Adding a little complexity....

Refining what you already gave me, is it possible to add a "filter" based on the types, and only select unique names that are from that specific type?

Glad to know, Mr. Ethan Hunt

 

[taurean]

Re: Adding a little complexity....

Refining what you already gave me, is it possible to add a "filter" based on the types, and only select unique names that are from that specific type?
:
:

I think you should post usable data here so that we don't have to re-build it. To do so, you can either directly paste table data with borders or look for html maker or Excel Jeanie.

Now coming to the formula. Here are two assumptions made and I have assumed rest remains constant like previous post.

• Adjacent column i.e. $B$6:$B$29 is another named range : vehicle_type
• Range $G$4 houses the category i.e. {Land, Air, Sea} you will type here.

Following is the formula [ARRAY]:
=IFERROR(INDEX(vehicle_name,SMALL(IF(FREQUENCY(IFERROR(MATCH(vehicle_name&$G$4,vehicle_name&vehicle_type,0),0),IFERROR(MATCH(vehicle_name&$G$4,vehicle_name&vehicle_type,0),-1))>0,ROW(vehicle_name)),ROWS($R$6:$R6))-RefRow),"")

Please notice we need to introduce IFERROR to counter the error that concatenated MATCH throws at us.

 

[zapppsr]

Re: Adding a little complexity....

Simply Awesome! You are a genius!

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

Sorry for the trouble not posting the data.

I think you should post usable data here so that we don't have to re-build it. To do so, you can either directly paste table data with borders or look for html maker or Excel Jeanie.

Now coming to the formula. Here are two assumptions made and I have assumed rest remains constant like previous post.

• Adjacent column i.e. $B$6:$B$29 is another named range : vehicle_type
• Range $G$4 houses the category i.e. {Land, Air, Sea} you will type here.

Following is the formula [ARRAY]:
=IFERROR(INDEX(vehicle_name,SMALL(IF(FREQUENCY(IFERROR(MATCH(vehicle_name&$G$4,vehicle_name&vehicle_type,0),0),IFERROR(MATCH(vehicle_name&$G$4,vehicle_name&vehicle_type,0),-1))>0,ROW(vehicle_name)),ROWS($R$6:$R6))-RefRow),"")

Please notice we need to introduce IFERROR to counter the error that concatenated MATCH throws at us.

 

[taurean]

Re: Adding a little complexity....

Simply Awesome! You are a genius!

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

Sorry for the trouble not posting the data.

Thanks for the feedback.

Here's one more which will work. It is based on mikerickson's approach. It is shorter than my formula and simpler.
=IFERROR(INDEX(vehicle_name,SMALL(IF(IFERROR(MATCH(vehicle_name&$K$3,vehicle_name&vehicle_type,0)=(ROW(vehicle_name)-RefRow),FALSE),ROW(vehicle_name),999),ROWS($S$6:$S6))-RefRow),"")
Pay attention to red part which is row number larger than your data is. I've avoided a function but you can replace it with MAX(ROW(vehicle_name))+1 for adjusting it with all cases.

 

[zapppsr]

Re: Adding a little complexity....

Thanks again!

Thanks for the feedback.

Here's one more which will work. It is based on mikerickson's approach. It is shorter than my formula and simpler.
=IFERROR(INDEX(vehicle_name,SMALL(IF(IFERROR(MATCH(vehicle_name&$K$3,vehicle_name&vehicle_type,0)=(ROW(vehicle_name)-RefRow),FALSE),ROW(vehicle_name),999),ROWS($S$6:$S6))-RefRow),"")
Pay attention to red part which is row number larger than your data is. I've avoided a function but you can replace it with MAX(ROW(vehicle_name))+1 for adjusting it with all cases.