Code for converting data into a particular format

kaushik_birmiwal

New Member
Joined
Feb 6, 2017
Messages
9
Hi All,

I have been facing this recurring issue. We need to analyze the spend as per product and SKU by week, months, quarters etc. The dump from the tool is in a raw form and we end up spending lot of time in converting the data into a format which can be further used to apply pivots. I am relatively new to coding and hence would like to seek help.

I have the data in below format:

MonthYearJanuaryJanuaryJanuaryJanuaryJanuary
Date201618152229
Week No.12345
AB
Product 12016SKU 1SKU 2
Product 12016
Product 12016SKU 3
Product 12016SKU 5
Product 12016SKU 6
Product 12016
Product 12016
Product 2Planned
Product 3Rental$6,000$17,655$5,000$40,000
Product 4listing fee
Product 5production cost
CD
Product 22016SKU 7
Product 22016SKU 11
Product 22016
Product 22016SKU 8SKU 9
Product 22016
Product 22016SKU 10
Product 22016
Product 2Planned
Product 2Rental$6,000$17,655$40,000$5,000
Product 2listing fee
Product 2production cost

<tbody>
</tbody>


I now need to convert this data into the below mentioned format, so that i can pivots for analyzing the data.

YearProduct CategoryProductWeekSKU #Amount
2016ABProduct 11SKU 1$6,000
2016ABProduct 11SKU 7
2016ABProduct 13SKU 5$5,000
2016ABProduct 14SKU 6$40,000
2016ABProduct 15SKU 2-
2016ABProduct 12SKU 13$17,655
2016ABProduct 12SKU 3
2016CDProduct 21SKU 8$6,000
2016CDProduct 22SKU 12$17,655
2016CDProduct 23SKU 11-
2016CDProduct 23SKU 9
2016CDProduct 25SKU 10$5,000
2016CDProduct 24NA$40,000

<tbody>
</tbody>




Can someone please help me with this issue.

Thanks in advance.

Regards,
Kaushik
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,099,275
Messages
5,467,709
Members
406,549
Latest member
midcoastchris04

This Week's Hot Topics

Top