Bringing together data

[Guest]

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

 

[aidan.heritage]

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.

 

[Roger Govier]

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 Sort>Ascending
All the blanks will "fall" to the bottom of the list

 

[Guest]

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.

 

[aidan.heritage]

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!)