4

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

Name Email
bob bob@example.com
bob bobby@example.com
bob bob1@example.com
sally sally@example.com
sally sallyabc@example.com

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 bob@example.com bobby@example.com bob1@example.com
sally sally@example.com sallyabc@example.com

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.

5

Using Miller and starting from this CSV:

Name,Email
bob,bob@example.com
bob,bobby@example.com
bob,bob1@example.com
sally,sally@example.com
sally,sallyabc@example.com

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 bob@example.com bobby@example.com bob1@example.com
sally sally@example.com sallyabc@example.com -
5
  • Can Miller also work with Excel files saved with xlsx format? Aug 2 at 10:41
  • @ReddyLutonadio No, CSV, TSV, JSON and other file formats miller.readthedocs.io/en/latest/file-formats.html
    – aborruso
    Aug 2 at 10:51
  • Thanks again! But this is printing the result to terminal. How do I make it output to a csv file?
    – TinyTiger
    Aug 2 at 10:58
  • 1
    @TinyTiger it's the shell: add >output.csv. I have edited my answer
    – aborruso
    Aug 2 at 11:31
  • When using output redirection, be incredibly careful about encodings. If you're using cmd you'll get the ANSI charset by default which can be rather problematic.
    – Voo
    Aug 3 at 10:39
6

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:

enter image description here

  1. Drag the cell down to fill in all values in column D. It will result like in the screenshot above.
  2. 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)),"")
  3. 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: enter image description here


SOURCE

1
  • Unfortunately, this won't work for me because it requires massive amounts of processing. I have 5870 rows to analyze and it won't play nice with that amount of data.
    – TinyTiger
    Aug 2 at 10:02
4

Add an auxiliary column with formula:

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

enter image description here

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

enter image description here

3
  • This looks promising, but I don't have that "from sheet" option in my "data" tab. I'm using Excel 2019 on MacOS. Maybe that is why?
    – TinyTiger
    Aug 2 at 10:06
  • @TinyTiger See if you have From Table/Range, Aug 2 at 10:27
  • Unfortunately not. But I do have From HTML, From Text, and New Database Query when I click on Get External Data in the Data tab.
    – TinyTiger
    Aug 2 at 11:05
0

If you have new Excel version, you can use these formulas.

Then you can do it this way: enter image description here The blue table is named Table1.

Used formulas:

  • E3: =UNIQUE(Table1[Name])
  • F2:N2: ="Mail"&COLUMN()-COLUMN($E$2)
  • F3 (stretched down): =INDEX(Table1[Mail];FILTER(TRANSPOSE(SORT(UNIQUE((ROW(Table1[Name])-ROW(Table1[[#Headers];[Name]]))*($E3=Table1[Name]))));TRANSPOSE(SORT(UNIQUE((ROW(Table1[Name])-ROW(Table1[[#Headers];[Name]]))*($E3=Table1[Name]))))>0;""))

Table is filled by SPILL function of the new Excel versions.

Example file: https://1drv.ms/x/s!ArVkYVSHnABFiiqzBYLPmoubvdHY?e=p7wOeF

In case you are using older Excel version, most of the functionality could be emulated using array functions. But it becomes much more chaotic...

1
  • 1
    I don't have those formulas unfortunately :(
    – TinyTiger
    Aug 2 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.