making multiple lists from one, with uniqueness

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

Here's an example of my data:

Foo Fighter
Foo Fighter
Foo Lishness
Foo Lishness
Bar Nun
Bar Exam
Foo D'Eluvit
Bar Oque
Bar D'Eluvit
Bar Nun
Foo Lishness
Foo Lishness
Baz Luhrmann
Foo Lishness
Baz Ooka
Baz Ooka
Baz Ebeez
Foo Manchu
Foo Manchu

How can I extract from that a set of lists of Foo's (Fighter, Lishness,
D'Eluvit, Manchu), Bar's (Nun, Exam, Oque, D'Eluvit) and Baz's
(Luhrmann, Ooka, Ebeez) for data validation? I'd like each item to
appear only once in whichever list(s) it applies to, but I don't care
about the order.

Pick a Foo: ______
Pick a Bar: ______
Pick a Baz: ______
 
I assume you want these lists for data validation drop down lists?

It'll take a couple of steps. First, you have to identify the unique items
then you have to extract those unique items into the individual lists.

Let's assume your data is in the range A1:B19

Enter this formula in C1 and copy down to C19. This will identify the
uniques.

=IF(SUMPRODUCT(--(A$1:A1&"_"&B$1:B1=A1&"_"&B1))=1,ROW(),"")

Now let's extract those uniques into the individual lists.

In some out of the way location on your sheet, say, X1:Z1, enter the 3
unique names for the lists:

X1 = Bar
Y1 = Baz
Z1 = Foo

Enter this array formula** in X2:

=INDEX($B:$B,MATCH(SMALL(IF($A$1:$A$19=X$1,$C$1:$C$19),ROWS(X$2:X2)),$C$1:$C$19,0))

Copy across to Y2 then down until you get a solid row of #NUM! errors. You
can hide those #NUM! errors if you want to but I'd just leave them there
since these extracted lists are out of sight.

Now, setup the drop down lists...

Assume you want the drop downs to appear in cells:

E1 = Bar
E2 = Baz
E3 = Foo

Select cell E1
Goto the menu Data>Validation
Allow: List
Source:

=X2:INDEX(X2:X100,COUNTIF(X2:X100,"*"))

OK

Repeat this for cells E2 and E3.

Source for E2:

=Y2:INDEX(Y2:Y100,COUNTIF(Y2:Y100,"*"))

Source for E3:

=Z2:INDEX(Z2:Z100,COUNTIF(Z2:Z100,"*"))
 
T. Valko said:
X1 = Bar
Y1 = Baz
Z1 = Foo
[...]
Select cell E1
Goto the menu Data>Validation
Allow: List
Source:

=X2:INDEX(X2:X100,COUNTIF(X2:X100,"*"))

OK

Repeat this for cells E2 and E3.

Source for E2:

=Y2:INDEX(Y2:Y100,COUNTIF(Y2:Y100,"*"))

Source for E3:

=Z2:INDEX(Z2:Z100,COUNTIF(Z2:Z100,"*"))
Click to expand...

Okay, that worked great, exactly what I wanted - but then I started
building on that based on advice I found elsewhere. Instead of using
these formulas in the data validation directly, I used them as the
targets of named ranges called Foo, Bar, and Baz, and set the validation
sources to =Foo, =Bar, and =Baz.

That worked also, but then I tried changing the validations to
=INDIRECT($X$1) etc., and it replied "The Source currently evaluates to
an error. Do you wish to continue?" and when I said "Yes", my pick
lists ended up empty. Any clue why? And any clue why Foo, Bar, and Baz
don't show up in the list of named ranges at the top left of the screen,
either, when "Insert > Name" shows them plain as day?
 
changing the validations to =INDIRECT($X$1)

The problem with that is INDIRECT won't work when referencing a dynamic
range.

I don't see why you're using INDIRECT in the first place. =Bar, =Baz, =Foo
will work as you noted. Unless you're using another cell to pick which list
to use? If that's the case then it gets a bit more complicated.

As far as the names not showing up in the name box...

Technically, those formulas used to define the dynamic ranges are *named
formulas*. Named formulas don't show up in the name box.

--
Biff
Microsoft Excel MVP


Phil said:
T. Valko said:
X1 = Bar
Y1 = Baz
Z1 = Foo
[...]
Select cell E1
Goto the menu Data>Validation
Allow: List
Source:

=X2:INDEX(X2:X100,COUNTIF(X2:X100,"*"))

OK

Repeat this for cells E2 and E3.

Source for E2:

=Y2:INDEX(Y2:Y100,COUNTIF(Y2:Y100,"*"))

Source for E3:

=Z2:INDEX(Z2:Z100,COUNTIF(Z2:Z100,"*"))
Click to expand...

Okay, that worked great, exactly what I wanted - but then I started
building on that based on advice I found elsewhere. Instead of using
these formulas in the data validation directly, I used them as the targets
of named ranges called Foo, Bar, and Baz, and set the validation sources
to =Foo, =Bar, and =Baz.

That worked also, but then I tried changing the validations to
=INDIRECT($X$1) etc., and it replied "The Source currently evaluates to an
error. Do you wish to continue?" and when I said "Yes", my pick lists
ended up empty. Any clue why? And any clue why Foo, Bar, and Baz don't
show up in the list of named ranges at the top left of the screen, either,
when "Insert > Name" shows them plain as day?
Click to expand...
 
T. Valko said:
I don't see why you're using INDIRECT in the first place. =Bar, =Baz, =Foo
will work as you noted. Unless you're using another cell to pick which list
to use?
Click to expand...

Not exactly - tho that thought had crossed my mind as a future
possibility; I'm already using X1:Z1 as validation for A1:A19, so I just
figured the file would be easier to maintain if that list of lists were
stored in only one place. (BTW, I'd greatly appreciate it if you'd
explain further, in case that possibility becomes a necessity - how
*would* I go about, say, adding validation to column B? :-))
As far as the names not showing up in the name box...

Technically, those formulas used to define the dynamic ranges are *named
formulas*. Named formulas don't show up in the name box.
Click to expand...

Okay, I hadn't made that distinction; I just thought "a name is a name".
Thanks for explaining it to me.
 
Let's assume A1 contains a drop down with these selections: Bar, Baz, Foo.

X1:Y1 are the column headers for the dynamic ranges Bar, Baz, Foo.

You want a drop down in B1 based on the selection made in cell A1. If you
select Foo in A1 you want the drop down in B1 to contain the items from te
dynamic range Foo.

As the source for the drop down in B1 use:

=CHOOSE(MATCH(A1,X1:Z1,0),Bar,Baz,Foo)
 
Back
Top