Is it possible to create a unique list based on cell input?

low98

Board Regular
Joined
Dec 21, 2004
Messages
116
I'm looking to return a list of Purchase Orders, based on the buyer of said PO. On the front page the user will enter buyer name. Sheet 2 has a list of purchase orders, some listed more then once. Is it possible to return a unique list of POs that buyer has? Basically this will be a vlookup with multiple records returned combined with a data filter. We're also looking at about 3,600 unique POs total, and 28 buyers.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
=IF(ROW($A2)-ROW($A$1)>COUNTIF(Sheet2!$B$1:$B$15,A1),"",INDEX(Sheet2!$A$1:$A$15,SMALL(IF(Sheet2!$B$1:$B$15=A1,ROW(Sheet2!$B$1:$B$15)),ROW($A2)-ROW($A$1))))

Where A1 contains the Purchaser and A1:B15 on the lookup sheet contains the Purchase Orders in column A and Buyers in Column B

You will have to adjust reference and ranges to suit.

Confirm the formula with CTRL+SHIFT+ENTER not just ENTER and then copy it down as far as you need.
 

low98

Board Regular
Joined
Dec 21, 2004
Messages
116
one additional peice, would it be possible to do this with a second variable (in a second location)? Say rather then a buyer, I'd like to search for a vendor number?

Thanks!
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

Just replace the cell reference, (A1 in my formula), with the reference to the Vendor #. Is that what you mean?
 

low98

Board Regular
Joined
Dec 21, 2004
Messages
116
Yes, but I'm hoping that I can get them both to display in the same location, so you can either enter buyer in one location OR Vendor # in another, and the POs will display in the same location. I'm thinking I'll have to build a 2nd array for the cell reference? Does that sound right?
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Ok, here we go....

First, In the results sheet, I suggest you create a combo-box with Buyer and Vendor as the choices and link it to a cell, say B1 (so making a choice will result in a 1 or 2 in cell B1)...Or you can have the user enter a 1 for Buyer and 2 for Vendor directly in cell B1. Then have the user enter the value (actual Buyer or Vendor, in B2).

Then add this formula in the column header next to the PO header, =IF($B$1=1,"Vendor","Buyer") - in my example in Cell D1 of the results sheet. This is so the appropriate header is brought up based on your choice in A1.

Now in the 2nd row under PO header (cell C2, here), enter this formula,

=CHOOSE($B$1,IF(ROW($B2)-ROW($B$1)>COUNTIF(Sheet2!$B$1:$B$15,$B$2),"",INDEX(Sheet2!$A$1:$A$15,SMALL(IF(Sheet2!$B$1:$B$15=$B$2,ROW(Sheet2!$A$1:$A$15)),ROW($B2)-ROW($B$1)))),IF(ROW($B2)-ROW($B$1)>COUNTIF(Sheet2!$C$1:$C$15,$B$2),"",INDEX(Sheet2!$A$1:$A$15,SMALL(IF(Sheet2!$C$1:$C$15=$B$2,ROW(Sheet2!$A$1:$A$15)),ROW($B2)-ROW($B$1)))))

always adjusting ranges to suit. Then confirm with Ctrl+Shift+Enter.

In D2, enter this formula, =CHOOSE($B$1,IF(ROW($B2)-ROW($B$1)>COUNTIF(Sheet2!$B$1:$B$15,$B$2),"",INDEX(Sheet2!$C$1:$C$15,SMALL(IF(Sheet2!$B$1:$B$15=$B$2,ROW(Sheet2!$C$1:$C$15)),ROW($B2)-ROW($B$1)))),IF(ROW($B2)-ROW($B$1)>COUNTIF(Sheet2!$C$1:$C$15,$B$2),"",INDEX(Sheet2!$B$1:$B$15,SMALL(IF(Sheet2!$C$1:$C$15=$B$2,ROW(Sheet2!$B$1:$B$15)),ROW($B2)-ROW($B$1)))))

with the same adjustments and confirmations.

Be careful that the column letters correspond with your database sheet.

Then just copy down these formulas as far as you need.

Example Database Sheet (Sheet2)
TestDec15.xls
ABCD
1POBuyerVendor
2PO12Joe7
3PO23Jack11
4P034Mary9
5PO35Luke6
6PO36Chris6
7PO37Jack10
8PO38Chris11
9PO39Mary2
Sheet2


Example Results Sheet with Buyer chosen:
TestDec15.xls
ABCD
1Choice (1/2):1POVendor
2Buyer/Vendor:JackPO2311
3PO3710
Results


Example Results Sheet with Vendor Chosen:
TestDec15.xls
ABCD
1Choice (1/2):2POBuyer
2Buyer/Vendor:6PO35Luke
3PO36Chris
Results


I really hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,142
Messages
5,576,318
Members
412,716
Latest member
thviid
Top