VBA to transpose blocks (arrays) of data

[Antonf]

I have to transform literally hundreds of blocks of data exported from financial software into a different format for my manager.
The source data is on the left in the image and the required output is on the right.
Doing it manually is not that difficult but i will drive me to drink!
Any help will be highly appreciated!

Transpose.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Code	Description	Mar 2023	Apr 2023	May 2023	Jun 2023							4001	9045	9150
2	NR0001	Name1									NR0001	Name1
3	4001		7.91	7.91	7.91	7.91						Mar 2023	7.91	177.12	178.73
4	9045		177.12	177.12	177.12	177.12						Apr 2023	7.91	177.12	182.54
5	9150		178.73	182.54	229.54	191.93						May 2023	7.91	177.12	229.54
6	NR0004	Name2										Jun 2023	7.91	177.12	191.93
7	4001		7.91	7.91	7.91	7.91					NR0004	Name2
8	9045		177.12	177.12	177.12	177.12						Mar 2023	7.91	177.12	551.67
9	9150		551.67	514.18	664.42	636.36						Apr 2023	7.91	177.12	514.18
10												May 2023	7.91	177.12	664.42
11												Jun 2023	7.91	177.12	636.36
Sheet1

 

[Kerryx]

Think of using Power query?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Code	Description	Mar-23	Apr-23	May-23	Jun-23							4001	9045	9150
2	NR0001	Name1									NR0001	Name1
3	4001		7.91	7.91	7.91	7.91						Mar-23	7.91	177.12	178.73
4	9045		177.12	177.12	177.12	177.12						Apr-23	7.91	177.12	182.54
5	9150		178.73	182.54	229.54	191.93						May-23	7.91	177.12	229.54
6	NR0004	Name2										Jun-23	7.91	177.12	191.93
7	4001		7.91	7.91	7.91	7.91					NR0004	Name2
8	9045		177.12	177.12	177.12	177.12						Mar-23	7.91	177.12	551.67
9	9150		551.67	514.18	664.42	636.36						Apr-23	7.91	177.12	514.18
10												May-23	7.91	177.12	664.42
11												Jun-23	7.91	177.12	636.36
12
13											NR	Description	Attribute	4001	9045	9150
14											NR0001	Name1	Apr-23	7.91	177.12	182.54
15											NR0001	Name1	Jun-23	7.91	177.12	191.93
16											NR0001	Name1	Mar-23	7.91	177.12	178.73
17											NR0001	Name1	May-23	7.91	177.12	229.54
18											NR0004	Name2	Apr-23	7.91	177.12	514.18
19											NR0004	Name2	Jun-23	7.91	177.12	636.36
20											NR0004	Name2	Mar-23	7.91	177.12	551.67
21											NR0004	Name2	May-23	7.91	177.12	664.42
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type any}, {"Description", type text}, {"Mar-23", type number}, {"Apr-23", type number}, {"May-23", type number}, {"Jun-23", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NR", each if [Code]= Text.Select([Code], {"0".."9"} ) then null else [Code]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"NR", null}}),
    #"Filled Down" = Table.FillDown(#"Replaced Errors",{"NR"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Code", "NR", "Description", "Mar-23", "Apr-23", "May-23", "Jun-23"}),
    #"Filled Down1" = Table.FillDown(#"Reordered Columns",{"Description"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filled Down1", {"Code", "NR", "Description"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Code", type text}}, "en-IE"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Code", type text}}, "en-IE")[Code]), "Code", "Value", List.Sum)
in
    #"Pivoted Column"

 

[Antonf]

Thank you. Power Query is a mystery to me, but I'm more than prepared to learn. Will revert soonest.

 

[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.

 

[Antonf]

Hi, thanks, but I have NO idea how to use this. I am OK with "normal" Excel stuff, but this is beyond me...

Please ELI5

 

[Antonf]

Thank you. Power Query is a mystery to me, but I'm more than prepared to learn. Will revert soonest.

 

[Antonf]

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.

Thanks a lot, the links really helped!!!

 

[Antonf]

Think of using Power query?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Code	Description	Mar-23	Apr-23	May-23	Jun-23							4001	9045	9150
2	NR0001	Name1									NR0001	Name1
3	4001		7.91	7.91	7.91	7.91						Mar-23	7.91	177.12	178.73
4	9045		177.12	177.12	177.12	177.12						Apr-23	7.91	177.12	182.54
5	9150		178.73	182.54	229.54	191.93						May-23	7.91	177.12	229.54
6	NR0004	Name2										Jun-23	7.91	177.12	191.93
7	4001		7.91	7.91	7.91	7.91					NR0004	Name2
8	9045		177.12	177.12	177.12	177.12						Mar-23	7.91	177.12	551.67
9	9150		551.67	514.18	664.42	636.36						Apr-23	7.91	177.12	514.18
10												May-23	7.91	177.12	664.42
11												Jun-23	7.91	177.12	636.36
12
13											NR	Description	Attribute	4001	9045	9150
14											NR0001	Name1	Apr-23	7.91	177.12	182.54
15											NR0001	Name1	Jun-23	7.91	177.12	191.93
16											NR0001	Name1	Mar-23	7.91	177.12	178.73
17											NR0001	Name1	May-23	7.91	177.12	229.54
18											NR0004	Name2	Apr-23	7.91	177.12	514.18
19											NR0004	Name2	Jun-23	7.91	177.12	636.36
20											NR0004	Name2	Mar-23	7.91	177.12	551.67
21											NR0004	Name2	May-23	7.91	177.12	664.42
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type any}, {"Description", type text}, {"Mar-23", type number}, {"Apr-23", type number}, {"May-23", type number}, {"Jun-23", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NR", each if [Code]= Text.Select([Code], {"0".."9"} ) then null else [Code]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"NR", null}}),
    #"Filled Down" = Table.FillDown(#"Replaced Errors",{"NR"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Code", "NR", "Description", "Mar-23", "Apr-23", "May-23", "Jun-23"}),
    #"Filled Down1" = Table.FillDown(#"Reordered Columns",{"Description"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filled Down1", {"Code", "NR", "Description"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Code", type text}}, "en-IE"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Code", type text}}, "en-IE")[Code]), "Code", "Value", List.Sum)
in
    #"Pivoted Column"

Thanks, it seems I can do a LOT of work a LOT more efficient using Power Query!

 

[Kerryx]

Thanks, it seems I can do a LOT of work a LOT more efficient using Power Query!

yes its great for handling large or even small data sets and once you get used to the steps process you can insert a step delete a step so a lot of flexibility

 

[Antonf]

Dear kind people
I have to go through the same exercise again, for one month only. Using the same output for some reason Power Query can'r find the column Code... I've even copied the heading from the previous sheet - no go!

Transpose2.xlsx
	A	B	C
3	Code	Description	Jul-23
4	AM002	Name1
5	8001		2018.47
6	9045		177.12
7	9150		191.93
8	AM004	Mr Robin Shawn Van Biljon
9	8001		13760.3
10	9045		177.12
11	9150		567.7
Sheet1

The code I'm using:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type any}, {"Description", type text}, {"Jul-23", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NR", each if

= Text.Select([Code], {"0".."9"} ) then null else [Code]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"NR", null}}),
    #"Filled Down" = Table.FillDown(#"Replaced Errors",{"NR"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Code", "NR", "Description", "Jul-23"}),
    #"Filled Down1" = Table.FillDown(#"Reordered Columns",{"Description"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filled Down1", {"Code", "NR", "Description"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Code", type text}}, "en-IE"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Code", type text}}, "en-IE")[Code]), "Code", "Value", List.Sum)
in
    #"Pivoted Column"