Skip Blanks (repeat post)

[Sachin]

Sorry for repeating the pos

Is there any worksheet function to do the following

I have seven columns; each column has variable rows of enteries (ranging from row1 to row30

Since these are captured from another calculator, the number of enteries would keep on changin
Ex
A B C D E F
26 Sep 28 Sep 01 Oct 03 Oct 05 Oct 08 Oct 13 Oc
27 Sep 29 Sep 02 Oct 04 Oct 06 Oct 09 Oct 14 Oc
28 Sep 30 Sep 03 Oct 05 Oct 07 Oct 10 Oct 15 Oc
01 Oct 08 Oct 11 Oct
12 Oct
13 Oct

Enteries in other cells are formulated to "
I need these enteries in one column in a sequential manner (For Ex
26 Se
27 Se
28 Se
28 Se
29 Se
30 Sep ....
& skip out the "

Please help! Thanx for your patienc

Regard
Sachin

 

[Andy B]

Hi

I would suggest using the SMALL() function if they are Excel dates. If you
try something similar to:
=SMALL($A$2:$F$6,ROW()-2)
where your array is A2:F6. The ROW() function will automatically increment
as you fill down the column

Hope this helps.

 

[Sachin]

not workin


----- Andy B wrote: ----

H

I would suggest using the SMALL() function if they are Excel dates. If yo
try something similar to
=SMALL($A$2:$F$6,ROW()-2
where your array is A2:F6. The ROW() function will automatically incremen
as you fill down the colum

Hope this helps

 

[Andy B]

Hi

It might assist if you gave a little more detail than 'not working'. Do you
get an error? What does it do that is not right?

 

[Sachin]

Hi! Sorry for being abrupt

It displays#NUM!

I hope you've got my requirement right; what I need is all entries of column 1 first (row-wise) followed by Column 2 and so on. This involves repeating of values like 28 Sep (as given in the example)

Any Suggestions
Regards

Sachin

 

[Andy B]

Well, I reckon that is what my post does!! It looks at the whole array and
returns the lowest number (date) first and filling down takes the next, and
the next etc.
Make sure that the range is set correctly (A2:F6). I should have said that
you may need to change the ROW() option to make the first cell look at row
1. If you enter the formula I sent into row 3, and fill down, it should work
for you. Let me know how it goes!

--
Andy.

Hi! Sorry for being abrupt

It displays#NUM!

I hope you've got my requirement right; what I need is all entries of

column 1 first (row-wise) followed by Column 2 and so on. This involves
repeating of values like 28 Sep (as given in the example)

 

[Sachin]

Yeh! Adjusted the range; actually it's $B$5:$H$3

but am not sure what to enter in row(


----- Andy B wrote: ----

Well, I reckon that is what my post does!! It looks at the whole array an
returns the lowest number (date) first and filling down takes the next, an
the next etc
Make sure that the range is set correctly (A2:F6). I should have said tha
you may need to change the ROW() option to make the first cell look at ro
1. If you enter the formula I sent into row 3, and fill down, it should wor
for you. Let me know how it goes

--
Andy

Hi! Sorry for being abrup

column 1 first (row-wise) followed by Column 2 and so on. This involve
repeating of values like 28 Sep (as given in the example

 

[Andy B]

Well, the result of the ROW() bit needs to be 1 in your first cell.
So, if you want to have the first formula in A1, use just ROW() and fill
down. If you want to have the first formula in A2 use ROW()-1 and fill down.
etc
The ROW() function just returns the row number, which increases as you fill
down.

 

[Sachin]

Than

Just to confir
The range is B5:H3

The formula in A5 =SMALL($B$5:$H$37,ROW()-4) & then drag dow

How do I get rid of the #NUM! (can I use a Helper column to eliminate this?

Regard
Sachin

 

[Andy B]

Hi

One way is to enclose the formula in an ISERROR formula:
=IF(ISERROR(SMALL($B$5:$H$37,ROW()-4)),"",SMALL($B$5:$H$37,ROW()-4))

 

[Andy B]

The formula I've posted will eliminate the #NUM error. Your new post has
also been answered by Chip who gives the same response as mine!!