How to normalize a large dataset into less columns?

[DevAlex]

I have the following data:

Item_ID

2010_Sales

2010_Costs

2010_UnitsSold

2011_Sales

2011_Costs

2011_UnitsSold

2012_Sales

2012_Costs

2012_UnitsSold

2013_Sales

2013_Costs

2013_UnitsSold

2014_Sales

2014_Costs

2014_UnitsSold

2015_Sales

2015_Costs

2015_UnitsSold

41988098​

448​

689​

410​

505​

761​

821​

822​

689​

871​

597​

837​

625​

795​

159​

688​

760​

541​

440​

85479389​

930​

930​

967​

732​

951​

429​

764​

340​

304​

272​

540​

424​

285​

298​

626​

718​

158​

740​

63981760​

785​

366​

874​

569​

818​

347​

462​

775​

759​

415​

302​

294​

134​

232​

395​

194​

708​

683​

46774076​

452​

526​

387​

885​

220​

470​

248​

563​

767​

851​

120​

490​

947​

622​

874​

742​

554​

352​

68727776​

500​

821​

204​

675​

817​

296​

214​

929​

136​

545​

604​

413​

147​

283​

917​

710​

109​

718​

13199311​

805​

219​

830​

587​

452​

731​

674​

972​

375​

612​

142​

979​

442​

595​

662​

981​

218​

563​

7026817​

971​

983​

368​

636​

644​

978​

357​

486​

376​

101​

387​

276​

636​

307​

744​

389​

705​

251​

68650619​

460​

344​

428​

397​

196​

594​

407​

490​

564​

872​

260​

217​

393​

276​

530​

532​

804​

197​

91968456​

541​

389​

423​

786​

824​

153​

419​

311​

856​

445​

710​

855​

691​

928​

770​

730​

994​

875​

6295037​

269​

475​

463​

537​

154​

621​

997​

410​

730​

315​

948​

577​

503​

706​

150​

389​

220​

509​

99957946​

144​

213​

319​

928​

631​

881​

227​

757​

758​

674​

770​

779​

484​

234​

711​

766​

592​

363​

38507206​

766​

697​

888​

344​

176​

289​

342​

819​

753​

341​

119​

289​

969​

744​

795​

998​

282​

891​

20902921​

256​

981​

942​

836​

311​

981​

772​

840​

161​

591​

562​

376​

102​

896​

355​

844​

766​

386​

93167988​

109​

388​

970​

720​

259​

220​

855​

748​

300​

392​

814​

801​

670​

637​

677​

197​

368​

981​

43881861​

662​

819​

156​

627​

649​

877​

143​

986​

498​

983​

304​

365​

378​

634​

167​

622​

492​

323​

97293992​

237​

368​

416​

386​

236​

487​

328​

629​

551​

627​

119​

903​

996​

320​

222​

779​

438​

747​

88816807​

521​

219​

928​

440​

778​

219​

583​

128​

243​

761​

523​

365​

755​

195​

449​

279​

969​

605​

15380507​

958​

204​

633​

379​

522​

609​

746​

175​

816​

227​

812​

399​

695​

105​

475​

216​

288​

631​

14420025​

718​

232​

781​

142​

680​

610​

230​

549​

787​

801​

960​

167​

597​

804​

146​

586​

732​

224​

The issue is that there is no year column in this dataset, which I want to add manually so that I can I graph the data. To do so I need to normalize the data and to have only 3 columns for each value (sales, costs, unitssold).

In the end I would like something like this:

Item_ID	Year	Sales	Costs	UnitsSold
36319622​	2010​	712​	372​	586​
36319622​	2011​	593​	123​	601​
36319622​	2012​	458​	214​	896​
36319622​	2013​	191​	163​	690​
36319622​	2014​	355​	935​	662​
36319622​	2015​	820​	498​	481​
49355938​	2010​	708​	109​	447​
49355938​	2011​	597​	714​	524​
49355938​	2012​	602​	144​	485​
49355938​	2013​	405​	633​	705​
49355938​	2014​	202​	521​	333​
49355938​	2015​	220​	190​	236​

What is the best way to go about cleaning the data to more like the bottom graph?

Thank you!

 

[KRice]

Have you looked into Power Query? With a few steps, you can transform the source table into the desired output table. Here is the M Code I used:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item_ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter1",{"Item_ID", "Attribute.1"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Attribute.2]), "Attribute.2", "Value", List.Sum),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Pivoted Column", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter2",{{"Merged.1", "Item_ID"}, {"Merged.2", "Year"}})
in
    #"Renamed Columns"

Source table:

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Item_ID	2010_Sales	2010_Costs	2010_UnitsSold	2011_Sales	2011_Costs	2011_UnitsSold	2012_Sales	2012_Costs	2012_UnitsSold	2013_Sales	2013_Costs	2013_UnitsSold	2014_Sales	2014_Costs	2014_UnitsSold	2015_Sales	2015_Costs	2015_UnitsSold
2	41988098	448	689	410	505	761	821	822	689	871	597	837	625	795	159	688	760	541	440
3	85479389	930	930	967	732	951	429	764	340	304	272	540	424	285	298	626	718	158	740
4	63981760	785	366	874	569	818	347	462	775	759	415	302	294	134	232	395	194	708	683
5	46774076	452	526	387	885	220	470	248	563	767	851	120	490	947	622	874	742	554	352
6	68727776	500	821	204	675	817	296	214	929	136	545	604	413	147	283	917	710	109	718
7	13199311	805	219	830	587	452	731	674	972	375	612	142	979	442	595	662	981	218	563
8	7026817	971	983	368	636	644	978	357	486	376	101	387	276	636	307	744	389	705	251
9	68650619	460	344	428	397	196	594	407	490	564	872	260	217	393	276	530	532	804	197
10	91968456	541	389	423	786	824	153	419	311	856	445	710	855	691	928	770	730	994	875
11	6295037	269	475	463	537	154	621	997	410	730	315	948	577	503	706	150	389	220	509
12	99957946	144	213	319	928	631	881	227	757	758	674	770	779	484	234	711	766	592	363
13	38507206	766	697	888	344	176	289	342	819	753	341	119	289	969	744	795	998	282	891
14	20902921	256	981	942	836	311	981	772	840	161	591	562	376	102	896	355	844	766	386
15	93167988	109	388	970	720	259	220	855	748	300	392	814	801	670	637	677	197	368	981
16	43881861	662	819	156	627	649	877	143	986	498	983	304	365	378	634	167	622	492	323
17	97293992	237	368	416	386	236	487	328	629	551	627	119	903	996	320	222	779	438	747
18	88816807	521	219	928	440	778	219	583	128	243	761	523	365	755	195	449	279	969	605
19	15380507	958	204	633	379	522	609	746	175	816	227	812	399	695	105	475	216	288	631
20	14420025	718	232	781	142	680	610	230	549	787	801	960	167	597	804	146	586	732	224
Sheet1

Small excerpt of Output table:

Book6
	A	B	C	D	E
1	Item_ID	Year	Sales	Costs	UnitsSold
2	13199311	2010	805	219	830
3	13199311	2011	587	452	731
4	13199311	2012	674	972	375
5	13199311	2013	612	142	979
6	13199311	2014	442	595	662
7	13199311	2015	981	218	563
8	14420025	2010	718	232	781
9	14420025	2011	142	680	610
10	14420025	2012	230	549	787
11	14420025	2013	801	960	167
12	14420025	2014	597	804	146
13	14420025	2015	586	732	224
14	15380507	2010	958	204	633
15	15380507	2011	379	522	609
16	15380507	2012	746	175	816
17	15380507	2013	227	812	399
18	15380507	2014	695	105	475
19	15380507	2015	216	288	631
20	20902921	2010	256	981	942
21	20902921	2011	836	311	981
22	20902921	2012	772	840	161
Table1

 

[HighAndWilder]

I have the following data:

Item_ID

2010_Sales

2010_Costs

2010_UnitsSold

2011_Sales

2011_Costs

2011_UnitsSold

2012_Sales

2012_Costs

2012_UnitsSold

2013_Sales

2013_Costs

2013_UnitsSold

2014_Sales

2014_Costs

2014_UnitsSold

2015_Sales

2015_Costs

2015_UnitsSold

41988098​

448​

689​

410​

505​

761​

821​

822​

689​

871​

597​

837​

625​

795​

159​

688​

760​

541​

440​

85479389​

930​

930​

967​

732​

951​

429​

764​

340​

304​

272​

540​

424​

285​

298​

626​

718​

158​

740​

63981760​

785​

366​

874​

569​

818​

347​

462​

775​

759​

415​

302​

294​

134​

232​

395​

194​

708​

683​

46774076​

452​

526​

387​

885​

220​

470​

248​

563​

767​

851​

120​

490​

947​

622​

874​

742​

554​

352​

68727776​

500​

821​

204​

675​

817​

296​

214​

929​

136​

545​

604​

413​

147​

283​

917​

710​

109​

718​

13199311​

805​

219​

830​

587​

452​

731​

674​

972​

375​

612​

142​

979​

442​

595​

662​

981​

218​

563​

7026817​

971​

983​

368​

636​

644​

978​

357​

486​

376​

101​

387​

276​

636​

307​

744​

389​

705​

251​

68650619​

460​

344​

428​

397​

196​

594​

407​

490​

564​

872​

260​

217​

393​

276​

530​

532​

804​

197​

91968456​

541​

389​

423​

786​

824​

153​

419​

311​

856​

445​

710​

855​

691​

928​

770​

730​

994​

875​

6295037​

269​

475​

463​

537​

154​

621​

997​

410​

730​

315​

948​

577​

503​

706​

150​

389​

220​

509​

99957946​

144​

213​

319​

928​

631​

881​

227​

757​

758​

674​

770​

779​

484​

234​

711​

766​

592​

363​

38507206​

766​

697​

888​

344​

176​

289​

342​

819​

753​

341​

119​

289​

969​

744​

795​

998​

282​

891​

20902921​

256​

981​

942​

836​

311​

981​

772​

840​

161​

591​

562​

376​

102​

896​

355​

844​

766​

386​

93167988​

109​

388​

970​

720​

259​

220​

855​

748​

300​

392​

814​

801​

670​

637​

677​

197​

368​

981​

43881861​

662​

819​

156​

627​

649​

877​

143​

986​

498​

983​

304​

365​

378​

634​

167​

622​

492​

323​

97293992​

237​

368​

416​

386​

236​

487​

328​

629​

551​

627​

119​

903​

996​

320​

222​

779​

438​

747​

88816807​

521​

219​

928​

440​

778​

219​

583​

128​

243​

761​

523​

365​

755​

195​

449​

279​

969​

605​

15380507​

958​

204​

633​

379​

522​

609​

746​

175​

816​

227​

812​

399​

695​

105​

475​

216​

288​

631​

14420025​

718​

232​

781​

142​

680​

610​

230​

549​

787​

801​

960​

167​

597​

804​

146​

586​

732​

224​

The issue is that there is no year column in this dataset, which I want to add manually so that I can I graph the data. To do so I need to normalize the data and to have only 3 columns for each value (sales, costs, unitssold).

In the end I would like something like this:

Item_ID	Year	Sales	Costs	UnitsSold
36319622​	2010​	712​	372​	586​
36319622​	2011​	593​	123​	601​
36319622​	2012​	458​	214​	896​
36319622​	2013​	191​	163​	690​
36319622​	2014​	355​	935​	662​
36319622​	2015​	820​	498​	481​
49355938​	2010​	708​	109​	447​
49355938​	2011​	597​	714​	524​
49355938​	2012​	602​	144​	485​
49355938​	2013​	405​	633​	705​
49355938​	2014​	202​	521​	333​
49355938​	2015​	220​	190​	236​

What is the best way to go about cleaning the data to more like the bottom graph?

Thank you!

A solution using VBA.

Remember to change the worksheet names where indicated.

Public Sub subNormaliseData()
Dim intLastColumn As Integer
Dim intLastRow As Integer
Dim intNextRow As Integer
Dim i As Integer
Dim WsExisting As Worksheet
Dim WsDestination As Worksheet
Dim rngData As Range
Dim intRows As Integer

  ActiveWorkbook.Save

  ' INDICATE YOUR EXISTING DATA WORKSHEET HERE.
  Set WsExisting = Worksheets("Existing")
  
  ' INDICATE YOUR DESTINATION WORKSHEET HERE.
  Set WsDestination = Worksheets("Destination")
    
  With WsExisting
    intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    intLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    intRows = .Cells(Rows.Count, 1).End(xlUp).Row - 1
    Set rngData = .Range("A2:A" & intLastRow).Resize(intLastRow - 1, intLastColumn)
  End With
    
  With WsDestination
  
    .Cells.Clear
  
    .Range("A1:E1").Value = Array("Year", "Item_ID", "Sales", "Costs", "UnitsSold")

    For i = 2 To intLastColumn Step 3
      
      intNextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
      With .Range(.Cells(intNextRow, 1), .Cells(intNextRow + intRows - 1, 1))
        .Value = rngData.Columns(1).Value
        .Offset(0, 1).Value = Val(Left(WsExisting.Cells(1, i), 4))
        .Offset(0, 2).Resize(intRows, 3).Value = rngData.Columns(i).Resize(intRows, 3).Value
      End With
      
    Next i
  
  End With
  
  ActiveWorkbook.Save
  
End Sub

 

[DevAlex]

A solution using VBA.

Remember to change the worksheet names where indicated.

Public Sub subNormaliseData()
Dim intLastColumn As Integer
Dim intLastRow As Integer
Dim intNextRow As Integer
Dim i As Integer
Dim WsExisting As Worksheet
Dim WsDestination As Worksheet
Dim rngData As Range
Dim intRows As Integer

  ActiveWorkbook.Save

  ' INDICATE YOUR EXISTING DATA WORKSHEET HERE.
  Set WsExisting = Worksheets("Existing")
 
  ' INDICATE YOUR DESTINATION WORKSHEET HERE.
  Set WsDestination = Worksheets("Destination")
   
  With WsExisting
    intLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    intLastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    intRows = .Cells(Rows.Count, 1).End(xlUp).Row - 1
    Set rngData = .Range("A2:A" & intLastRow).Resize(intLastRow - 1, intLastColumn)
  End With
   
  With WsDestination
 
    .Cells.Clear
 
    .Range("A1:E1").Value = Array("Year", "Item_ID", "Sales", "Costs", "UnitsSold")

    For i = 2 To intLastColumn Step 3
     
      intNextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
      With .Range(.Cells(intNextRow, 1), .Cells(intNextRow + intRows - 1, 1))
        .Value = rngData.Columns(1).Value
        .Offset(0, 1).Value = Val(Left(WsExisting.Cells(1, i), 4))
        .Offset(0, 2).Resize(intRows, 3).Value = rngData.Columns(i).Resize(intRows, 3).Value
      End With
     
    Next i
 
  End With
 
  ActiveWorkbook.Save
 
End Sub

Thank you this is helpful. However, if I wanted to only track 2 columns instead of 3? For example, I want to only track Sales and Costs and do not have units sold. Would I just remove the "UnitsSold" from the array function?

 

[HighAndWilder]

Thank you this is helpful. However, if I wanted to only track 2 columns instead of 3? For example, I want to only track Sales and Costs and do not have units sold. Would I just remove the "UnitsSold" from the array function?

Just substitute this line:
.Range("A1:E1").Value = Array("Year", "Item_ID", "Sales", "Costs", "UnitsSold")
for this line:
.Range("A1:D1").Value = Array("Year", "Item_ID", "Sales", "Costs")

Also substitute this line:
.Offset(0, 2).Resize(intRows, 3).Value = rngData.Columns(i).Resize(intRows, 3).Value
for this line:
.Offset(0, 2).Resize(intRows, 2).Value = rngData.Columns(i).Resize(intRows, 2).Value