I have a column of 7 cells which may or may not contain comments. Such as

A1: Comment 1
A2: Comment 2
A3:
A4: Comment 3
A5: Comment 4
A6:
A7: Comment 5

i wish to have

B1: Comment 1
B2: Comment 2
B3: Comment 3
B4: Comment 4
B5: Comment 5
B6:
B7:

I've tried if statements & i can't get it right, please can any one help.
Thanks

Is this a one-off, or a regular thing - if one off, copy the entire
contents of column a and paste into column B, then select column B as a
block and sort it in ascending order whcih would get rid of the blanks.
If regular, then a macro would be your best solution.

Hi Emma
Assuming you still want to keep the original data layout in column A, in
column B enter =A1 and copy down as far as needed.
Then, select JUST column B and SortAscending
All the blanks will "fall" to the bottom of the list

--
Regards

Roger Govier


"Emma Hope" wrote in message
...
I have a column of 7 cells which may or may not contain comments. Such
as

A1: Comment 1
A2: Comment 2
A3:
A4: Comment 3
A5: Comment 4
A6:
A7: Comment 5

i wish to have

B1: Comment 1
B2: Comment 2
B3: Comment 3
B4: Comment 4
B5: Comment 5
B6:
B7:

I've tried if statements & i can't get it right, please can any one
help.
Thanks

Should have specified that using the sort function is not a possibility nor
is a macro as this is a spreadsheet reading from an access database.

It needs to be done using worksheet functions only.

"Emma Hope" wrote:

I have a column of 7 cells which may or may not contain comments. Such as

A1: Comment 1
A2: Comment 2
A3:
A4: Comment 3
A5: Comment 4
A6:
A7: Comment 5

i wish to have

B1: Comment 1
B2: Comment 2
B3: Comment 3
B4: Comment 4
B5: Comment 5
B6:
B7:

I've tried if statements & i can't get it right, please can any one help.
Thanks

how is it reading from the database? If this is being done via macro,
then the macro could be modified to ONLY return the cases with comments
- basically, you are going to be on a hiding to nothing trying to do it
with formula alone (I think!)