I would like to convert data in Col A to a format in Col B, so that Col B
omits the blank cells in Col A. Is this possible with a formula I can drag
down? For example:

Col A Col B
1 1
2
2 34
34 5
5 6
9
6 7
9

7

Hi!

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNT(A$1:A$10),INDEX(A$1:A$10,SMA LL(IF(A$1:A$10<"",ROW(A$1:A$10)-ROW(A$1)+1),ROWS($1:1))),"")

Copy down until you blanks.

Biff

"SteveC" wrote in message
...
I would like to convert data in Col A to a format in Col B, so that Col B
omits the blank cells in Col A. Is this possible with a formula I can
drag
down? For example:

Col A Col B
1 1
2
2 34
34 5
5 6
9
6 7
9

7

Hi,

You may want to try the following formula

In range F1:F10, enter the number 1:10

In cell D1, enter the following formula

=if(iserror(index($A$1:$A$9,small($B$1:$B9,F1),1)) ,€�€�,index($A$1:$A$9,small($B$1:$B9,F1),1))

Regards,

"SteveC" wrote:

I would like to convert data in Col A to a format in Col B, so that Col B
omits the blank cells in Col A. Is this possible with a formula I can drag
down? For example:

Col A Col B
1 1
2
2 34
34 5
5 6
9
6 7
9

7

Thanks Biff, it works great.

"Biff" wrote:

Hi!

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNT(A$1:A$10),INDEX(A$1:A$10,SMA LL(IF(A$1:A$10<"",ROW(A$1:A$10)-ROW(A$1)+1),ROWS($1:1))),"")

Copy down until you blanks.

Biff

"SteveC" wrote in message
...
I would like to convert data in Col A to a format in Col B, so that Col B
omits the blank cells in Col A. Is this possible with a formula I can
drag
down? For example:

Col A Col B
1 1
2
2 34
34 5
5 6
9
6 7
9

7

Ashish, thanks for your help.

The formula you provided returns on data -- the cells are left blank.

To clarify, I have data in Column A, and I want to Copy that data into Colum
B but without blanks. Do you have an alternative suggestion?

Thanks very much for your time.


"Ashish Mathur" wrote:

Hi,

You may want to try the following formula

In range F1:F10, enter the number 1:10

In cell D1, enter the following formula

=if(iserror(index($A$1:$A$9,small($B$1:$B9,F1),1)) ,€�€�,index($A$1:$A$9,small($B$1:$B9,F1),1))

Regards,

"SteveC" wrote:

I would like to convert data in Col A to a format in Col B, so that Col B
omits the blank cells in Col A. Is this possible with a formula I can drag
down? For example:

Col A Col B
1 1
2
2 34
34 5
5 6
9
6 7
9

7

Biff, something strange is occuring with the formula you gave me:

I have data in colums A, B and C.

In Column E, I have the following array formula:
=IF(ROWS($1:1)<=COUNT(A$1:A$1000),INDEX(A$1:A$1000 ,SMALL(IF(A$1:A$1000<"",ROW(A$1:A$1000)-ROW(A$1)+1),ROWS($1:1))),"")

In Column F, I have the following array formula:
=IF(ROWS($1:1)<=COUNT(B$1:B$1000),INDEX(B$1:B$1000 ,SMALL(IF(B$1:B$1000<"",ROW(B$1:B$1000)-ROW(B$1)+1),ROWS($1:1))),"")

In Column G, I have the following arrray formula:
=IF(ROWS($1:1)<=COUNT(C$1:C$1000),INDEX(C$1:C$1000 ,SMALL(IF(C$1:C$1000<"",ROW(C$1:C$1000)-ROW(C$1)+1),ROWS($1:1))),"")

Column E and G return values just fine, but Column F returns no data! Any
suggestions? I've spent some time on this probem but haven't been able to
fix it. Thanks....







"Biff" wrote:

Hi!

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNT(A$1:A$10),INDEX(A$1:A$10,SMA LL(IF(A$1:A$10<"",ROW(A$1:A$10)-ROW(A$1)+1),ROWS($1:1))),"")

Copy down until you blanks.

Biff

"SteveC" wrote in message
...
I would like to convert data in Col A to a format in Col B, so that Col B
omits the blank cells in Col A. Is this possible with a formula I can
drag
down? For example:

Col A Col B
1 1
2
2 34
34 5
5 6
9
6 7
9

7

I just noticed another problem. The formula does not return all values in
Column A. For example, if there is data in rows 1:150, data is only returned
in Column B only 2/3 the way through... Thanks for your help.

"Biff" wrote:

Hi!

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNT(A$1:A$10),INDEX(A$1:A$10,SMA LL(IF(A$1:A$10<"",ROW(A$1:A$10)-ROW(A$1)+1),ROWS($1:1))),"")

Copy down until you blanks.

Biff

"SteveC" wrote in message
...
I would like to convert data in Col A to a format in Col B, so that Col B
omits the blank cells in Col A. Is this possible with a formula I can
drag
down? For example:

Col A Col B
1 1
2
2 34
34 5
5 6
9
6 7
9

7

What type of data do you have in each of your columns? The sample you posted
used *ALL NUMERIC* values so the formula I suggested is based on having *ALL
NUMERIC* data.

Try changing this portion in each formula:

COUNT(A$1:A$1000)

Change to:

COUNTA(A$1:A$1000)

and use the appropriate range

Biff

"SteveC" wrote in message
...
I just noticed another problem. The formula does not return all values in
Column A. For example, if there is data in rows 1:150, data is only
returned
in Column B only 2/3 the way through... Thanks for your help.

"Biff" wrote:

Hi!

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNT(A$1:A$10),INDEX(A$1:A$10,SMA LL(IF(A$1:A$10<"",ROW(A$1:A$10)-ROW(A$1)+1),ROWS($1:1))),"")

Copy down until you blanks.

Biff

"SteveC" wrote in message
...
I would like to convert data in Col A to a format in Col B, so that Col
B
omits the blank cells in Col A. Is this possible with a formula I can
drag
down? For example:

Col A Col B
1 1
2
2 34
34 5
5 6
9
6 7
9

7