I have a colum with 30 odd thousand rows.
There are only 6 or so unique item-codes in it (eg BLD,
COW, MCU...)
I want to use a list box to give an option for a user to
select from.
I have set up a dynamic range (because the number of rows
will change each month) and I have listed this as the
souce for the listbox datainput.
Unfortunately it returns all of the items in the range,
not just the unique items.
Is there a named range fromula I can use or is it a VBA
issue?

see the extract below from pearsons webpage(you can do this even to 2
dimensional range)-copied from my notes.
===============
You can do this with a very simple array formula.

=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

Enter this formula in the first cell of the range you want to contain the
unique entries. Change A1 and $A$1 to the first cell in the range
containing the from which data that you want to extract unique items. Then,
use Fill Down (from the Edit menu) to fill the formula down to as many rows
as you need to hold the unique entries (i.e., up to as many rows as there
are in the original range.)

You can then transfer these values to another range of cells and eliminate
the
blank entries. See "Eliminating Blank Cells" for details about how to do
this.

http://www.cpearson.com/excel/duplicat.htm

===============================
Dave Mc wrote in message
...
I have a colum with 30 odd thousand rows.
There are only 6 or so unique item-codes in it (eg BLD,
COW, MCU...)
I want to use a list box to give an option for a user to
select from.
I have set up a dynamic range (because the number of rows
will change each month) and I have listed this as the
souce for the listbox datainput.
Unfortunately it returns all of the items in the range,
not just the unique items.
Is there a named range fromula I can use or is it a VBA
issue?

Hi!

A couple of possibilities:

Use an advanced filter to extract the unique values to a
new location and use that new location as a dynamic range
for the source of your list box.

Use formulas to do the same as above.

Are the number of unique values about the same each month?
If so, you'd probably be better off using the filter.

If you used formulas to extract the values the initial
amount of work is greater but should only need to be done
once. Using a filter is easier and quicker but would need
to be done every month. You could record a macro and put
the filter operation on a button.

Biff

-----Original Message-----
I have a colum with 30 odd thousand rows.
There are only 6 or so unique item-codes in it (eg BLD,
COW, MCU...)
I want to use a list box to give an option for a user to
select from.
I have set up a dynamic range (because the number of rows
will change each month) and I have listed this as the
souce for the listbox datainput.
Unfortunately it returns all of the items in the range,
not just the unique items.
Is there a named range fromula I can use or is it a VBA
issue?
.

Hi!

That countif formula would require that it be copied the
length of entire list of values, ~30K.

This formula is much better and eliminates blanks. If the
list to extract values is in A1:A30000, leave cell B1
empty and enter this ARRAY formula in B2:

=INDEX($A$1:$A$30000,MATCH(0,COUNTIF
($B$1:B1,$A$1:$A$3000),0))

Copy down until you get #N/A errors.

Biff

-----Original Message-----
see the extract below from pearsons webpage(you can do
this even to 2
dimensional range)-copied from my notes.
===============
You can do this with a very simple array formula.

=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

Enter this formula in the first cell of the range you
want to contain the
unique entries. Change A1 and $A$1 to the first cell in
the range
containing the from which data that you want to extract
unique items. Then,
use Fill Down (from the Edit menu) to fill the formula
down to as many rows
as you need to hold the unique entries (i.e., up to as
many rows as there
are in the original range.)

You can then transfer these values to another range of
cells and eliminate
the
blank entries. See "Eliminating Blank Cells" for details
about how to do
this.

http://www.cpearson.com/excel/duplicat.htm

===============================
Dave Mc wrote in
message
...
I have a colum with 30 odd thousand rows.
There are only 6 or so unique item-codes in it (eg BLD,
COW, MCU...)
I want to use a list box to give an option for a user to
select from.
I have set up a dynamic range (because the number of
rows
will change each month) and I have listed this as the
souce for the listbox datainput.
Unfortunately it returns all of the items in the range,
not just the unique items.
Is there a named range fromula I can use or is it a VBA
issue?


.

"Biff" wrote...
That countif formula would require that it be copied the
length of entire list of values, ~30K.

This formula is much better and eliminates blanks. If the
list to extract values is in A1:A30000, leave cell B1
empty and enter this ARRAY formula in B2:

=INDEX($A$1:$A$30000,MATCH(0,COUNTIF
($B$1:B1,$A$1:$A$3000),0))

Copy down until you get #N/A errors.
....

Since it appears the data changes every month and presumably remains fixed
during the month, better to use an advanced filter and just make that part
of the monthly data revision process.

If you must use formulas, and the dynamic data range were named BigRange and
the defined name RowsInBigRange were defined as =ROWS(BigRange), and if
there would never be more than, say, 100 distinct items, select a 100 row by
1 column range and enter the array formula

=T(OFFSET(BigRange,
SMALL(IF(MATCH(BigRange,BigRange,0)=ROW(INDIRECT(" 1:"&RowsInBigRange)),
MATCH(BigRange,BigRange,0),""),ROW(INDIRECT("1:"&R owsInBigRange)))-1,0))

If this were entered in X1:X100, then define DistinctItems referring to

=OFFSET($X$1:$X$100,0,0,COUNTIF($X$1:$X$100,"<#NU M!"),1)

and use DistinctItems as the source for the validation drop-down list.