Excell help

S

scott

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
T

tghcogo

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

MAX(B4:B28
 
S

scott

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
 
S

scott

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
 
S

scott

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
 
S

scott

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
 
S

scott

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
 
J

John Michl

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
 
S

scott

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
 
S

scott

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
Top