Hi,
I need to transform a raw extract into a new sheet for a pivot source. The raw extract has transactional information + check-in and check-out as shown below:
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
Objective > create a macro that create a new table in a new sheet .
The new table should have 28 rows ( 5 + 5 + 14) with:
(1) an additional colum DATE with the actual date of each stay and;
(2) an new column Average Price = (Total Payout - Total Refunded) / Number of Nights.
I guess I need a For loop but don't have the competency to write this code. I guess it should go like this:
For each row in range, consider that check in date and create a new row until check out date.
Many thanks for your help.
Bless,
Matt
I need to transform a raw extract into a new sheet for a pivot source. The raw extract has transactional information + check-in and check-out as shown below:
LISTING'S NICKNAME | CHECK OUT | CHECK IN | SOURCE | ΣTOTAL PAYOUT | ΣNUMBER OF NIGHTS | ΣTOTAL REFUNDED |
BL031 | 12/19/2018 | 12/14/2018 | Booking.com | £1 495 | 5 | |
BL032 | 12/19/2018 | 12/14/2018 | Booking.com | £1 495 | 5 | £100 |
BL031 | 01/23/2019 | 01/09/2019 | Booking.com | £4 060 | 14 |
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
Objective > create a macro that create a new table in a new sheet .
The new table should have 28 rows ( 5 + 5 + 14) with:
(1) an additional colum DATE with the actual date of each stay and;
(2) an new column Average Price = (Total Payout - Total Refunded) / Number of Nights.
I guess I need a For loop but don't have the competency to write this code. I guess it should go like this:
For each row in range, consider that check in date and create a new row until check out date.
Many thanks for your help.
Bless,
Matt