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

[low98]

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.

 

[NBVC]

=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]

Awesome! That works perfect, Thanks!

 

[low98]

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]

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

 

[low98]

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]

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
	A	B	C	D
1	PO	Buyer	Vendor
2	PO12	Joe	7
3	PO23	Jack	11
4	P034	Mary	9
5	PO35	Luke	6
6	PO36	Chris	6
7	PO37	Jack	10
8	PO38	Chris	11
9	PO39	Mary	2
Sheet2

Example Results Sheet with Buyer chosen:

TestDec15.xls
	A	B	C	D
1	Choice (1/2):	1	PO	Vendor
2	Buyer/Vendor:	Jack	PO23	11
3			PO37	10
Results

Example Results Sheet with Vendor Chosen:

TestDec15.xls
	A	B	C	D
1	Choice (1/2):	2	PO	Buyer
2	Buyer/Vendor:	6	PO35	Luke
3			PO36	Chris
Results

I really hope this helps!