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
Is it too much to ask to adapt your formula to consider that the Vehicle row is a named interval? For instance, vehicle_name

Since you use some tricks adding 1 more line to the interval in ROW($C$6:$C$30) and the ROWS($R$6:$R6))-4) I'm having some trouble to replace $C$5:$C$29 for vehicle_name and stil use that logic.
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
vehicle_name = $C$6:$C$29
with header is $C$5:$C$29

But I'd rather not use the header.

zapppsr said:
Is it too much to ask to adapt your formula to consider that the Vehicle row is a named interval? For instance, vehicle_name

Since you use some tricks adding 1 more line to the interval in ROW($C$6:$C$30) and the ROWS($R$6:$R6))-4) I'm having some trouble to replace $C$5:$C$29 for vehicle_name and stil use that logic.
Click to expand...
 
Upvote 0
zapppsr said:
vehicle_name = $C$6:$C$29
with header is $C$5:$C$29

But I'd rather not use the header.
Click to expand...

I suppose Named_Range shall not be a problem provided you adjust it by TopMostRow - 1 number (see red marked part row6 - 1=5)
=IFERROR(INDEX(vehicle_name,SMALL(IF(FREQUENCY(MATCH(vehicle_name,vehicle_name,0),MATCH(vehicle_name,vehicle_name,0))>0,ROW($C$6:$C$30)),ROWS($R$6:$R6))-5),"")
 
Upvote 0
Yeah, I did something like that. But the problem is vehicle_name is a dynamic range, so it grows and I would not like to depend on references like ROW($C$6:$C$30)),ROWS($R$6:$R6))-5) that presume vehicle_name will be whitin 6-30, and its header will be on 6.

Sorry If I'm pushing too much... but I'm refining a solution that already works and I'm grateful for your previous help.

I'm thinking on naming $C$6:$C$30 to vehicle_name1 and $R$6:$R6 to vehicle_nameh, but that seems kinda unecessary, if we get a solution that depends only on vehicle_name (which has no header by the way).

taurean said:
I suppose Named_Range shall not be a problem provided you adjust it by TopMostRow - 1 number (see red marked part row6 - 1=5)
=IFERROR(INDEX(vehicle_name,SMALL(IF(FREQUENCY(MATCH(vehicle_name,vehicle_name,0),MATCH(vehicle_name,vehicle_name,0))>0,ROW($C$6:$C$30)),ROWS($R$6:$R6))-5),"")
Click to expand...
 
Upvote 0
Sorry for late reply. Try following:
Define
Name: RefRow
RefersTo:
PHP: 
=LOOKUP("z",vehicle_name,ROW(vehicle_name))-ROWS(vehicle_name)

Then use it in the formula:
=IFERROR(INDEX(vehicle_name,SMALL(IF(FREQUENCY(MATCH(vehicle_name,vehicle_name,0),MATCH(vehicle_name,vehicle_name,0))>0,ROW(vehicle_name)),ROWS($R$6:$R6))-RefRow),"")

Do not bother about Red Marked part as it simply evaluates to 1 when input first time. Once you copy down it simply adjust like 1,2,3,4....etc. to get the next item in the INDEX.
 
Upvote 0
Thanks again.

I just got a little confused with PHP code...

It is just a normal Excel name, RefRow, that is =LOOKUP("z",vehicle_name,ROW(vehicle_name))-ROWS(vehicle_name), right?

Beucause I have no experience in doing codes in Excel, like Visual Basic when you open that console to type codes...

I will try this.

Thank you very much.

taurean said:
Sorry for late reply. Try following:
Define
Name: RefRow
RefersTo:
PHP: 
=LOOKUP("z",vehicle_name,ROW(vehicle_name))-ROWS(vehicle_name)

Then use it in the formula:
=IFERROR(INDEX(vehicle_name,SMALL(IF(FREQUENCY(MATCH(vehicle_name,vehicle_name,0),MATCH(vehicle_name,vehicle_name,0))>0,ROW(vehicle_name)),ROWS($R$6:$R6))-RefRow),"")

Do not bother about Red Marked part as it simply evaluates to 1 when input first time. Once you copy down it simply adjust like 1,2,3,4....etc. to get the next item in the INDEX.
Click to expand...
 
Upvote 0
Ahh,

That's not php code it was just that I was trying to display formula with some "different" formatting. The formula written in php is normal formula. Sorry for the confusion.

That said, I was probably dreaming when I suggested it :eek:. Please ignore my previous suggestion for "RefRow"

  • Name:RefRow
  • RefersTo:=MIN(ROW(vehicle_name))-1
 
Upvote 0
MISSION ACOMPLISHED!

Thank you very much. Everything works fine. I'm stil trying to figure out the logic behind all that, but now it works with named ranges and I can use it here on my needs.

Thank you for always answer to my questions and for delivering such a nice solution.

See you!

taurean said:
Ahh,

That's not php code it was just that I was trying to display formula with some "different" formatting. The formula written in php is normal formula. Sorry for the confusion.

That said, I was probably dreaming when I suggested it :eek:. Please ignore my previous suggestion for "RefRow"

  • Name:RefRow
  • RefersTo:=MIN(ROW(vehicle_name))-1
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Creating Unique list from Customer Order Amount, Customer Name and Order Identifier using the Unique function
2
Replies
11
Views
218
etaf
etaf
R
  • Question
create one unique master list from multiple lists
Replies
1
Views
195
Artik
Artik
D
  • Question
Macro to create a new pivot table for each column in a spreadsheet
Replies
0
Views
182
donna_koenig
D
M
  • Solved
Finding cell with highest/lowest index from dropdown list?
Replies
9
Views
322
dreid1011
dreid1011
A
  • Question
Creating A Validation List From Unique Values in A Column (Filtered and unfiltered)
Replies
3
Views
469
RoryA
RoryA

Forum statistics

Threads
1,221,558
Messages
6,160,484
Members
451,651
Latest member
Penapensil

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