Bulk convert row data into columns on Mac Excel (transpose not suitable)

microsoft excelvba

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

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

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:

Name Email 1 Email 2 Email 3
bob [email protected] [email protected] [email protected]
sally [email protected] [email protected]

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.

Best Answer

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:

Name Email_1 Email_2 Email_3
bob [email protected] [email protected] [email protected]
sally [email protected] [email protected] -
Related Question