How to bulk convert row data into columns on Mac Excel (transpose not suitable)?

I have lots of data that looks like this. It has two columns:

But I need it to look like this. One name per row, with the emails added as extra columns as required. There is an unknown number of emails for each name:

I have to do this for a huge spreadsheet with thousands of rows. So I cannot manually convert each of them using something like transpose. I need a bulk/batch/automated solution.

I know that VBA is a thing that exists, but I don't know how to code it. If that is the solution can you please give me some direction of what the code should look like?

Non-VBA solutions are also very welcome.

Solution 1:

Using Miller and starting from this CSV:

Name,Email
bob,[email protected]
bob,[email protected]
bob,[email protected]
sally,[email protected]
sally,[email protected]

you can run:

mlr --csv nest --ivar ";" -f Email then nest --explode --values --across-fields -f Email then unsparsify input.csv >output.csv

to obtain:

Solution 2:

Starting with your example Column A: Name , Column B: Email

1. Enter this array formula: =INDEX($A$2:$A$6, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) into a blank cell, D2, for example, and press Shift + Ctrl + Enter keys together to get the correct result, see screenshot:

2. Drag the cell down to fill in all values in column D. It will result like in the screenshot above.
3. In cell E2 where you want your email insert this formula =IFERROR(INDEX($B$2:$B$16, MATCH(0,COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2,1,0),0)),"")
4. Drag the cell right 5-10 cells (or how much you consider is enough) to fill in and then drag the row down to fill in emails for each person.

Result:

SOURCE

Solution 3:

Add an auxiliary column with formula:

="Mail "&COUNTIF($A$2:A2,A2)

Open Power Query Editor - Select Column1- go to Transform- Pivot Column- select Email and under Advanced option select Don't Aggregate: