Re: making multiple lists from one, with uniqueness

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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,"*"))


--
Biff
Microsoft Excel MVP


"Phil" <spam-me@xxxxxxxxx> wrote in message
news:uFwUpoFEKHA.4432@xxxxxxxxxxxxxxxxxxxxxxx
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: ______


.

Relevant Pages

  • Re: Overriding Generics
    ... extends Foo> getFooList{... ... Foo or other subtypes like Bar. ... subclasses, and Allowed has Baz and Quux, and you use a List. ... or find another solution (such as separate lists for ...
    (comp.lang.java.programmer)
  • Re: replacing adjacent cells
    ... > I have a list of item descriptions with corresponding codes, and> a set of lists with item descriptions with quantities. ...
    (microsoft.public.excel)
  • XPath expression help wanted
    ... multiple bar elements. ... Now suppose I want to produce a list of all the bar ids. ... how do I get a list of the foo ids that matches the ... I want to do this so that when I display the lists side-by-side, ...
    (comp.text.xml)
  • Re: replacing adjacent cells
    ... of lists with item descriptions with quantities. ...
    (microsoft.public.excel)
  • Re: Insert with response
    ... FooBar, there's no way and no need to put them in synch. ... column in the foo table to 250 calumns in the bar table. ... set statistics time off ...
    (microsoft.public.sqlserver.programming)