Convert multiple rows into columns, with keeping some grouping

[Thierry1]

Hello,
I am trying to group a list for our school parents.
I searched for some solution on the forum, but it was often with PowerQuery, which I don't know. (I am not even sure if I have on my Office 2019..)
I found few solutions for converting rows into columns, but it was only with 2 columns for starting, and I have few columns that I need to keep as "grouped".
Thanks for your help!

Here is the data I have, for parents and children. (All is fake names, for this example)

For each child, we have the parent name and email address.

Child Name	Child Surname	Child Class	Parent Name	Parent email address
ANDERSEN	Lea	2	ANDERSEN Mark	a.M@gmail.com
ELIS	Vincent	2	ELIS Simone	e.s@gmail.com
ELIS	Valerie	3	ELIS Simone	e.s@gmail.com
ELIS	Alex	5	ELIS Simone	e.s@gmail.com
WILSON	Rayan	2	WILSON Anna	w.a@gmail.com
WILSON	Clara	3	WILSON Anna	w.a@gmail.com
WILSON	Rayan	2	BROWN John	b.j@gmail.com
WILSON	Clara	3	BROWN John	b.j@gmail.com

I would need to change, for our parents mailing list, with the email address as the unique ID, but I need separate fields for additional children.

Parent email address	Parent Name	Child Name	Child Surname	Child Class
a.M@gmail.com	ANDERSEN Mark	ANDERSEN	Lea	2
e.s@gmail.com	ELIS Simone	ELIS	Vincent	2
e.s@gmail.com	ELIS Simone	ELIS	Valerie	3
e.s@gmail.com	ELIS Simone	ELIS	Alex	5
w.a@gmail.com	WILSON Anna	WILSON	Rayan	2
w.a@gmail.com	WILSON Anna	WILSON	Clara	3
b.j@gmail.com	BROWN John	WILSON	Rayan	2
b.j@gmail.com	BROWN John	WILSON	Clara	3

For each parent, I would like to show the children in additional columns. (3,4, 5 columns if some parents had 5 children in the school)

I would like to obtain something like this:

Parent email address

Parent Name

Child Name 1

Child Surname1

Child Class1

Child Name2

Child Surname2

Child Class2

Child Name3

Child Surname3

Child Class3

Child Name4

Child Surname4

Child Class4

a.M@gmail.com

ANDERSEN Mark

ANDERSEN

Lea

2

e.s@gmail.com

ELIS Simone

ELIS

Vincent

2

ELIS

Valerie

3

ELIS

Alex

5

w.a@gmail.com

WILSON Anna

WILSON

Rayan

2

WILSON

Clara

3

b.j@gmail.com

BROWN John

WILSON

Rayan

2

WILSON

Clara

3

Thanks a lot for your help.
I am the only "computer guy" in our parents group... but obviously, I only know the very basic things to make a mailing list..

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Thierry()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   With Sheets("Sheet1")
      Ary = .Range("A1:E" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To 32)
   For r = 2 To UBound(Ary)
      If Ary(r, 4) <> Ary(r - 1, 4) Then
         nr = nr + 1
         Nary(nr, 1) = Ary(r, 4)
         Nary(nr, 2) = Ary(r, 5)
         Nary(nr, 3) = Ary(r, 1)
         Nary(nr, 4) = Ary(r, 2)
         Nary(nr, 5) = Ary(r, 3)
         c = 6
      Else
         Nary(nr, c) = Ary(r, 1)
         Nary(nr, c + 1) = Ary(r, 2)
         Nary(nr, c + 2) = Ary(r, 3)
         c = c + 3
      End If
   Next r
   Sheets("Sheet2").Range("A2").Resize(UBound(Ary), 11).Value = Nary
End Sub

Change sheet names to suit.

 

[Thierry1]

Thank you Fluff.

I must say that I never used VBA (or the PowerQuery mentionned in some other threads..)
I don't even know where I would past this script...

Is it somehting easily achievable? or should I try with formula?

 

[Fluff]

Have a look here how to use macros. Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com

 

[Thierry1]

Thank you. I manage to use your code.
I run the macro on this table in sheet 1

a.M@gmail.com	ANDERSEN Mark	ANDERSEN	Lea	2​
e.s@gmail.com	ELIS Simone	ELIS	Vincent	2​
e.s@gmail.com	ELIS Simone	ELIS	Valerie	3​
e.s@gmail.com	ELIS Simone	ELIS	Alex	5​
w.a@gmail.com	WILSON Anna	WILSON	Rayan	2​
w.a@gmail.com	WILSON Anna	WILSON	Clara	3​
b.j@gmail.com	BROWN John	WILSON	Rayan	2​
b.j@gmail.com	BROWN John	WILSON	Clara	3​

I get this result in sheet 2. Not working well.

It should be one line for each parent
ANDERSEN Mark
ELIS Simone
WILSON Anna
BROWN John

Vincent	2​	e.s@gmail.com	ELIS Simone	ELIS
Valerie	3​	e.s@gmail.com	ELIS Simone	ELIS
Alex	5​	e.s@gmail.com	ELIS Simone	ELIS
Rayan	2​	w.a@gmail.com	WILSON Anna	WILSON
Clara	3​	w.a@gmail.com	WILSON Anna	WILSON
Rayan	2​	b.j@gmail.com	BROWN John	WILSON
Clara	3​	b.j@gmail.com	BROWN John	WILSON

I will try to dig into your code, if I can make out of something... Maybe you tested on different columns and I need to adapt..
Thank you

 

[Fluff]

I wrote it based on what you originally posted, namely

+Fluff 1.xlsm
	A	B	C	D	E
1	Child Name	Child Surname	Child Class	Parent Name	Parent email address
2	ANDERSEN	Lea	2	ANDERSEN Mark	a.M@gmail.com
3	ELIS	Vincent	2	ELIS Simone	e.s@gmail.com
4	ELIS	Valerie	3	ELIS Simone	e.s@gmail.com
5	ELIS	Alex	5	ELIS Simone	e.s@gmail.com
6	WILSON	Rayan	2	WILSON Anna	w.a@gmail.com
7	WILSON	Clara	3	WILSON Anna	w.a@gmail.com
8	WILSON	Rayan	2	BROWN John	b.j@gmail.com
9	WILSON	Clara	3	BROWN John	b.j@gmail.com
10
Sheet1

Is your actual data like

+Fluff 1.xlsm
	A	B	C	D	E
1	Parent email address	Parent Name	Child Name	Child Surname	Child Class
2	a.M@gmail.com	ANDERSEN Mark	ANDERSEN	Lea	2
3	e.s@gmail.com	ELIS Simone	ELIS	Vincent	2
4	e.s@gmail.com	ELIS Simone	ELIS	Valerie	3
5	e.s@gmail.com	ELIS Simone	ELIS	Alex	5
6	w.a@gmail.com	WILSON Anna	WILSON	Rayan	2
7	w.a@gmail.com	WILSON Anna	WILSON	Clara	3
8	b.j@gmail.com	BROWN John	WILSON	Rayan	2
9	b.j@gmail.com	BROWN John	WILSON	Clara	3
10
Sheet1

 

[Thierry1]

Thank you so much!
It works perfectly with the table 1 in original post.

I would not understand all the code, to be able to adapt it. (I see the "if the cell is identical, copy the data to the next cells"... but it's only a basic understanding.)

It's going to save me hours and hours... Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Thierry1]

One additional info, if it can help others: it seems the original table should be sorted by column email and then by the column parent name.

Since the script is grouping by each parent, with several children.

I had few cases where the mother and father are using the same email and it was not working.

In my list, it was
Kid1 Father father@email.com
Kid1 Mother father@email.com
Kid2 Father father@email.com
Kid2 Mother father@email.com

it was transformed into 4 lines, instead of 2.