How do I return the unique entries from a column to a listbox

[Dave Mc]

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]

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

 

[Biff]

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

 

[Harlan Grove]

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:"&RowsInBigRange)))-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,"<>#NUM!"),1)

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