Transform excel data from Format 1 to Format 2

[karangarg05]

Hi

I am working on this problem in excel. Any recommendations will be appreciated

I want to transform data from Format 1 to Format 2 in attached sheet.

Format 1

Column A: Customer Group is at 2 levels CBP and Target. One CBP can have multiple targets. One target can fall in only one CBP

Column B: Products are at one level only

Column E,F,G: Rates are divided into 3 groups - Rate Group 1, 2 and 3

Rates vary by a combination of customer group, product, date range and rate groups

1	A	B	C	D	E	F	G
2	Customer Group	Product	Valid From	Valid Through	Rate Group 1	Rate Group 2	Rate Group 3
3	CBP 1	Product 1	1/1/2013	4/30/2013	$1.00	$0.30	$0.50
4	CBP 1	Product 1	5/1/2013	12/31/2013	$1.50	$0.40	$0.20
5	CBP 2	Product 2	1/1/2013	12/31/2013	$2.00	$0.50	$0.25
6	Target 1	Product 3	5/1/2013	12/31/2013	$2.50	$0.60	$0.10
7	Target 2	Product 4	1/1/2013	12/31/2013	$3.00	$0.70	$0.75
8	Target 3	Product 5	5/1/2013	12/31/2013	$3.50	$0.80	$1.25

<tbody>
</tbody>

Format 2

There is a unique rate for every combination of Target, Product, Valid From, Valid Thru and Rate Group

If in Format 1 customer group is CBP then rate applies to all targets within that CBP in Format 2

1	A	B	C	D	E	F	G
2	Customer Group 1	Customer Group 2	Product	Rate	Valid From	Valid Through	Rate Group
3	CBP 1	Target 1	Product 1	$1.00	1/1/2013	4/30/2013	Rate Group 1
4	CBP 1	Target 1	Product 1	$0.30	1/1/2013	4/30/2013	Rate Group 2
5	CBP 1	Target 1	Product 1	$0.50	1/1/2013	4/30/2013	Rate Group 3
6	CBP 1	Target 1	Product 1	$1.50	5/1/2013	12/31/2013	Rate Group 1
7	CBP 1	Target 1	Product 1	$0.40	5/1/2013	12/31/2013	Rate Group 2
8	CBP 1	Target 1	Product 1	$0.20	5/1/2013	12/31/2013	Rate Group 3
9	CBP 1	Target 2	Product 1	$1.00	1/1/2013	4/30/2013	Rate Group 1
10	CBP 1	Target 2	Product 1	$0.30	1/1/2013	4/30/2013	Rate Group 2
11	CBP 1	Target 2	Product 1	$0.50	1/1/2013	4/30/2013	Rate Group 3
12	CBP 1	Target 2	Product 1	$1.50	5/1/2013	12/31/2013	Rate Group 1
13	CBP 1	Target 2	Product 1	$0.40	5/1/2013	12/31/2013	Rate Group 2
14	CBP 1	Target 2	Product 1	$0.20	5/1/2013	12/31/2013	Rate Group 3
15	CBP 2	Target 3	Product 2	$2.00	1/1/2013	12/31/2013	Rate Group 1
16	CBP 2	Target 3	Product 2	$0.50	1/1/2013	12/31/2013	Rate Group 2
17	CBP 2	Target 3	Product 2	$0.25	1/1/2013	12/31/2013	Rate Group 3
18	CBP 1	Target 1	Product 3	$2.50	5/1/2013	12/31/2013	Rate Group 1
19	CBP 1	Target 1	Product 3	$0.60	5/1/2013	12/31/2013	Rate Group 2
20	CBP 1	Target 1	Product 3	$0.10	5/1/2013	12/31/2013	Rate Group 3
21	CBP 1	Target 2	Product 4	$3.00	1/1/2013	12/31/2013	Rate Group 1
22	CBP 1	Target 2	Product 4	$0.70	1/1/2013	12/31/2013	Rate Group 2
23	CBP 1	Target 2	Product 4	$0.75	1/1/2013	12/31/2013	Rate Group 3
24	CBP 2	Target 3	Product 5	$3.50	5/1/2013	12/31/2013	Rate Group 1
25	CBP 2	Target 3	Product 5	$0.80	5/1/2013	12/31/2013	Rate Group 2
26	CBP 2	Target 3	Product 5	$1.25	5/1/2013	12/31/2013	Rate Group 3

<tbody>
</tbody>

Thanks!