Macro to rearrange data order from one sheet to another

[crispangilinan]

Hi Excel Gurus. I need your expert guidance on how to write a simple macro that will help to reorganize my raw data from Sheet 1 into Sheet 2. Basically, each data entry on Sheet 1 will be broken down into 8 data points once transferred to Sheet 2.

From this:
Sheet 1 Data

					Full Time	Full Time	Full Time	Full Time	Part Time	Part Time	Part Time	Part Time
Location	Emp ID	Manager	Email	Name	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8
Location 1	100001​	Manager 1	email 1	Employee 1	0	0	0	1	0	0	0	1
Location 2	100002​	Manager 2	email 2	Employee 2	0	0	1	0	0	0	1	0
Location 3	100003​	Manager 3	email 3	Employee 3	0	1	0	0	0	1	0	0
Location 4	100004​	Manager 4	email 4	Employee 4	1	0	1	1	1	0	1	1
Location 5	100005​	Manager 5	email 5	Employee 5	0	0	0	0	0	0	0	0
Location 1	100006​	Manager 6	email 6	Employee 6	0	0	0	0	0	0	0	0
Location 2	100007​	Manager 7	email 7	Employee 7	0	1	0	1	0	1	0	1
Location 3	100008​	Manager 8	email 8	Employee 8	1	0	0	0	1	0	0	0
Location 4	100009​	Manager 9	email 9	Employee 9	0	0	0	0	0	0	0	0
Location 5	100010​	Manager 10	email 10	Employee 10	0	1	1	0	0	1	1	0
Location 1	100011​	Manager 1	email 11	Employee 11	0	0	0	0	0	0	0	0
Location 2	100012​	Manager 2	email 12	Employee 12	0	0	0	0	0	0	0	0
Location 3	100013​	Manager 3	email 13	Employee 13	0	0	0	1	0	0	0	1
Location 4	100014​	Manager 4	email 14	Employee 14	0	0	1	0	0	0	1	0
Location 5	100015​	Manager 5	email 15	Employee 15	0	1	0	0	0	1	0	0
Location 1	100016​	Manager 6	email 16	Employee 16	1	0	0	0	1	0	0	0
Location 2	100017​	Manager 7	email 17	Employee 17	0	0	1	0	0	0	1	0
Location 3	100018​	Manager 8	email 18	Employee 18	0	0	0	1	0	0	0	1
Location 4	100019​	Manager 9	email 19	Employee 19	0	0	0	0	0	0	0	0
Location 5	100020​	Manager 10	email 20	Employee 20	0	1	1	0	0	1	1	0
Location 1	100021​	Manager 1	email 21	Employee 21	0	0	0	0	0	0	0	0
Location 2	100022​	Manager 2	email 22	Employee 22	1	0	1	0	1	0	1	0
Location 3	100023​	Manager 3	email 23	Employee 23	0	0	0	1	0	0	0	1
Location 4	100024​	Manager 4	email 24	Employee 24	0	0	0	0	0	0	0	0
Location 5	100025​	Manager 5	email 25	Employee 25	0	1	0	0	0	1	0	0
Location 1	100026​	Manager 6	email 26	Employee 26	0	0	0	0	0	0	0	0
Location 2	100027​	Manager 7	email 27	Employee 27	1	0	0	0	1	0	0	0
Location 3	100028​	Manager 8	email 28	Employee 28	0	0	0	0	0	0	0	0
Location 4	100029​	Manager 9	email 29	Employee 29	0	1	1	1	0	1	1	1
Location 5	100030​	Manager 10	email 30	Employee 30	0	0	0	0	0	0	0	0

To This:
Sheet 2 Data

Emp ID	Name	Email	Manager	Location	Status	Question	Value
100001​	Employee 1	email 1	Manager 1	Location 1	Full Time	Q1	0
100001​	Employee 1	email 1	Manager 1	Location 1	Full Time	Q2	0
100001​	Employee 1	email 1	Manager 1	Location 1	Full Time	Q3	0
100001​	Employee 1	email 1	Manager 1	Location 1	Full Time	Q4	1
100001​	Employee 1	email 1	Manager 1	Location 1	Part Time	Q5	0
100001​	Employee 1	email 1	Manager 1	Location 1	Part Time	Q6	0
100001​	Employee 1	email 1	Manager 1	Location 1	Part Time	Q7	0
100001​	Employee 1	email 1	Manager 1	Location 1	Part Time	Q8	1
100002​	Employee 2	email 2	Manager 2	Location 2	Full Time	Q1	0
100002​	Employee 2	email 2	Manager 2	Location 2	Full Time	Q2	0
100002​	Employee 2	email 2	Manager 2	Location 2	Full Time	Q3	1
100002​	Employee 2	email 2	Manager 2	Location 2	Full Time	Q4	0
100002​	Employee 2	email 2	Manager 2	Location 2	Part Time	Q5	0
100002​	Employee 2	email 2	Manager 2	Location 2	Part Time	Q6	0
100002​	Employee 2	email 2	Manager 2	Location 2	Part Time	Q7	1
100002​	Employee 2	email 2	Manager 2	Location 2	Part Time	Q8	0
100003​	Employee 3	email 3	Manager 3	Location 3	Full Time	Q1	0
100003​	Employee 3	email 3	Manager 3	Location 3	Full Time	Q2	1
100003​	Employee 3	email 3	Manager 3	Location 3	Full Time	Q3	0
100003​	Employee 3	email 3	Manager 3	Location 3	Full Time	Q4	0
100003​	Employee 3	email 3	Manager 3	Location 3	Part Time	Q5	0
100003​	Employee 3	email 3	Manager 3	Location 3	Part Time	Q6	1
100003​	Employee 3	email 3	Manager 3	Location 3	Part Time	Q7	0
100003​	Employee 3	email 3	Manager 3	Location 3	Part Time	Q8	0

As you can observe, a single line data on Sheet 1 will be broken into 8 data points once transferred to sheet 2. Also, the order of the rows are changed/reorganized. Would appreciate if the macro can be adjusted to cover more data points on Sheet 1 (probable 5000?). Thanks in advance!!

 

[SQUIDD]

HI

So give this a go, on a dummy set of data.

This is assuming that sheet 2 is blank, but has headers.

And the data in sheet 1 we are starting with in in cell B3

and the data is going into sheet 2, starting at A2

Sub transpose_bespoke()
LR = Range("'SHEET1'!A" & Rows.Count).End(xlUp).Row
For D = 3 To LR
LR1 = Range("'SHEET2'!A" & Rows.Count).End(xlUp).Row + 1
C = 6
For B = LR1 To LR1 + 7
    Sheets("SHEET2").Cells(B, 1) = Sheets("SHEET1").Cells(D, 2)
    Sheets("SHEET2").Cells(B, 2) = Sheets("SHEET1").Cells(D, 5)
    Sheets("SHEET2").Cells(B, 3) = Sheets("SHEET1").Cells(D, 4)
    Sheets("SHEET2").Cells(B, 4) = Sheets("SHEET1").Cells(D, 3)
    Sheets("SHEET2").Cells(B, 5) = Sheets("SHEET1").Cells(D, 1)
    Sheets("SHEET2").Cells(B, 6) = Sheets("SHEET1").Cells(1, C)
    Sheets("SHEET2").Cells(B, 7) = Sheets("SHEET1").Cells(2, C)
    Sheets("SHEET2").Cells(B, 8) = Sheets("SHEET1").Cells(D, C)
    C = C + 1
Next B
Next D
End Sub

Good luck

dave

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub crispangilinan()
   Dim Ary As Variant, Nary As Variant, Cols As Variant
   Dim r As Long, c As Long, nr As Long, nc As Long
   
   Cols = Array("", 2, 5, 4, 3, 1)
   Ary = Sheets("Sheet1").Range("A2").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 8)
   For r = 3 To UBound(Ary)
      For c = 6 To UBound(Ary, 2)
         nr = nr + 1
         For nc = 1 To 5
            Nary(nr, nc) = Ary(r, Cols(nc))
         Next nc
         Nary(nr, 6) = Ary(1, c)
         Nary(nr, 7) = Ary(2, c)
         Nary(nr, 8) = Ary(r, c)
      Next c
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, 8).Value = Nary
End Sub

 

[JamesCanale]

Office 365 but no macros:

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1						Full Time	Full Time	Full Time	Full Time	Part Time	Part Time	Part Time	Part Time
2	Location	Emp ID	Manager	Email	Name	Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8		Name	Email	Manager	ID	Location	Status	Question	Value
3	Location 1	100001	Manager 1	email 1	Employee 1	0	0	0	1	0	0	0	1		Employee 1	email 1	Manager 1	100001	Location 1	Full Time	Q1	0
4	Location 2	100002	Manager 2	email 2	Employee 2	0	0	1	0	0	0	1	0		Employee 1	email 1	Manager 1	100001	Location 1	Full Time	Q2	0
5	Location 3	100003	Manager 3	email 3	Employee 3	0	1	0	0	0	1	0	0		Employee 1	email 1	Manager 1	100001	Location 1	Full Time	Q3	0
6	Location 4	100004	Manager 4	email 4	Employee 4	1	0	1	1	1	0	1	1		Employee 1	email 1	Manager 1	100001	Location 1	Full Time	Q4	1
7	Location 5	100005	Manager 5	email 5	Employee 5	0	0	0	0	0	0	0	0		Employee 1	email 1	Manager 1	100001	Location 1	Part Time	Q5	0
8	Location 1	100006	Manager 6	email 6	Employee 6	0	0	0	0	0	0	0	0		Employee 1	email 1	Manager 1	100001	Location 1	Part Time	Q6	0
9	Location 2	100007	Manager 7	email 7	Employee 7	0	1	0	1	0	1	0	1		Employee 1	email 1	Manager 1	100001	Location 1	Part Time	Q7	0
10	Location 3	100008	Manager 8	email 8	Employee 8	1	0	0	0	1	0	0	0		Employee 1	email 1	Manager 1	100001	Location 1	Part Time	Q8	1
11	Location 4	100009	Manager 9	email 9	Employee 9	0	0	0	0	0	0	0	0		Employee 2	email 2	Manager 2	100002	Location 2	Full Time	Q1	0
12	Location 5	100010	Manager 10	email 10	Employee 10	0	1	1	0	0	1	1	0		Employee 2	email 2	Manager 2	100002	Location 2	Full Time	Q2	0
13	Location 1	100011	Manager 1	email 11	Employee 11	0	0	0	0	0	0	0	0		Employee 2	email 2	Manager 2	100002	Location 2	Full Time	Q3	1
14	Location 2	100012	Manager 2	email 12	Employee 12	0	0	0	0	0	0	0	0		Employee 2	email 2	Manager 2	100002	Location 2	Full Time	Q4	0
15	Location 3	100013	Manager 3	email 13	Employee 13	0	0	0	1	0	0	0	1		Employee 2	email 2	Manager 2	100002	Location 2	Part Time	Q5	0
16	Location 4	100014	Manager 4	email 14	Employee 14	0	0	1	0	0	0	1	0		Employee 2	email 2	Manager 2	100002	Location 2	Part Time	Q6	0
17	Location 5	100015	Manager 5	email 15	Employee 15	0	1	0	0	0	1	0	0		Employee 2	email 2	Manager 2	100002	Location 2	Part Time	Q7	1
18	Location 1	100016	Manager 6	email 16	Employee 16	1	0	0	0	1	0	0	0		Employee 2	email 2	Manager 2	100002	Location 2	Part Time	Q8	0
19	Location 2	100017	Manager 7	email 17	Employee 17	0	0	1	0	0	0	1	0		Employee 3	email 3	Manager 3	100003	Location 3	Full Time	Q1	0
20	Location 3	100018	Manager 8	email 18	Employee 18	0	0	0	1	0	0	0	1		Employee 3	email 3	Manager 3	100003	Location 3	Full Time	Q2	1
21	Location 4	100019	Manager 9	email 19	Employee 19	0	0	0	0	0	0	0	0		Employee 3	email 3	Manager 3	100003	Location 3	Full Time	Q3	0
22	Location 5	100020	Manager 10	email 20	Employee 20	0	1	1	0	0	1	1	0		Employee 3	email 3	Manager 3	100003	Location 3	Full Time	Q4	0
23	Location 1	100021	Manager 1	email 21	Employee 21	0	0	0	0	0	0	0	0		Employee 3	email 3	Manager 3	100003	Location 3	Part Time	Q5	0
24	Location 2	100022	Manager 2	email 22	Employee 22	1	0	1	0	1	0	1	0		Employee 3	email 3	Manager 3	100003	Location 3	Part Time	Q6	1
25	Location 3	100023	Manager 3	email 23	Employee 23	0	0	0	1	0	0	0	1		Employee 3	email 3	Manager 3	100003	Location 3	Part Time	Q7	0
26	Location 4	100024	Manager 4	email 24	Employee 24	0	0	0	0	0	0	0	0		Employee 3	email 3	Manager 3	100003	Location 3	Part Time	Q8	0
27	Location 5	100025	Manager 5	email 25	Employee 25	0	1	0	0	0	1	0	0		Employee 4	email 4	Manager 4	100004	Location 4	Full Time	Q1	1
28	Location 1	100026	Manager 6	email 26	Employee 26	0	0	0	0	0	0	0	0		Employee 4	email 4	Manager 4	100004	Location 4	Full Time	Q2	0
29	Location 2	100027	Manager 7	email 27	Employee 27	1	0	0	0	1	0	0	0		Employee 4	email 4	Manager 4	100004	Location 4	Full Time	Q3	1
30	Location 3	100028	Manager 8	email 28	Employee 28	0	0	0	0	0	0	0	0		Employee 4	email 4	Manager 4	100004	Location 4	Full Time	Q4	1
31	Location 4	100029	Manager 9	email 29	Employee 29	0	1	1	1	0	1	1	1		Employee 4	email 4	Manager 4	100004	Location 4	Part Time	Q5	1
32	Location 5	100030	Manager 10	email 30	Employee 30	0	0	0	0	0	0	0	0		Employee 4	email 4	Manager 4	100004	Location 4	Part Time	Q6	0
33															Employee 4	email 4	Manager 4	100004	Location 4	Part Time	Q7	1
34															Employee 4	email 4	Manager 4	100004	Location 4	Part Time	Q8	1
35															Employee 5	email 5	Manager 5	100005	Location 5	Full Time	Q1	0
36															Employee 5	email 5	Manager 5	100005	Location 5	Full Time	Q2	0
37															Employee 5	email 5	Manager 5	100005	Location 5	Full Time	Q3	0
38															Employee 5	email 5	Manager 5	100005	Location 5	Full Time	Q4	0
Sheet30

Cell Formulas
Range	Formula
O3:O242	O3	=INDEX(E3:E32,INT((SEQUENCE(8*COUNTA(A3:A267))+7)/8),1)
P3:P242	P3	=XLOOKUP(O3#,E3:E32,D3:D32,"",0)
Q3:Q242	Q3	=XLOOKUP(O3#,E3:E32,C3:C32,"",0)
R3:R242	R3	=XLOOKUP(O3#,E3:E32,B3:B32,"",0)
S3:S242	S3	=XLOOKUP(O3#,E3:E32,A3:A32,"",0)
T3:T242	T3	=INDEX(F1:M1,1,MOD(SEQUENCE(8*COUNTA(A3:A267))-1,8)+1)
U3:U242	U3	=INDEX(F2:M2,1,MOD(SEQUENCE(8*COUNTA(A3:A267))-1,8)+1)
V3:V242	V3	=INDEX(F3:M32,INT((SEQUENCE(8*COUNTA(A3:A267))+7)/8),MOD(SEQUENCE(8*COUNTA(A3:A267))-1,8)+1)
Dynamic array formulas.

 

[Fluff]

Another formula option, if you have the LET function

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Emp ID	Name	Email	Manager	Location	Status	Question	Value
2	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q1	0
3	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q2	0
4	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q3	0
5	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q4	1
6	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q5	0
7	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q6	0
8	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q7	0
9	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q8	1
10	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q1	0
11	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q2	0
12	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q3	1
13	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q4	0
14	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q5	0
15	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q6	0
16	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q7	1
17	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q8	0
18	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q1	0
19	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q2	1
20	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q3	0
21	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q4	0
22	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q5	0
23	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q6	1
24	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q7	0
25	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q8	0
26	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q1	1
27	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q2	0
28	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q3	1
29	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q4	1
30	100004	Employee 4	email 4	Manager 4	Location 4	Part Time	Q5	1
Sheet2

Cell Formulas
Range	Formula
A2:E241	A2	=LET(Rng,Sheet1!A3:E32,Cols,8,Rws,ROWS(Rng),Qty,SEQUENCE(Rws*Cols,,0),SORTBY(INDEX(Rng,INT(Qty/Cols)+1,SEQUENCE(,5)),MATCH(Sheet1!A2:E2,A1:E1,0)))
F2:G241	F2	=LET(Rng,Sheet1!F1:M2,Cols,COLUMNS(Rng),INDEX(Rng,SEQUENCE(,2),MOD(SEQUENCE(Cols*ROWS(Sheet1!A3:A32),,0),Cols)+1))
H2:H241	H2	=LET(rng,Sheet1!F3:M32,Cols,COLUMNS(rng),Qty,SEQUENCE(Cols*ROWS(rng),,0),INDEX(rng,INT(Qty/Cols)+1,MOD(Qty,Cols)+1))
Dynamic array formulas.

 

[alansidman]

An alternative to VBA is Power Query. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1:Location", "Column2:Emp ID", "Column3:Manager", "Column4:Email", "Full Time:Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each Text.StartsWith([Attribute.1], "Full") or Text.StartsWith([Attribute.1], "Part")),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Filtered Rows", "Attribute.1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1.1", "Attribute.1.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.1.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2:Emp ID", "ID"}, {"Column1:Location", "Location"}, {"Column3:Manager", "Manager"}, {"Column4:Email", "Email"}, {"Full Time:Name", "Name"}, {"Attribute.1.1", "Status"}, {"Attribute.2", "Question"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ID", "Name", "Email", "Location", "Manager", "Status", "Question", "Value"})
in
    #"Reordered Columns"

Book6
	A	B	C	D	E	F	G	H
1	ID	Name	Email	Location	Manager	Status	Question	Value
2	100001	Employee 1	email 1	Location 1	Manager 1	Full Time	Q1	0
3	100001	Employee 1	email 1	Location 1	Manager 1	Full Time	Q2	0
4	100001	Employee 1	email 1	Location 1	Manager 1	Full Time	Q3	0
5	100001	Employee 1	email 1	Location 1	Manager 1	Part Time	Q4	1
6	100001	Employee 1	email 1	Location 1	Manager 1	Part Time	Q5	0
7	100001	Employee 1	email 1	Location 1	Manager 1	Part Time	Q6	0
8	100001	Employee 1	email 1	Location 1	Manager 1	Part Time	Q7	0
9	100002	Employee 2	email 2	Location 2	Manager 2	Full Time	Q1	0
10	100002	Employee 2	email 2	Location 2	Manager 2	Full Time	Q2	0
11	100002	Employee 2	email 2	Location 2	Manager 2	Full Time	Q3	1
12	100002	Employee 2	email 2	Location 2	Manager 2	Part Time	Q4	0
13	100002	Employee 2	email 2	Location 2	Manager 2	Part Time	Q5	0
14	100002	Employee 2	email 2	Location 2	Manager 2	Part Time	Q6	0
15	100002	Employee 2	email 2	Location 2	Manager 2	Part Time	Q7	1
16	100003	Employee 3	email 3	Location 3	Manager 3	Full Time	Q1	0
17	100003	Employee 3	email 3	Location 3	Manager 3	Full Time	Q2	1
18	100003	Employee 3	email 3	Location 3	Manager 3	Full Time	Q3	0
19	100003	Employee 3	email 3	Location 3	Manager 3	Part Time	Q4	0
20	100003	Employee 3	email 3	Location 3	Manager 3	Part Time	Q5	0
21	100003	Employee 3	email 3	Location 3	Manager 3	Part Time	Q6	1
22	100003	Employee 3	email 3	Location 3	Manager 3	Part Time	Q7	0
Table1

Only showing first three employees. File runs 200 lines plus

 

[Fluff]

Or, just for the fun of it, one (rather long) formula

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Emp ID	Name	Email	Manager	Location	Status	Question	Value
2	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q1	0
3	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q2	0
4	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q3	0
5	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q4	1
6	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q5	0
7	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q6	0
8	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q7	0
9	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q8	1
10	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q1	0
11	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q2	0
12	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q3	1
13	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q4	0
14	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q5	0
15	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q6	0
16	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q7	1
17	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q8	0
18	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q1	0
19	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q2	1
20	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q3	0
21	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q4	0
22	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q5	0
23	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q6	1
24	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q7	0
25	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q8	0
26	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q1	1
27	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q2	0
28	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q3	1
29	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q4	1
30	100004	Employee 4	email 4	Manager 4	Location 4	Part Time	Q5	1
Sheet2

Cell Formulas
Range	Formula
A2:H241	A2	=LET(RngA,Sheet1!A3:E32,RngB,Sheet1!F1:M2,RngC,Sheet1!F3:M32,Cols,COLUMNS(RngB),Rws,ROWS(RngA),Qty,SEQUENCE(Rws*Cols,,0),aryA,SORTBY(INDEX(RngA,INT(Qty/Cols)+1,SEQUENCE(,5)),MATCH(Sheet1!A2:E2,A1:E1,0)),aryB,INDEX(RngB,SEQUENCE(,2),MOD(SEQUENCE(Cols*ROWS(Sheet1!A3:A32),,0),Cols)+1),aryC,INDEX(RngC,INT(Qty/Cols)+1,MOD(Qty,Cols)+1),CHOOSE(SEQUENCE(,8),aryA,aryA,aryA,aryA,aryA,INDEX(aryB,,1),INDEX(aryB,,2),aryC))
Dynamic array formulas.

 

[crispangilinan]

Or, just for the fun of it, one (rather long) formula

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Emp ID	Name	Email	Manager	Location	Status	Question	Value
2	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q1	0
3	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q2	0
4	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q3	0
5	100001	Employee 1	email 1	Manager 1	Location 1	Full Time	Q4	1
6	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q5	0
7	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q6	0
8	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q7	0
9	100001	Employee 1	email 1	Manager 1	Location 1	Part Time	Q8	1
10	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q1	0
11	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q2	0
12	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q3	1
13	100002	Employee 2	email 2	Manager 2	Location 2	Full Time	Q4	0
14	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q5	0
15	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q6	0
16	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q7	1
17	100002	Employee 2	email 2	Manager 2	Location 2	Part Time	Q8	0
18	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q1	0
19	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q2	1
20	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q3	0
21	100003	Employee 3	email 3	Manager 3	Location 3	Full Time	Q4	0
22	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q5	0
23	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q6	1
24	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q7	0
25	100003	Employee 3	email 3	Manager 3	Location 3	Part Time	Q8	0
26	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q1	1
27	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q2	0
28	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q3	1
29	100004	Employee 4	email 4	Manager 4	Location 4	Full Time	Q4	1
30	100004	Employee 4	email 4	Manager 4	Location 4	Part Time	Q5	1
Sheet2

Cell Formulas
Range	Formula
A2:H241	A2	=LET(RngA,Sheet1!A3:E32,RngB,Sheet1!F1:M2,RngC,Sheet1!F3:M32,Cols,COLUMNS(RngB),Rws,ROWS(RngA),Qty,SEQUENCE(Rws*Cols,,0),aryA,SORTBY(INDEX(RngA,INT(Qty/Cols)+1,SEQUENCE(,5)),MATCH(Sheet1!A2:E2,A1:E1,0)),aryB,INDEX(RngB,SEQUENCE(,2),MOD(SEQUENCE(Cols*ROWS(Sheet1!A3:A32),,0),Cols)+1),aryC,INDEX(RngC,INT(Qty/Cols)+1,MOD(Qty,Cols)+1),CHOOSE(SEQUENCE(,8),aryA,aryA,aryA,aryA,aryA,INDEX(aryB,,1),INDEX(aryB,,2),aryC))
Dynamic array formulas.

Thanks so much!

 

[crispangilinan]

An alternative to VBA is Power Query. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Column1:Location", "Column2:Emp ID", "Column3:Manager", "Column4:Email", "Full Time:Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter", each Text.StartsWith([Attribute.1], "Full") or Text.StartsWith([Attribute.1], "Part")),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Filtered Rows", "Attribute.1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1.1", "Attribute.1.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.1.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2:Emp ID", "ID"}, {"Column1:Location", "Location"}, {"Column3:Manager", "Manager"}, {"Column4:Email", "Email"}, {"Full Time:Name", "Name"}, {"Attribute.1.1", "Status"}, {"Attribute.2", "Question"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ID", "Name", "Email", "Location", "Manager", "Status", "Question", "Value"})
in
    #"Reordered Columns"

Book6
	A	B	C	D	E	F	G	H
1	ID	Name	Email	Location	Manager	Status	Question	Value
2	100001	Employee 1	email 1	Location 1	Manager 1	Full Time	Q1	0
3	100001	Employee 1	email 1	Location 1	Manager 1	Full Time	Q2	0
4	100001	Employee 1	email 1	Location 1	Manager 1	Full Time	Q3	0
5	100001	Employee 1	email 1	Location 1	Manager 1	Part Time	Q4	1
6	100001	Employee 1	email 1	Location 1	Manager 1	Part Time	Q5	0
7	100001	Employee 1	email 1	Location 1	Manager 1	Part Time	Q6	0
8	100001	Employee 1	email 1	Location 1	Manager 1	Part Time	Q7	0
9	100002	Employee 2	email 2	Location 2	Manager 2	Full Time	Q1	0
10	100002	Employee 2	email 2	Location 2	Manager 2	Full Time	Q2	0
11	100002	Employee 2	email 2	Location 2	Manager 2	Full Time	Q3	1
12	100002	Employee 2	email 2	Location 2	Manager 2	Part Time	Q4	0
13	100002	Employee 2	email 2	Location 2	Manager 2	Part Time	Q5	0
14	100002	Employee 2	email 2	Location 2	Manager 2	Part Time	Q6	0
15	100002	Employee 2	email 2	Location 2	Manager 2	Part Time	Q7	1
16	100003	Employee 3	email 3	Location 3	Manager 3	Full Time	Q1	0
17	100003	Employee 3	email 3	Location 3	Manager 3	Full Time	Q2	1
18	100003	Employee 3	email 3	Location 3	Manager 3	Full Time	Q3	0
19	100003	Employee 3	email 3	Location 3	Manager 3	Part Time	Q4	0
20	100003	Employee 3	email 3	Location 3	Manager 3	Part Time	Q5	0
21	100003	Employee 3	email 3	Location 3	Manager 3	Part Time	Q6	1
22	100003	Employee 3	email 3	Location 3	Manager 3	Part Time	Q7	0
Table1

Only showing first three employees. File runs 200 lines plus

Thanks so much!

 

[crispangilinan]

Hi & welcome to MrExcel.
How about

Sub crispangilinan()
   Dim Ary As Variant, Nary As Variant, Cols As Variant
   Dim r As Long, c As Long, nr As Long, nc As Long
  
   Cols = Array("", 2, 5, 4, 3, 1)
   Ary = Sheets("Sheet1").Range("A2").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 8)
   For r = 3 To UBound(Ary)
      For c = 6 To UBound(Ary, 2)
         nr = nr + 1
         For nc = 1 To 5
            Nary(nr, nc) = Ary(r, Cols(nc))
         Next nc
         Nary(nr, 6) = Ary(1, c)
         Nary(nr, 7) = Ary(2, c)
         Nary(nr, 8) = Ary(r, c)
      Next c
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, 8).Value = Nary
End Sub

Wow works like magic!