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

[TABLE="width: 743"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Customer Group[/TD]
[TD]Product[/TD]
[TD]Valid From[/TD]
[TD]Valid Through[/TD]
[TD]Rate Group 1[/TD]
[TD]Rate Group 2[/TD]
[TD]Rate Group 3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CBP 1[/TD]
[TD]Product 1[/TD]
[TD]1/1/2013[/TD]
[TD]4/30/2013[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$0.30[/TD]
[TD="align: right"]$0.50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CBP 1[/TD]
[TD]Product 1[/TD]
[TD]5/1/2013[/TD]
[TD]12/31/2013[/TD]
[TD="align: right"]$1.50[/TD]
[TD="align: right"]$0.40[/TD]
[TD="align: right"]$0.20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CBP 2[/TD]
[TD]Product 2[/TD]
[TD]1/1/2013[/TD]
[TD]12/31/2013[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]$0.50[/TD]
[TD="align: right"]$0.25[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Target 1[/TD]
[TD]Product 3[/TD]
[TD]5/1/2013[/TD]
[TD]12/31/2013[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: right"]$0.60[/TD]
[TD="align: right"]$0.10[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Target 2[/TD]
[TD]Product 4[/TD]
[TD]1/1/2013[/TD]
[TD]12/31/2013[/TD]
[TD="align: right"]$3.00[/TD]
[TD="align: right"]$0.70[/TD]
[TD="align: right"]$0.75[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Target 3[/TD]
[TD]Product 5[/TD]
[TD]5/1/2013[/TD]
[TD]12/31/2013[/TD]
[TD="align: right"]$3.50[/TD]
[TD="align: right"]$0.80[/TD]
[TD="align: right"]$1.25[/TD]
[/TR]
</tbody>[/TABLE]
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

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

Thanks!