Convert multiple rows into columns, with keeping some grouping

Thierry1

New Member
Joined
Sep 23, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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 NameChild SurnameChild ClassParent NameParent email address
ANDERSENLea2ANDERSEN Marka.M@gmail.com
ELISVincent2ELIS Simonee.s@gmail.com
ELISValerie3ELIS Simonee.s@gmail.com
ELISAlex5ELIS Simonee.s@gmail.com
WILSONRayan2WILSON Annaw.a@gmail.com
WILSONClara3WILSON Annaw.a@gmail.com
WILSONRayan2BROWN Johnb.j@gmail.com
WILSONClara3BROWN Johnb.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 addressParent NameChild NameChild SurnameChild Class
a.M@gmail.comANDERSEN MarkANDERSENLea2
e.s@gmail.comELIS SimoneELISVincent2
e.s@gmail.comELIS SimoneELISValerie3
e.s@gmail.comELIS SimoneELISAlex5
w.a@gmail.comWILSON AnnaWILSONRayan2
w.a@gmail.comWILSON AnnaWILSONClara3
b.j@gmail.comBROWN JohnWILSONRayan2
b.j@gmail.comBROWN JohnWILSONClara3


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 addressParent NameChild Name 1Child Surname1Child Class1Child Name2Child Surname2Child Class2Child Name3Child Surname3Child Class3Child Name4Child Surname4Child Class4
a.M@gmail.comANDERSEN MarkANDERSENLea2
e.s@gmail.comELIS SimoneELISVincent2ELISValerie3ELISAlex5
w.a@gmail.comWILSON AnnaWILSONRayan2WILSONClara3
b.j@gmail.comBROWN JohnWILSONRayan2WILSONClara3


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


 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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.
 
Solution

Thierry1

New Member
Joined
Sep 23, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Thierry1

New Member
Joined
Sep 23, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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


a.M@gmail.comANDERSEN MarkANDERSENLea
2​
e.s@gmail.comELIS SimoneELISVincent
2​
e.s@gmail.comELIS SimoneELISValerie
3​
e.s@gmail.comELIS SimoneELISAlex
5​
w.a@gmail.comWILSON AnnaWILSONRayan
2​
w.a@gmail.comWILSON AnnaWILSONClara
3​
b.j@gmail.comBROWN JohnWILSONRayan
2​
b.j@gmail.comBROWN JohnWILSONClara
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.comELIS SimoneELIS
Valerie
3​
e.s@gmail.comELIS SimoneELIS
Alex
5​
e.s@gmail.comELIS SimoneELIS
Rayan
2​
w.a@gmail.comWILSON AnnaWILSON
Clara
3​
w.a@gmail.comWILSON AnnaWILSON
Rayan
2​
b.j@gmail.comBROWN JohnWILSON
Clara
3​
b.j@gmail.comBROWN JohnWILSON


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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
I wrote it based on what you originally posted, namely
+Fluff 1.xlsm
ABCDE
1Child NameChild SurnameChild ClassParent NameParent email address
2ANDERSENLea2ANDERSEN Marka.M@gmail.com
3ELISVincent2ELIS Simonee.s@gmail.com
4ELISValerie3ELIS Simonee.s@gmail.com
5ELISAlex5ELIS Simonee.s@gmail.com
6WILSONRayan2WILSON Annaw.a@gmail.com
7WILSONClara3WILSON Annaw.a@gmail.com
8WILSONRayan2BROWN Johnb.j@gmail.com
9WILSONClara3BROWN Johnb.j@gmail.com
10
Sheet1


Is your actual data like
+Fluff 1.xlsm
ABCDE
1Parent email addressParent NameChild NameChild SurnameChild Class
2a.M@gmail.comANDERSEN MarkANDERSENLea2
3e.s@gmail.comELIS SimoneELISVincent2
4e.s@gmail.comELIS SimoneELISValerie3
5e.s@gmail.comELIS SimoneELISAlex5
6w.a@gmail.comWILSON AnnaWILSONRayan2
7w.a@gmail.comWILSON AnnaWILSONClara3
8b.j@gmail.comBROWN JohnWILSONRayan2
9b.j@gmail.comBROWN JohnWILSONClara3
10
Sheet1
 

Thierry1

New Member
Joined
Sep 23, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,850
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Thierry1

New Member
Joined
Sep 23, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,962
Messages
5,744,053
Members
423,843
Latest member
alex2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top