Sort two tables of data

[AMH]

I am not sure if this has been asked/answered before but I couldn’t find
anything that would help me, so please if you have any ideas they would be
appreciated :

I have a spreadsheet that has a table within it :

Project P1 P1 P2
Task Test 1 Test 2 Test 1
ID 123 124 223
Start 15/5/09 20/6/09 15/7/09
Finish 16/5/09 25/6/09 15/8/09
Date
1/1/09 For wk09_15
2/1/09 for wk08_50
Etc.


The etc extends to 3000 rows and displays history information built up over
a period of time

The easiest way would have to been to build the spreadsheet with all the
data going from left to right rather than top to bottom but I needed more
columns than were available (i.e. 3000 rather than 256) so built it as above.

This was fine when I had less than 256 tasks however there are now over 256
so I have created a 2nd data table with exactly the same format as above, but
running from line 4000.

The tables are populated from a weekly run macro and this is fine

I can sort both tables (I sort on Project , Start) individually and can
remove tasks not updated and add as required.

However I want to be able to sort both tables together as new tasks are
added into the source data they get added to table 2, but if the rest of the
projects tasks are detailed in table 1 then when I produce a report from
these two tables combined the project are not sorted correctly (the report
cant be sorted as it is in a summary spreadsheet and has a series of lookup
fields so when you sort you don’t actually change the data. I had thought
about making a hard copy of the table but it is dynamic in as much as you can
select which weeks data you want to look at.

E.g. if a task from P2 is added it will be added to table 2 yet the other 50
tasks for P2 are in table 1

So basically is there any way to sort to tables of data horizontally on two
fields ? I don’t mind code being the answer

 

[Bernd P]

Hello,

Easiest escape might be Excel 2007 which offers more than 256 columns.

Regards,
Bernd

 

[joel]

There is a option button in the sort window to allow sorting left - to -right.

 

[AMH]

Thakns Joel I can sort left to right thats not the issue, its the fact that I
want to sort two set of data in seperate tables left to right

 

[AMH]

Thanks Bernd,

I have explored this route unfortunately my organisation will not allow me
to upgrade to 2007

 

[joel]

Yes I can write a macro but there is still problems. When you get to the end
of the first table a project could end up in both tables. Won't it be better
to put each project/task on its own worksheet?

I could generat e a macro that has an option to split the summary sheet into
Tasks or projects and then sort each sheet. May be generate a new workbook
with each project on a sheet and create a new workbook with each task on each
own sheet. Even create a seperate workbook for each task and each project.