Bring fields from header to columns

[AG]

Hi all,
I need help on automating the following:
everyday I get a report in the format as described below

title1: abc
title2: xyz

1 Name1 Id1 Qty1 Cust1 Diff1
2 Name2 Id2 Qty2 Cust2 Diff2
3 Name3 Id3 Qty3 Cust3 Diff3
...
...

title1: pqr
title2: def

1 Name4 Id4 Qty4 Cust4 Diff4
2 Name5 Id5 Qty5 Cust5 Diff5

the number of records under each title1 and title2 vary each day.

I would like to have the output as below:

1 abc. xyz. Name1 Id1 Qty1 Cust1 Diff1
2 abc. xyz. Name2 Id2 Qty2 Cust2 Diff2
3 abc. xyz. Name3 Id3 Qty3 Cust3 Diff3
4 pqr. def. Name4 Id4 Qty4 Cust4 Diff4
5 pqr. def. Name5 Id5 Qty5 Cust5 Diff5

that is I want the output to be nicely arranged in the columns with
the title1 and title2 info also captured in the columns as opposed to
as headers. Again the length of the report varies and also the number
of records within each title1 & title2.

Can someone please help me with a code to automate this task? Thanks a
lot in advance for looking into this.

Regards,
ag

 

[dk]

Hi all,
I need help on automating the following:
everyday I get a report in the format as described below

title1: abc
title2: xyz

1    Name1    Id1    Qty1    Cust1    Diff1
2    Name2    Id2    Qty2    Cust2    Diff2
3    Name3    Id3    Qty3    Cust3    Diff3
..
..

title1: pqr
title2: def

1    Name4    Id4    Qty4    Cust4    Diff4
2    Name5    Id5    Qty5    Cust5    Diff5

the number of records under each title1 and title2 vary each day.

I would like to have the output as below:

1    abc.    xyz.    Name1    Id1    Qty1    Cust1    Diff1
2    abc.    xyz.    Name2    Id2    Qty2    Cust2    Diff2
3    abc.    xyz.    Name3    Id3    Qty3    Cust3    Diff3
4    pqr.     def.    Name4    Id4    Qty4    Cust4    Diff4
5    pqr.     def.    Name5    Id5    Qty5    Cust5    Diff5

that is I want the output to be nicely arranged in the columns with
the title1 and title2 info also captured in the columns as opposed to
as headers. Again the length of the report varies and also the number
of records within each title1 & title2.

Can someone please help me with a code to automate this task? Thanks a
lot in advance for looking into this.

Regards,
ag

You did not mention if you get this data in an EXCEL SHEET?

 

[AG]

Hi dk, Yes I get this data on an excel sheet.

 

[dk]

Hi dk, Yes I get this data on an excel sheet.

I would suggest you following algo:

1. Get the last data cell in the worksheet.
2. Loop untill you reach the last data cell
3. Read each row and check if it has 'title' in there.
4. If yes read the title1 and title2 into two variables.
5. Write into the new sheet the titles and then the data record.
6. Repeat the step 5 untill you come across next title1 and title2.
7. Update the variables with new title1 and title2. Repeat steps 5 to
7.
8. At the end of this, you may delete the old worksheet.

Hope it helps
-DK