building list from a column of data
 

excel2003
i have a range name SPH1 (='Salar'!$I$146:$I$2033)
it will cover all the weekly entry of values fed from our office.

i146 contains the header
i2033 is always empty.
i147:i2032 not empty with values 0+.

after one week the range will increase for about 100.

i need to create a dynamic list in a separate -Sheet1- like under a name of
SPHU (='Sheet1'!B:B).

SPHU must contain only the unique values entered in SPH1.
so, SPHU may look somethiing like this..
B1 will contain the header
B2:B#
e.g.( 0,401,500,502.5,650.33336,700,max!)

I will use this list from Sheet1 to review other existing workbooks -
somekind of validation work.
thanks and regards,
driller
--
*****
birds of the same feather flock together..

building list from a column of data
 

Is VBA O.K. for this??
--
Gary''s Student - gsnu200725


"driller" wrote:

excel2003
i have a range name SPH1 (='Salar'!$I$146:$I$2033)
it will cover all the weekly entry of values fed from our office.

i146 contains the header
i2033 is always empty.
i147:i2032 not empty with values 0+.

after one week the range will increase for about 100.

i need to create a dynamic list in a separate -Sheet1- like under a name of
SPHU (='Sheet1'!B:B).

SPHU must contain only the unique values entered in SPH1.
so, SPHU may look somethiing like this..
B1 will contain the header
B2:B#
e.g.( 0,401,500,502.5,650.33336,700,max!)

I will use this list from Sheet1 to review other existing workbooks -
somekind of validation work.
thanks and regards,
driller
--
*****
birds of the same feather flock together..

building list from a column of data
 

i147:i2032 not empty with values 0+.

Try this entered in D2. D1 is a column header (or empty cell, or just not a
number)

=IF(ROWS($1:1)<=COUNT(1/FREQUENCY(rng,rng)),INDEX(rng,MATCH(0,INDEX(COUNTI F(D$1:D1,rng),,1),0)),"")

Copy down until you get blanks.

Biff

"driller" wrote in message
...
excel2003
i have a range name SPH1 (='Salar'!$I$146:$I$2033)
it will cover all the weekly entry of values fed from our office.

i146 contains the header
i2033 is always empty.
i147:i2032 not empty with values 0+.

after one week the range will increase for about 100.

i need to create a dynamic list in a separate -Sheet1- like under a name
of
SPHU (='Sheet1'!B:B).

SPHU must contain only the unique values entered in SPH1.
so, SPHU may look somethiing like this..
B1 will contain the header
B2:B#
e.g.( 0,401,500,502.5,650.33336,700,max!)

I will use this list from Sheet1 to review other existing workbooks -
somekind of validation work.
thanks and regards,
driller
--
*****
birds of the same feather flock together..

building list from a column of data
 

thanks
i tried to use the formulas, then macro recording seems to fit for further
more routine computation.

regards,
driller
--
*****
birds of the same feather flock together..

Try this entered in D2. D1 is a column header (or empty cell, or just not a
number)

=IF(ROWS($1:1)<=COUNT(1/FREQUENCY(rng,rng)),INDEX(rng,MATCH(0,INDEX(COUNTI F(D$1:D1,rng),,1),0)),"")

Copy down until you get blanks.