Duplicates in database validation
 

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
--
Though daily learning, I LOVE EXCEL!
Jennifer

One play to try ..

Assume the invoice #s are in Sheet1, in A1 down

1
1
1
3
3
2
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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Jennifer" wrote in message
...
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
--
Though daily learning, I LOVE EXCEL!
Jennifer

Here's a sample file with the implemented construct:
http://flypicture.com/p.cfm?id=53209
(Right-click on the link: "Download File"
at the top in the page, just above the ads)
File: Jennifer_misc_1.xls

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----