Macro Needed for Pivot Data Info.

[garypea123]

Good Afternoon All,

Apologies as I cannot to Xl2BB from computer. I am not sure how to word this but I am trying to create a data table so I can do some Pivot Table (Possible Power Pivots)

The Raw Data format is not great to pivot as I need it more in columns

An example below is the raw data (simplified) and is on say "Sheet 1"

What I believe I need to do is to create a macro which will do the following in "Sheet 2" (Lets say "B3")

- Transpose data from D:P (This will always be 13 entries as I look at a 13 week period)
- I then need Column B and C to copy another 12 times to match with the data.
- The would be more than 3 rows of data in general. Columns will always be the same format but number of rows will change each week potentially)

An example of how it should look:

 

[Kerryx]

Easy just use power query to transpose the info for you

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"})
in
    #"Removed Columns"

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2
3	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15
4	Gary	Jones	1	2	3	4	5	6	7	8	9	10	11	12	13
5	Tom	Smith	13	12	11	10	9	8	7	6	5	4	3	2	1
6	Phil	Pott	6	6	6	6	6	6	6	6	6	6	6	6	6
7
8	Column1	Column2	Value
9	Gary	Jones	1
10	Gary	Jones	2
11	Gary	Jones	3
12	Gary	Jones	4
13	Gary	Jones	5
14	Gary	Jones	6
15	Gary	Jones	7
16	Gary	Jones	8
17	Gary	Jones	9
18	Gary	Jones	10
19	Gary	Jones	11
20	Gary	Jones	12
Sheet1

 

[garypea123]

Fabulous - I will take a look at this tomorrow.

I assumed this must be quite common and a simple way...Will let you know the outcode.

 

[Kerryx]

If you unsure about PQ there is some info abour Pq with links supplied by alansidman in this thread​

VBA to transpose blocks (arrays) of data

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

www.mrexcel.com

 

[Fluff]

Another option with a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15
3	Gary	Jones	1	2	3	4	5	6	7	8	9	10	11	12	13
4	Tom	Smith	13	12	11	10	9	8	7	6	5	4	3	2	1
5	Phil	Pott	6	6	6	6	6	6	6	6	6	6	6	6	6
6
7	Column1	Column2	Value
8	Gary	Jones	1
9	Gary	Jones	2
10	Gary	Jones	3
11	Gary	Jones	4
12	Gary	Jones	5
13	Gary	Jones	6
14	Gary	Jones	7
15	Gary	Jones	8
16	Gary	Jones	9
17	Gary	Jones	10
18	Gary	Jones	11
19	Gary	Jones	12
20	Gary	Jones	13
21	Tom	Smith	13
22	Tom	Smith	12
23	Tom	Smith	11
24	Tom	Smith	10
25	Tom	Smith	9
26	Tom	Smith	8
27	Tom	Smith	7
28	Tom	Smith	6
29	Tom	Smith	5
30	Tom	Smith	4
31	Tom	Smith	3
32	Tom	Smith	2
33	Tom	Smith	1
34	Phil	Pott	6
35	Phil	Pott	6
36	Phil	Pott	6
37	Phil	Pott	6
38	Phil	Pott	6
39	Phil	Pott	6
40	Phil	Pott	6
41	Phil	Pott	6
42	Phil	Pott	6
43	Phil	Pott	6
44	Phil	Pott	6
45	Phil	Pott	6
46	Phil	Pott	6
Master

Cell Formulas
Range	Formula
A8:C46	A8	=LET(s,SEQUENCE(,13),HSTACK(TOCOL(IF(s,A3:A5)),TOCOL(IF(s,B3:B5)),TOCOL(C3:O5)))
Dynamic array formulas.

 

[garypea123]

Thank you guys - I need to do some training on power pivotting so I have some knowledge regarding this. (Thank you for the links)

Fluff, as usual your formula is exactly what I need for now (however, I do need to make an adventure into power queries etc)

 

[Fluff]

Glad we could help & thanks for the feedback.