Sorting 5 columns of data based on column A

[CAT]

Hi everyone, hope someone can help me
(I am using Excell 2007, a fairly new user but getting better and better
everyday!)

What I would like to achieve:
I am downloading some data into a spreadsheet daily. Before I can copy it
into my workbook, I need to sort it:
1) Chronologically, by the data in column A, making sure that all the data
in the other 4 columns is linked - Column A only contains 1 row of data
(Time) but columns B, C, D and E can contain between 4 and 20 rows of data
and are linked to column A;
2) Within each "Time" section of column A, the data of column D
Alphabetically, whilst keeping the data in columns B, C and E linked to it.

Example:
Col A Col B Col C Col D Col E
MR 1.35 1 33143 Sydney JJ Quinn
2 600 Paris JG Given
3 72 London E Williams
etc...
MR 6.00
AS 1.50 data data data data
etc...
AS 6.30 data data data data

What it should look like after sorting:
MR1.35 3 72 London E Williams
2 600 Paris JG Given
1 33143 Sydney JJ Quinn
AS1.50 data data data data
data data data data
MR 2.35 data data data data
Etc...


What I am doing instead:
In trying the filter and sort function, all I get is a Sort on the first
column and none of the other columns get linked which is completely useless.
So I have to copy the first lot of chronologically sorted data into my
workbook, and then insert the other sets by copying and inserting blocks of
data in the appropriate time sequence slots which is all very time consuming
and can lead to errors.

Is there a way?
Many thanks in advance

 

[JasonP CCTM LV]

For the sort to work properly, you would need each cell in column A to be
filled if there is something on that row in the other columns.

MR1.35 3 72 London E Williams
MR1.35 2 600 Paris JG Given
MR1.35 1 33143 Sydney JJ Quinn
AS1.50 data data data data
AS1.50 data data data data
MR 2.35 data data data data

Once you have that done, then you would sort by column A then by Column D.

 

[Sean Timmons]

You can fill in the blank rows by entering =A2 in cell A3. Copy the formula,
highlight column A, Edit - Go to - Special - Blanks then paste. Copy the
column and paste-special values to remove formula...

 

[CAT]

Thank you for that, but it does mean copying and pasting right through in
column A, then sorting, then deleting it all again except for the first time
entry before copying the final sorted version to the workbook. Is there
another, quicker way?
Also, the alphabetical sorting of column D should be only applied to the
total of rows in each "time zone" but NOT to the overall column D. At the
moment, I highlight the block of 5 columns and sort Column D manually in each
section; Another quicker way?
I am trying to automate/speed up all of this as much as possible as I have
much more work to do on the worksheet later on. But thank you anyway for the
suggestion.

 

[CAT]

Thank you Jason and Sean
I will try the formula copying and pasting - sounds a little faster and I'm
sure will work.
Now for Column D to be sorted alphabetically within each "Time" session: is
there a quicker way than what I am doing now, i.e. highlighting each block of
5 columns within each "time zone" and sorting col D manually?

 

[Gord Dibben]

Fill in the blanks in Column A with data from above.

Select column A and F5>Special>Blanks>OK

In active blank cell type an + sign.

Point or arrow to cell above and hit CTRL + ENTER

Copy>Paste Special>Values>OK>Esc.

Sort on column A after selecting all columns.


Gord Dibben MS Excel MVP

 

[CAT]

Thank you very much Gord, Jason and Sean for your help, it works ! and fast
too, great stuff. I now have to find a quick way of dealing with the sorting
of column D and all will be done.
Thank you again
CAT