VLookup - One lookup, many values

  • Thread starter Thread starter Sue
  • Start date Start date
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!
 
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
 
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!
 
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
 
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?
 
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!
 
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
 
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!
 
NMP said:
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.
Click to expand...

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
Click to expand...

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 D1:D9 array
enter =ArrayUniques(C1:C9)

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.

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
 
NMP said:
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.
Click to expand...

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
 
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
 
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!
 
Back
Top