Vlookup with multiple results

[robertsgw]

I'm trying to write of formula to that will allow me lookup a customer property and tell me their invoices outstanding from another excel tab. I have been using a vlookup, but it only comes up with the first occurrence. Below is an example. So on another tab I would enter ABC ST and the formula would pull all the invoices for ABC ST. Thanks.

COLUMN A COLUMN B COLUMN C
customer pro invoice # Amount
ABC ST 1234 5000
ABC ST 5678 60000
CBDG 91878 7000
PEFGR 87890 8000
GJERS 7856 9000

 

[robertsgw]

It worked, thank you. Another question. Is the away for in the customer column to create a drop down like you created to not list a customer name more than once. The sheet I'm working on is over 200 lines and the customer name could be listed 3 or 4 times. So the drop down that I have lists the customer name 3 or 4 times, but really I would like it to be listed just once.

 

[T. Valko]

It worked, thank you. Another question. Is the away for in the customer column to create a drop down like you created to not list a customer name more than once. The sheet I'm working on is over 200 lines and the customer name could be listed 3 or 4 times. So the drop down that I have lists the customer name 3 or 4 times, but really I would like it to be listed just once.

Create a separate list the contains the unique customer names and use that list as the source for the drop down list.

You can create this list using the advanced filter to extract unqiue records only.

 

[robertsgw]

The sheet I"m creating is meant to be used by other users. Is there formula I could write that will give me a list of unique customers instead of using filters?

 

[T. Valko]

The sheet I"m creating is meant to be used by other users. Is there formula I could write that will give me a list of unique customers instead of using filters?

Yeah, you could do that.

What version of Excel are you using?

 

[robertsgw]

2007

 

[T. Valko]

2007

I posted this just a couple of days ago:

http://www.mrexcel.com/forum/showpost.php?p=2847681&postcount=14

 

[T. Valko]

I posted this just a couple of days ago:

http://www.mrexcel.com/forum/showpost.php?p=2847681&postcount=14

Eh, disregard that reply.

That's for extracting uniques that meet a condition.

You want the uniques but there is no condition.

Standby...

 

[T. Valko]

Eh, disregard that reply.

That's for extracting uniques that meet a condition.

You want the uniques but there is no condition.

Standby...

OK, try this...

Book1
	A	B
2	Bob	Bob
3	Bob	Eric
4	Eric	Jill
5	Jill	Lisa
6	Lisa	Mary
7	Lisa	Tom
8	Mary	Tony
9	Mary	_
10	Tom	_
11	Tom	_
12	Tom	_
13	Tom	_
14	Tony	_
15	Tony	_
Sheet1

To extract the unique names from column A to column B...

Enter this formula in B2:

=A2

Enter this array formula** in B3:

=IFERROR(INDEX(A$2:A$15,MATCH(0,COUNTIF(B$2:B2,A$2:A$15),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Then, as the source for the drop down list you'd use:

=OFFSET(B$2,,,COUNTIF(B$2:B$15,"?*"))

 

[robertsgw]

It worked, Thank you.

 

[robertsgw]

One other question for you. How do you copy a macro from one workbook to a new workbook. I tried right clicking on tab of old workbook and did a move/copy to new workbook. Doesn't seem to be working.