Merge and Sort 2 worksheets into 3rd

[dc059]

There are many threads on this Forum dealing with merging tables, worksheets,
and workbooks, but I cannot locate one that suits the problem I am trying to
resolve.

Problem:
I have two worksheets of data.
The 1st (WS1) contains the following 5 headings (columns):
Date | Econ | Dist | Cost per | Total $

The 2nd (WS2) contains 3 columns:
Date | Total $ | Service Type

Both worksheets contain many rows of data that are constantly being added to
- via a data connection to 2 CSV text files. Both text files are sorted in
Date order (newest to oldest). Many of the Dates contain data in both text
files.


Result Desired:
I want to dynamically merge the two sets of data into a 3rd worksheet (WS3),
with the following 6 headings (columns):
Date | Econ | Dist | Cost per | Total $ | Service Type
and sorted into oldest to newest Date order.
Multiple entries for each Date are allowed.

BTW I am using Excel 2007.

The data constantly added to so I am looking for a method that will update
the merged table instantly and dynamically. Can anyone help me code a
formula (or series of formulas) to do this, please? By reading other similar
threads in this Forum, I am guessing there will need to be a combination if
IF, INDEX, ROW / COLUMN, INDEX, MATCH, OFFSET etc formula to make this work.
Whilst I consider myself resonably proficient with Excel, most of those
formula leave me cold.

Any help will be certainly appreciated.

 

[Herbert Seidenberg]

Excel 2007
Maybe something like this?
No hot formulas, but some code:
http://www.savefile.com/files/1837834

 

[dc059]

Herbert,

Many thanks - it looks very promising. I say promising, as I was able to
follow your instructions, but unable to get the "Update" buttons to operate.
I suspect my "Trust Center" is disabling the macros. I note the file I
downloaded was 0.5Mb, when I saved it and starting work on it the size had
reduced to 0.05MB.

Anyway, if you have the inclination to continue with this problem I have
uploaded my version of your example:
http://www.savefile.com/files/1837989

I certainly appreciate your assistance. It will save me enormous manual
input / manipulation in the future.

 

[Herbert Seidenberg]

Customized to your data.
Added macro enable instructions.
http://www.savefile.com/files/1838719

 

[dc059]

Herbert,

Brilliant - works like a charm. Many many thanks.