Hi
I want to transform some data into a table on a new sheet but to me it seems complicated so I am not sure if it is possible. Below is the source data format
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
I want the script to work down column A and for every entry go along the row and transform the data into the following format.
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
The number of rows in the new column should = the number of values there are against Dates. The first 4 columns are copied across and then repeated for each date that has a value. For each customer there can be up to 12 rows for the 12 months of the year.
Can anyone help, thanks.
Kerry
I want to transform some data into a table on a new sheet but to me it seems complicated so I am not sure if it is possible. Below is the source data format
01/01/2014 | 01/02/2014 | 01/03/2014 | 01/04/2014 | 01/05/2014 | 01/06/2014 | 01/07/2014 | 01/08/2014 | 01/09/2014 | 01/10/2014 | 01/11/2014 | 01/12/2014 | ||||
Column A | Column B | Column C | Column D | Jan-14 | Feb-14 | Mar-14 | Apr-14 | May-14 | Jun-14 | Jul-14 | Aug-14 | Sep-14 | Oct-14 | Nov-14 | Dec-14 |
Customer A | 50% | DM | £1,000 | £1,000 | £1,000 | ||||||||||
Customer B | 150% | DM | £1,000 | £1,000 | £1,000 | £1,000 | £1,000 |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
I want the script to work down column A and for every entry go along the row and transform the data into the following format.
Column A | Column B | Column C | Column D | Value | Date |
Customer A | 50% | DM | £1,000 | 01/01/2014 | |
Customer A | 50% | DM | £1,000 | 01/02/2014 | |
Customer A | 50% | DM | £1,000 | 01/03/2014 | |
Customer B | 150% | DM | £1,000 | 01/03/2014 | |
Customer B | 150% | DM | £1,000 | 01/04/2014 | |
Customer B | 150% | DM | £1,000 | 01/05/2014 | |
Customer B | 150% | DM | £1,000 | 01/06/2014 | |
Customer B | 150% | DM | £1,000 | 01/07/2014 |
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
The number of rows in the new column should = the number of values there are against Dates. The first 4 columns are copied across and then repeated for each date that has a value. For each customer there can be up to 12 rows for the 12 months of the year.
Can anyone help, thanks.
Kerry