fastest way to tranpose this data...vba or formula

[daveyc18]

historical-rate-sheet---Janaury-2021.xlsx

Table 1 FIXED RATES OF THE PAST 25 YEARS AVERAGE RESIDENTIAL MORTGAGE LENDING RATE – 5 YEAR Year,January,February,March,April,May,June,July,August,September,October,November,December,Average 1996,8.02%,7.79%,8.16%,8.48%,8.47%,8.48%,8.48%,8.07%,7.94%,7.50%,7.01%,6.94%,7.95% 1997,7.14%,7.12%,7.06%...

docs.google.com

so i manually have the dates in a vertical row (eg in column P, starting row 3, i have jan 96, cell p4 is feb 96, p5 is march 96, etc.) ...then i used this formula in Q3 (=OFFSET($B$3,0,ROW()-3,1,1)) and dragged it down so that horizontal data is now vertifcal

this is too manual since for the year 1997, i need to manually change the formula again (in Q15, I have =OFFSET($B$4,0,ROW()-15,1,1))

perhaps a macro that loops would be better? e.g., copy b3:m3 , paste to q3 (year 1996)...then copy b4:m4, paste to q15 (year 1997) ...my VBA isnt good enough to write this..help!

 

[KRice]

Does TRANSPOSE produce the desired result? Here's just a snippet with the formula:

historical-rate-sheet---Janaury-2021.xlsx
	P	Q	R	S	T	U	V	W
2	Year	1996	1997	1998	1999	2000	2001	2002
3	January	8.02%	7.14%	6.90%	6.72%	8.34%	7.58%	6.62%
4	February	7.79%	7.12%	6.84%	6.79%	8.43%	7.52%	6.59%
5	March	8.16%	7.06%	6.84%	7.03%	8.24%	7.19%	6.80%
6	April	8.48%	7.56%	6.79%	6.71%	8.23%	7.22%	7.00%
7	May	8.47%	7.46%	6.92%	6.99%	8.50%	7.38%	7.00%
8	June	8.48%	7.22%	6.90%	7.35%	8.34%	7.47%	6.98%
9	July	8.48%	6.98%	6.90%	7.42%	8.18%	7.46%	6.90%
10	August	8.07%	7.00%	7.08%	7.80%	8.08%	7.39%	6.61%
11	September	7.94%	6.96%	7.32%	7.67%	8.08%	7.03%	6.49%
12	October	7.50%	6.73%	6.73%	7.90%	8.08%	6.74%	6.50%
13	November	7.01%	6.69%	6.94%	8.13%	8.04%	6.51%	6.47%
14	December	6.94%	6.90%	6.69%	8.13%	7.81%	6.64%	6.39%
15	Average	7.95%	7.07%	6.90%	7.39%	8.20%	7.18%	6.70%
Table 1

Cell Formulas
Range	Formula
P2:AR15	P2	=TRANSPOSE(A2:N30)
Dynamic array formulas.

 

[daveyc18]

Does TRANSPOSE produce the desired result? Here's just a snippet with the formula:

historical-rate-sheet---Janaury-2021.xlsx
	P	Q	R	S	T	U	V	W
2	Year	1996	1997	1998	1999	2000	2001	2002
3	January	8.02%	7.14%	6.90%	6.72%	8.34%	7.58%	6.62%
4	February	7.79%	7.12%	6.84%	6.79%	8.43%	7.52%	6.59%
5	March	8.16%	7.06%	6.84%	7.03%	8.24%	7.19%	6.80%
6	April	8.48%	7.56%	6.79%	6.71%	8.23%	7.22%	7.00%
7	May	8.47%	7.46%	6.92%	6.99%	8.50%	7.38%	7.00%
8	June	8.48%	7.22%	6.90%	7.35%	8.34%	7.47%	6.98%
9	July	8.48%	6.98%	6.90%	7.42%	8.18%	7.46%	6.90%
10	August	8.07%	7.00%	7.08%	7.80%	8.08%	7.39%	6.61%
11	September	7.94%	6.96%	7.32%	7.67%	8.08%	7.03%	6.49%
12	October	7.50%	6.73%	6.73%	7.90%	8.08%	6.74%	6.50%
13	November	7.01%	6.69%	6.94%	8.13%	8.04%	6.51%	6.47%
14	December	6.94%	6.90%	6.69%	8.13%	7.81%	6.64%	6.39%
15	Average	7.95%	7.07%	6.90%	7.39%	8.20%	7.18%	6.70%
Table 1

Cell Formulas
Range	Formula
P2:AR15	P2	=TRANSPOSE(A2:N30)
Dynamic array formulas.

I'm trying to make the data like this so I can graph it (x axis has month and hear and y axis has rates)

pls see columns T and U in revised file attached

historical-rate-sheet---Janaury-2021 - revised.xlsx

Table 1 FIXED RATES OF THE PAST 25 YEARS AVERAGE RESIDENTIAL MORTGAGE LENDING RATE – 5 YEAR Year,January,February,March,April,May,June,July,August,September,October,November,December,Average,Jan-96 1996,8.02%,7.79%,8.16%,8.48%,8.47%,8.48%,8.48%,8.07%,7.94%,7.50%,7.01%,6.94%,7.95%,Feb-96 1997,7.1...

docs.google.com

 

[KRice]

I see...are you familiar with Power Query? Your table can be fed into PQ and transformed in a few steps to produce something like this...

historical-rate-sheet---Janaury-2021 - revised.xlsx
	A	B
1	Date	Rate
2	1/1/1996	0.0802
3	2/1/1996	0.0779
4	3/1/1996	0.0816
5	4/1/1996	0.0848
6	5/1/1996	0.0847
7	6/1/1996	0.0848
8	7/1/1996	0.0848
9	8/1/1996	0.0807
10	9/1/1996	0.0794
11	10/1/1996	0.075
12	11/1/1996	0.0701
13	12/1/1996	0.0694
14	1/1/1997	0.0714
15	2/1/1997	0.0712
16	3/1/1997	0.0706
17	4/1/1997	0.0756
18	5/1/1997	0.0746
19	6/1/1997	0.0722
20	7/1/1997	0.0698
21	8/1/1997	0.07
22	9/1/1997	0.0696
23	10/1/1997	0.0673
24	11/1/1997	0.0669
25	12/1/1997	0.069
26	1/1/1998	0.069
27	2/1/1998	0.0684
28	3/1/1998	0.0684
29	4/1/1998	0.0679
30	5/1/1998	0.0692
31	6/1/1998	0.069
32	7/1/1998	0.069
33	8/1/1998	0.0708
34	9/1/1998	0.0732
35	10/1/1998	0.0673
36	11/1/1998	0.0694
37	12/1/1998	0.0669
Table2

Here is the M-code I used:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"January", type number}, {"February", type number}, {"March", type number}, {"April", type number}, {"May", type number}, {"June", type number}, {"July", type number}, {"August", type number}, {"September", type number}, {"October", type number}, {"November", type number}, {"December", type number}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table1" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Year"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Date", each [Year]&"-"&[Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year", "Attribute"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "Rate"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Rate", Percentage.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

Table2 is what Excel automatically named your upper table...I left it. The results of this transformation were loaded from PQ onto worksheet [Table2]. I recycled the same M-code and operated on your lower table (Table4) and those results are shown on a sheet of the same name...here...

historical-rate-sheet---Janaury-2021 - revised_kr.xlsx

Shared with Dropbox

www.dropbox.com

 

[daveyc18]

I see...are you familiar with Power Query? Your table can be fed into PQ and transformed in a few steps to produce something like this...

historical-rate-sheet---Janaury-2021 - revised.xlsx
	A	B
1	Date	Rate
2	1/1/1996	0.0802
3	2/1/1996	0.0779
4	3/1/1996	0.0816
5	4/1/1996	0.0848
6	5/1/1996	0.0847
7	6/1/1996	0.0848
8	7/1/1996	0.0848
9	8/1/1996	0.0807
10	9/1/1996	0.0794
11	10/1/1996	0.075
12	11/1/1996	0.0701
13	12/1/1996	0.0694
14	1/1/1997	0.0714
15	2/1/1997	0.0712
16	3/1/1997	0.0706
17	4/1/1997	0.0756
18	5/1/1997	0.0746
19	6/1/1997	0.0722
20	7/1/1997	0.0698
21	8/1/1997	0.07
22	9/1/1997	0.0696
23	10/1/1997	0.0673
24	11/1/1997	0.0669
25	12/1/1997	0.069
26	1/1/1998	0.069
27	2/1/1998	0.0684
28	3/1/1998	0.0684
29	4/1/1998	0.0679
30	5/1/1998	0.0692
31	6/1/1998	0.069
32	7/1/1998	0.069
33	8/1/1998	0.0708
34	9/1/1998	0.0732
35	10/1/1998	0.0673
36	11/1/1998	0.0694
37	12/1/1998	0.0669
Table2

Here is the M-code I used:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"January", type number}, {"February", type number}, {"March", type number}, {"April", type number}, {"May", type number}, {"June", type number}, {"July", type number}, {"August", type number}, {"September", type number}, {"October", type number}, {"November", type number}, {"December", type number}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table1" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Year"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Date", each [Year]&"-"&[Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year", "Attribute"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Value", "Rate"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Rate", Percentage.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

Table2 is what Excel automatically named your upper table...I left it. The results of this transformation were loaded from PQ onto worksheet [Table2]. I recycled the same M-code and operated on your lower table (Table4) and those results are shown on a sheet of the same name...here...

historical-rate-sheet---Janaury-2021 - revised_kr.xlsx

Shared with Dropbox

www.dropbox.com

Tks for your effort ...I'm not familiar with power query but I'll take a look

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.