liner data into tabular format

HI All,

I have a file with data in leniar format.

For example:

Column A Column B Column C Column D Column E
OC ID Client Name Date Mail FTP
OCHY07 HSBC 1 10 25
OCHY07 CENLAR 1 20 30
OCRY08 HSBC 1 10

Now i want a macro to post this data into below format to each respective sheet named acroding to column C(date)

Sheet 1 in other workbook look like

Column A Column B,C&D(merged) Column E,F&G(merged)
OC ID HSBC CENLAR
MAIL FTP TOTAL MAIL FTP TOTAL

OCHY07 10 25 35 20 30 35
OCRY08 10 10

Please respond me with the complete code and attached file to complete this function.
thanks in advance.