Is this even possible without recording a macro. I have a column with
setting's entered. say b4:b28. In this column diffrent chemical settings are
entered. You may start the day at 130 but during the course of the day this
may change. The changes are entered in the same column but diffrent row. What
I need is at the end of the day I want the settings to be arranged in the top
spots of a diffrent column. I really do not want to record a macro to sort if
I do not have to because I have multiply columns with different settings. I
want to skip the blank cells and have the cells with settings in at the top
of a seperate column. The cells between the settings are blank.

Thanks in adavance
Scott

Example Finish if possible
B4
183 183
134
120

134
120



etc to b28

On Thu, 8 Dec 2005 07:29:06 -0800, "scott"
wrote:

Is this even possible without recording a macro. I have a column with
setting's entered. say b4:b28. In this column diffrent chemical settings are
entered. You may start the day at 130 but during the course of the day this
may change. The changes are entered in the same column but diffrent row. What
I need is at the end of the day I want the settings to be arranged in the top
spots of a diffrent column. I really do not want to record a macro to sort if
I do not have to because I have multiply columns with different settings. I
want to skip the blank cells and have the cells with settings in at the top
of a seperate column. The cells between the settings are blank.

Thanks in adavance
Scott

Example Finish if possible
B4
183 183
134
120

134
120



etc to b28

1. Download and Install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. Enter the array formula =VSORT(B4:B28) into the cells of your Result
column.

To enter this formula:
1. Select the target range (e.g. Z4:Z28)
2. Type or paste in =VSORT(B4:B28)
3. Hold down <ctrl<shift while hitting <enter. Excel will place
braces {...} around the formula and also enter the formula in every selected
cell.

Note that this formula, as written, is volatile so will update automatically as
you enter data.

Note also that the "blanks" will be displayed at the end so there is no need to
specifically test for them.




--ron

Not sure what result you want, if it's the maximum value, enter in B5

MAX(B4:B28)


--
tghcogo
------------------------------------------------------------------------
tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494
View this thread: http://www.excelforum.com/showthread...hreadid=491846

Ron

Thanks for the tip but I do not think this will work. All it returns is the
last setting that was entered. I am still playing with it but I am unsure if
it will work
Scott

"Ron Rosenfeld" wrote:

On Thu, 8 Dec 2005 07:29:06 -0800, "scott"
wrote:

Is this even possible without recording a macro. I have a column with
setting's entered. say b4:b28. In this column diffrent chemical settings are
entered. You may start the day at 130 but during the course of the day this
may change. The changes are entered in the same column but diffrent row. What
I need is at the end of the day I want the settings to be arranged in the top
spots of a diffrent column. I really do not want to record a macro to sort if
I do not have to because I have multiply columns with different settings. I
want to skip the blank cells and have the cells with settings in at the top
of a seperate column. The cells between the settings are blank.

Thanks in adavance
Scott

Example Finish if possible
B4
183 183
134
120

134
120



etc to b28

1. Download and Install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. Enter the array formula =VSORT(B4:B28) into the cells of your Result
column.

To enter this formula:
1. Select the target range (e.g. Z4:Z28)
2. Type or paste in =VSORT(B4:B28)
3. Hold down <ctrl<shift while hitting <enter. Excel will place
braces {...} around the formula and also enter the formula in every selected
cell.

Note that this formula, as written, is volatile so will update automatically as
you enter data.

Note also that the "blanks" will be displayed at the end so there is no need to
specifically test for them.




--ron

tghcogo

No I do not need max value. I just need the settings to elimante the blank
cells(no changes made) and to show the setting changes to be at the top of a
column. Hope this helps.
Scott

"tghcogo" wrote:


Not sure what result you want, if it's the maximum value, enter in B5

MAX(B4:B28)


--
tghcogo
------------------------------------------------------------------------
tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494
View this thread: http://www.excelforum.com/showthread...hreadid=491846

Ron

I have been playing wityh this and now I can not close out of excel. It
keeps saying can not changeand array and will not let me do anything.
Scott

"Ron Rosenfeld" wrote:

On Thu, 8 Dec 2005 07:29:06 -0800, "scott"
wrote:

Is this even possible without recording a macro. I have a column with
setting's entered. say b4:b28. In this column diffrent chemical settings are
entered. You may start the day at 130 but during the course of the day this
may change. The changes are entered in the same column but diffrent row. What
I need is at the end of the day I want the settings to be arranged in the top
spots of a diffrent column. I really do not want to record a macro to sort if
I do not have to because I have multiply columns with different settings. I
want to skip the blank cells and have the cells with settings in at the top
of a seperate column. The cells between the settings are blank.

Thanks in adavance
Scott

Example Finish if possible
B4
183 183
134
120

134
120



etc to b28

1. Download and Install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. Enter the array formula =VSORT(B4:B28) into the cells of your Result
column.

To enter this formula:
1. Select the target range (e.g. Z4:Z28)
2. Type or paste in =VSORT(B4:B28)
3. Hold down <ctrl<shift while hitting <enter. Excel will place
braces {...} around the formula and also enter the formula in every selected
cell.

Note that this formula, as written, is volatile so will update automatically as
you enter data.

Note also that the "blanks" will be displayed at the end so there is no need to
specifically test for them.




--ron

Ron

Got it figured out I can enter functions and close out. Any more tips
Scott

"Ron Rosenfeld" wrote:

On Thu, 8 Dec 2005 07:29:06 -0800, "scott"
wrote:

Is this even possible without recording a macro. I have a column with
setting's entered. say b4:b28. In this column diffrent chemical settings are
entered. You may start the day at 130 but during the course of the day this
may change. The changes are entered in the same column but diffrent row. What
I need is at the end of the day I want the settings to be arranged in the top
spots of a diffrent column. I really do not want to record a macro to sort if
I do not have to because I have multiply columns with different settings. I
want to skip the blank cells and have the cells with settings in at the top
of a seperate column. The cells between the settings are blank.

Thanks in adavance
Scott

Example Finish if possible
B4
183 183
134
120

134
120



etc to b28

1. Download and Install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. Enter the array formula =VSORT(B4:B28) into the cells of your Result
column.

To enter this formula:
1. Select the target range (e.g. Z4:Z28)
2. Type or paste in =VSORT(B4:B28)
3. Hold down <ctrl<shift while hitting <enter. Excel will place
braces {...} around the formula and also enter the formula in every selected
cell.

Note that this formula, as written, is volatile so will update automatically as
you enter data.

Note also that the "blanks" will be displayed at the end so there is no need to
specifically test for them.




--ron

Ron

Hey great thanks I have it figured out and it works. Thank you so much. I
was not selecting enough cells doh. As soon as I did that bingo bango, great
job

"Ron Rosenfeld" wrote:

On Thu, 8 Dec 2005 07:29:06 -0800, "scott"
wrote:

Is this even possible without recording a macro. I have a column with
setting's entered. say b4:b28. In this column diffrent chemical settings are
entered. You may start the day at 130 but during the course of the day this
may change. The changes are entered in the same column but diffrent row. What
I need is at the end of the day I want the settings to be arranged in the top
spots of a diffrent column. I really do not want to record a macro to sort if
I do not have to because I have multiply columns with different settings. I
want to skip the blank cells and have the cells with settings in at the top
of a seperate column. The cells between the settings are blank.

Thanks in adavance
Scott

Example Finish if possible
B4
183 183
134
120

134
120



etc to b28

1. Download and Install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

2. Enter the array formula =VSORT(B4:B28) into the cells of your Result
column.

To enter this formula:
1. Select the target range (e.g. Z4:Z28)
2. Type or paste in =VSORT(B4:B28)
3. Hold down <ctrl<shift while hitting <enter. Excel will place
braces {...} around the formula and also enter the formula in every selected
cell.

Note that this formula, as written, is volatile so will update automatically as
you enter data.

Note also that the "blanks" will be displayed at the end so there is no need to
specifically test for them.




--ron

Ron

Need help. I wanted to change where I put this array and it will not let me
do anything. I do not remember how I got out of it the last time. help

thanks scott


"scott" wrote:

Is this even possible without recording a macro. I have a column with
setting's entered. say b4:b28. In this column diffrent chemical settings are
entered. You may start the day at 130 but during the course of the day this
may change. The changes are entered in the same column but diffrent row. What
I need is at the end of the day I want the settings to be arranged in the top
spots of a diffrent column. I really do not want to record a macro to sort if
I do not have to because I have multiply columns with different settings. I
want to skip the blank cells and have the cells with settings in at the top
of a seperate column. The cells between the settings are blank.

Thanks in adavance
Scott

Example Finish if possible
B4
183 183
134
120

134
120



etc to b28

Ron

Why can't I delete this array?
Scott

"scott" wrote:

Is this even possible without recording a macro. I have a column with
setting's entered. say b4:b28. In this column diffrent chemical settings are
entered. You may start the day at 130 but during the course of the day this
may change. The changes are entered in the same column but diffrent row. What
I need is at the end of the day I want the settings to be arranged in the top
spots of a diffrent column. I really do not want to record a macro to sort if
I do not have to because I have multiply columns with different settings. I
want to skip the blank cells and have the cells with settings in at the top
of a seperate column. The cells between the settings are blank.

Thanks in adavance
Scott

Example Finish if possible
B4
183 183
134
120

134
120



etc to b28

Typically with arrays, you must edit the entire array. Highligh the
same range that you started with then press F2 to edit or delete to
delete. You cannot delete just one cell of the array.

- John

John

I am really having trouble deleting this array. I highlighted press F2 and
delete and nothing happens.The first array deletes but the others are still
there. Excel keeps saying You can not change an array. What am I doing wrong?
The array works I just want to put it in a different location.

Scott

"John Michl" wrote:

Typically with arrays, you must edit the entire array. Highligh the
same range that you started with then press F2 to edit or delete to
delete. You cannot delete just one cell of the array.

- John

John

Thanks I must have had my head where the sun doesn't shine. Just oush delete
it works....doh!!!!!

Thanks for all your help

"John Michl" wrote:

Typically with arrays, you must edit the entire array. Highligh the
same range that you started with then press F2 to edit or delete to
delete. You cannot delete just one cell of the array.

- John

On Thu, 8 Dec 2005 08:38:08 -0800, "scott"
wrote:

Ron

Hey great thanks I have it figured out and it works. Thank you so much. I
was not selecting enough cells doh. As soon as I did that bingo bango, great
job

Glad you got it working. Thanks for the feedback.

And yes, entering formulas that return arrays is not something that is commonly
done, but it is a useful feature of Excel; and there are some built-in
functions that do the same (LINEST comes to mind).


--ron

On Thu, 8 Dec 2005 08:49:03 -0800, "scott"
wrote:

Ron

Need help. I wanted to change where I put this array and it will not let me
do anything. I do not remember how I got out of it the last time. help

thanks scott

You need to select ALL the cells where you entered the array formula. In other
words, if you initially entered it in Z4:Z28, you need to select that entire
range. Then you will be able to delete it.
--ron