VLookup - One lookup, many values

[Alan Beban]

Two things occur to me aside from Frank Kabel's formula that doesn't
seem to work for me. Assuming your basic data is in A1:Bwhatever, then
if the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, you can
enter in D1

=INDEX(Vlookups($C1,$A$1:$B$10,2),COLUMN(A1)) and fill across until you
get #REF! This will produce what you described when you insert an order
number in C1. (I used A1:B10; you would substitute A1:Bwhatever).

The other, which has a different organization of the desired output, is
to simply Auto Filter on A1:Bwhatever. Then you can click on the down
arrow in A1 and select the order number.

I would guess, since Frank Kabel approached it as he did, that there is
a formula comprised of built-in functions that can work; maybe other
people got his to work; maybe someone else can contribute that.

Alan Beban

 

[NMP]

Part of the problem is that I am dealing with over 500 different order
numbers repeated for each feature description. What's the easiest way to
copy just one of each order number over to C1?

 

[Alan Beban]

If you have made the functions in the freely downloadable file at
http:/home.pacbell.net/beban available to your workbook, simply enter in C1

=INDEX(ArrayUniques($A$1:$A$10),ROW(A1)) and copy down until you get a
#REF! error. Then you can enter in D1 the formula I previously provided
and copy it down and then across.

If you aren't going to make those functions available to your workbook,
then perhaps someone will provide a formula of built-in functions to
load the unique values in C1:Cwhatever, or describe how to do it with
filtering; but you will still then need code to provide your feature
descriptions in D1:whateverwhatever.

Alan Beban

 

[NMP]

OK. I'm not familiar with making new functions available to my workbook. I
went to your website and copied everything over to a worksheet in the same
workbook I am working on. Sorry if this is a stupid question, but now what
do I do?

Thanks!

 

[Alan Beban]

Not very many questions are stupid; yours isn't either.

What do you mean you copied everything over? Exactly what did you do?
The file at the Web site has one Worksheet (the description page) and
one Module (which contains all the code). Does the workbook you're
working on now have a Module containing all the code from the Module at
the Web site? If so, you can now just enter the formulas as I described
them in my recent posts in this thread.

One normal way to make the functions available to your workbooks is to
download the file from the Web site using the Download button, save it
as an xl add-in, and in the relevant workbook click Tools|References and
click on the downloaded add-in that then appears in the list of
available library references--that makes the functions available to that
workbook. But if you have copied all that code into a Module in the
workbook, that too, of course, makes those functions available to that
workbook. Using the add-in approach just avoids having to copy into each
workbook all the code--you simply check a reference to the add-in instead.

Let me know how it works out for you.

Alan Beban

 

[NMP]

OK. Still not working. I right clicked on the link and saved it to
MyDocuments. Then I opened the file and did a Save As, as an add-in. I then
closed Excel, opened it back up and activated the add-in, closed it again,
and then opened my file with the formulas and they are both coming up with
NAME?. Which means for some reason it is not picking up those add-ins or
functions. When I open up the ArrayFunctions file I saved and when I just
click on the link on your site, I do not see the module. Does it show on a
separate worksheet?

I'm working in Windows2000 with Excel2000 if that matters.

Thanks!

 

[Alan Beban]

When you open up the ArrayFunctions file you saved, it opens to the
Description sheet, right? Click on Tools|Macro|VisualBasicEditor. You
can then see the Module.

When you open your file with the formulas click on Tools|References.
Included in the list of library references should be the ArrayFunctions
file. Check it.

Let me know.

Alan Beban

 

[NMP]

Why doesn't it show up when you do Tools|Macro|Macros? Anyway, yes, when I
do Tools|Macro|VisualBasicEditor it opens the module.

As far as the Tools|References thing goes, I don't have a selection called
References under Tools. Do you mean Add-Ins? I already checked that and it
still does not work. Is there something I have to do to envoke the macros or
should they just work automatically, just like a regular function?

 

[NMP]

I think I may have it working now! But it is "burning up" my laptop trying
to calculate. I think I may have had something wrong with the formula. I'll
let you know when if I can't get it right.

Thanks!

 

[Alan Beban]

Just to test that you do indeed have it working, activate a blank
worksheet, and in A1:A10 enter 1,2,3,3,2,5,6,4,8,4. Then in C1:C7 array
enter =ArrayUniques(A1:A10). You should get 1,2,3,5,6,4,8.

Alan Beban

 

[NMP]

It works. It's just wearing out my laptop! I think once I get all the cells
copied I need, I'll probably just paste the values.

One more question: Any idea on how I can get rid of duplicates? See below.
I want to return only one of the blacks for 1, one of the blues for 3, and
one one of the reds for red. So, my output would be C1 = 1, D1 = black, E1 =
red...

A B
1 black
1 black
1 red
2 yellow
2 green
3 blue
3 blue
3 red
3 red

Thanks!

 

[Alan Beban]

It works. It's just wearing out my laptop! I think once I get all the cells
copied I need, I'll probably just paste the values.

What do you mean "it's wearing out" your laptop? What formula is causing
it to "wear out"?

One more question: Any idea on how I can get rid of duplicates? See below.
I want to return only one of the blacks for 1, one of the blues for 3, and
one one of the reds for red. So, my output would be C1 = 1, D1 = black, E1 =
red...

A B
1 black
1 black
1 red
2 yellow
2 green
3 blue
3 blue
3 red
3 red

Why would E1=red??? Why would there be anyting in column E?

You could enter in C1 =A1$B1 and copy it down to C9; then in D19 array
enter =ArrayUniques(C1:C9)

Alan Beban

 

[NMP]

I mean I have 530 rows and 15+ columns of the
=INDEX(Vlookups($C1,$A$1:$B$10,2),COLUMN(A1)) formula and each time it
calulates it takes forever. So, I am trying to reduce the number of columns
by taking out duplicate items. For instance, I have one order number with
several different features, but some of those features may be duplicated on
each order. So, your first two formulas helped me put the order number once
in a column and then each feature in columns following. So the end result
has an order number and all of its features on the same row. But since some
of those features are duplicates, the output may read:
C D E F
2056232 BLACK BLACK RED

Well, I wan to keep the red, but I only want one black. Even though the
feature may actually appear more than once on the order, I only need to know
if it is on there at all. So, if I can get rid of the duplicates, I should
be able to cut down on the number of calcutaions it has to make.

My laptop is old! My company is in the process of replacing all of our PCs,
but it's probably going to be another couple of months before they get to me!

NMP

 

[Alan Beban]

I mean I have 530 rows and 15+ columns of the
=INDEX(Vlookups($C1,$A$1:$B$10,2),COLUMN(A1)) formula and each time it
calulates it takes forever. So, I am trying to reduce the number of columns
by taking out duplicate items. For instance, I have one order number with
several different features, but some of those features may be duplicated on
each order. So, your first two formulas helped me put the order number once
in a column and then each feature in columns following. So the end result
has an order number and all of its features on the same row. But since some
of those features are duplicates, the output may read:
C D E F
2056232 BLACK BLACK RED

Well, I wan to keep the red, but I only want one black. Even though the
feature may actually appear more than once on the order, I only need to know
if it is on there at all. So, if I can get rid of the duplicates, I should
be able to cut down on the number of calcutaions it has to make.

To eliminate the duplicates in the feature descriptions, a new
requirement you hadn't previously mentioned, change the formula I
previously gave you

=INDEX(vlookups($C2,$A$1:$B$10,2),COLUMN(A1)), to

=INDEX(ArrayUniques(vlookups($C2,$A$1:$B$10,2)),COLUMN(A1))

Alan Beban

 

[Alan Beban]

I tried to send you an email about slow recalculations to
(e-mail address removed) but it got bounced as not a valid address.

Alan Beban

 

[NMP]

Yah, sorry! I have not set up my profile for that to work. Let's see...I
don't like to post my work e-mail address on public forums...I think I still
have a Yahoo account that works...send it to (e-mail address removed).

Thanks!