Which Formula?
 

What I want to do is
Search through product codes for a certain code€¦ Then I have it return the
transaction number€¦
So, I created a Vlookup to do so...
(EXAMPLE BELOW)

My problem is€¦
When I use my Vlookup formula and drag it down, it will repeat the same
transaction for several rows (probably based of # of rows until the next
transaction with that product code) before listing the next transaction.
I would like for each transaction to only be listed once and in subsequent
order.
How can I eliminate this repetition€¦

Example- Searching for product code 21€¦
DATA SHEET
A-Product Code B-Transaction #
18 AX144
21 AT888
55 BT444
89 EE789
21 BW123
10 CR559

VLOOKUP FORMULA SHEET
A- (=Vlookup(21, A1:B1000, 2, false)
AT888
AT888
BW123
BW123
BW123

WHAT I WOULD LIKE IS JUST:
AT888
BW123

Which Formula?
 

VLOOKUP will not do what you want. Have you considered using
<Data<Filter<Autofilter on your data to filter for the product code you
want?

Tom

Which Formula?
 

I recommend using AutoFilter but if you want a dynamic formula solution....

A2:A100 = Product Code
B2:B100 = Transaction #
D2 = lookup Product Code = 21

Enter this array formula** in E2 and copy down*** until you get blanks:

=IF(ROWS(E$2:E2)<=COUNTIF(A:A,D$2),INDEX(B$2:B$100 ,SMALL(IF(A$2:A$100=D$2,ROW(B$2:B$100)),ROWS(E$2:E 2))-MIN(ROW(B$2:B$100))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

*** you have to copy the formula to a number of cells that is at least equal
to the max count of any product code. For example, if product code 21
appears 10 times in col A and that is the most times out of all the product
codes then you have to copy the formula to at least 10 cells.

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
What I want to do is
Search through product codes for a certain code. Then I have it return the
transaction number.
So, I created a Vlookup to do so...
(EXAMPLE BELOW)

My problem is.
When I use my Vlookup formula and drag it down, it will repeat the same
transaction for several rows (probably based of # of rows until the next
transaction with that product code) before listing the next transaction.
I would like for each transaction to only be listed once and in subsequent
order.
How can I eliminate this repetition.

Example- Searching for product code 21.
DATA SHEET
A-Product Code B-Transaction #
18 AX144
21 AT888
55 BT444
89 EE789
21 BW123
10 CR559

VLOOKUP FORMULA SHEET
A- (=Vlookup(21, A1:B1000, 2, false)
AT888
AT888
BW123
BW123
BW123

WHAT I WOULD LIKE IS JUST:
AT888
BW123