Duplicates in database validation

J

Jennifer

Hey all,
My database has duplicate invoice entries. This is good. Problem arrises on
another worksheet where I want to use these invoice numbers in a validation
drop box but I don't want to see
1
1
1
3
3
2
2
I would like to only see one of each invoice entry in the validation. Anyone
have any ideas. Thank you, Jennifer
 
M

Max

One play to try ..

Assume the invoice #s are in Sheet1, in A1 down
1
1
1
3
3
2
Click to expand...
etc

Put in C1:
=IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",A1+ROW()/10^10))

Put in B1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(C:C,ROWS($A$1
:A1)),C:C,0)))

Select B1:C1, copy down to say, C100
to cover the max expected data in col A

Col B will return an ascending sort of
the unique invoice #s in col A

Now to create a dynamic range for use in the DV

Click Insert > Name > Define
Put in "Names in workbook": Invoice
Put in "Refers to":
=OFFSET(Sheet1!$B$1,,,SUMPRODUCT(--(Sheet1!$B$1:$B$100<>"")))
Click OK

Try out the DV ..

In another / new sheet

Select the DV range
Click Data > Validation
Make the settings as:
Under "Allow": List
Source: =Invoice
Click OK

The DV droplists will show the ascending sort
of the unique invoice #s in col A in Sheet1
 
Top