Reorganising data

[Shrewrob]

Hello All,

I'd be grateful for your help with this one.

A data sample is below:

Name	Course 1	Course 2	Course 3	Course 4	Course 5	Course 6
Andrew	05-05-2022	05-05-2022	05-05-2022	05-05-2022	08-03-2024	08-03-2024
Bev	05-05-2022	05-05-2022	05-05-2022	05-05-2022	05-05-2022	05-05-2022
Charlotte	06-04-2023	06-04-2023	06-04-2023	06-04-2023	30-03-2024	30-03-2024
Elena	05-05-2022	15-03-2024	05-05-2022	05-05-2022	05-05-2022	05-05-2022
Emma	07-07-2022	07-07-2022	07-07-2022	07-07-2022	29-03-2024	29-03-2024
Jill	05-05-2022	05-05-2022	27-03-2024	05-05-2022	27-03-2024	27-03-2024

I want create a table with 3 columns like this:

Name	Course Title	Date
Andrew	Course 1	05-05-2022
Andrew	Course 2	05-05-2022
Andrew	Course 3	05-05-2022
Andrew	Course 4	05-05-2022
Andrew	Course 5	08-03-2024
Andrew	Course 6	08-03-2024
Bev	Course 1	05-05-2022
Bev	Course 2	05-05-2022
Bev	Course 3	05-05-2022
Bev	Course 4	05-05-2022
Bev	Course 5	05-05-2022
Bev	Course 6	05-05-2022

Whats the best way to do this please.

Many thanks. Rob

 

[awoohaw]

You can do this with Power Query with a few clicks. It can also be done with a fairly complicated formula. I am not an expert in Power Query. There is a Mr Excel forum called "Power Tools" that you can get this assistance in. You can also review some youtube videos on "Un Pivoting" Data with Power Query.

look at this youtube video:

 

[Shrewrob]

You can do this with Power Query with a few clicks. It can also be done with a fairly complicated formula. I am not an expert in Power Query. There is a Mr Excel forum called "Power Tools" that you can get this assistance in. You can also review some youtube videos on "Un Pivoting" Data with Power Query.

look at this youtube video:

Brilliant! thanks for your help

 

[Dave Patton]

A formula solution follows. Someone may provide a solution with one formula or improve this quick thought.

ToCol.xlsm
	A	B	C	D	E	F	G
1
2	Name	Course 1	Course 2	Course 3	Course 4	Course 5	Course 6
3	Andrew	05-05-22	05-05-22	05-05-22	05-05-22	08-03-24	08-03-24
4	Bev	05-05-22	05-05-22	05-05-22	05-05-22	05-05-22	05-05-22
5	Charlotte	06-04-23	06-04-23	06-04-23	06-04-23	30-03-24	30-03-24
6	Elena	05-05-22	15-03-24	05-05-22	05-05-22	05-05-22	05-05-22
7	Emma	07-07-22	07-07-22	07-07-22	07-07-22	29-03-24	29-03-24
8	Jill	05-05-22	05-05-22	27-03-24	05-05-22	27-03-24	27-03-24
9
10
11
12	Name	Date	Course
13	Andrew	05-05-22	Course 1
14	Andrew	05-05-22	Course 2
15	Andrew	05-05-22	Course 3
16	Andrew	05-05-22	Course 4
17	Andrew	08-03-24	Course 5
18	Andrew	08-03-24	Course 6
19	Bev	05-05-22	Course 1
20	Bev	05-05-22	Course 2
21	Bev	05-05-22	Course 3
22	Bev	05-05-22	Course 4
23	Bev	05-05-22	Course 5
24	Bev	05-05-22	Course 6
25	Charlotte	06-04-23	Course 1
26	Charlotte	06-04-23	Course 2
27	Charlotte	06-04-23	Course 3
28	Charlotte	06-04-23	Course 4
29	Charlotte	30-03-24	Course 5
30	Charlotte	30-03-24	Course 6
31	Elena	05-05-22	Course 1
32	Elena	15-03-24	Course 2
33	Elena	05-05-22	Course 3
34	Elena	05-05-22	Course 4
35	Elena	05-05-22	Course 5
36	Elena	05-05-22	Course 6
37	Emma	07-07-22	Course 1
38	Emma	07-07-22	Course 2
39	Emma	07-07-22	Course 3
40	Emma	07-07-22	Course 4
41	Emma	29-03-24	Course 5
42	Emma	29-03-24	Course 6
43	Jill	05-05-22	Course 1
44	Jill	05-05-22	Course 2
45	Jill	27-03-24	Course 3
46	Jill	05-05-22	Course 4
47	Jill	27-03-24	Course 5
48	Jill	27-03-24	Course 6
49
2c

Cell Formulas
Range	Formula
A13:B48	A13	=LET(s,SEQUENCE(,6),HSTACK(TOCOL(IF(s,A3:A8)),TOCOL(B3:G8)))
C13:C48	C13	=LET(c,TOCOL(B2:G2),VSTACK(c,c,c,c,c,c))
Dynamic array formulas.