Breaking out names and email addresses that are many reply alls in one column, without losing reference point

[AtariBaby]

I have a spreadsheet that looks like this. I exported it from Outlook.

Col A Names | Col B email | Col C Subject

The idea was to see what contacts to keep and what to delete, with the name and subject line helping the human who makes that decision.

The problem is the list contains many cells with multiple names and email addresses. I have wracked my brains and asked a lot of excel people:

How can I break those multiple items, separated by semicolons into one per cell, but still have a reference point to the subject line? And how do I also do the Names cell so it’s synchronous with the broken out email cells?

Whether Excel formulas or 3rd party app or tool, I just really want to solve this problem I’m stuck on.

Thanks for being here and for reading this!

 

[Alex Blakenburg]

Does make any difference if you add a zero in this line ? ie change from 100k to 1m
ReDim arrOut(1 To 100000, 1 To UBound(arrSrc, 2) + 2)

What happen when it "breaks" ?
Do you get an error ?
Do some lines work and some not ?
Is the delimiter definitely still ";" ?
Are there any merged cells ?
Does Cubist formula produce a result on the same data that breaks the code ?

 

[AtariBaby]

Oh sorry @Alex Blakenburg
It works when I try 3 to 5 rows. If I try to do a lot of rows, the result I get is a column of sequential numbers and a row of subject lines. This happens anytime I’ve tried more than like 3 rows with your vba. I don’t know what’s going on there but I tried 300 and got the undesired result. I don’t know more than that but I can try anything.
Cubist formula works if I do small sets of about 20-40 rows at a time. if I do more, i get Calc error presumably because it's too much data, if I understood my conversations with Cubist in this thread.

Yes the delimiter is still semicolon.

I’ll try your change now if I can find it

Update: adding the zero produced an interesting result
Column 1 header reads No
The column is sequential numbers
Column 2 says Subject but appears to be the names!
Column 3 says Name but is sequential numbers
Column 4 says Email but is the subjects

Promisingly it did break the names out to individuals cells while keeping them with the original subject line

 

[AtariBaby]

Oh sorry @Alex Blakenburg
It works when I try 3 to 5 rows. If I try to do a lot of rows, the result I get is a column of sequential numbers and a row of subject lines. This happens anytime I’ve tried more than like 3 rows with your vba. I don’t know what’s going on there but I tried 300 and got the undesired result. I don’t know more than that but I can try anything.
Cubist formula works if I do small sets of about 20-40 rows at a time. if I do more, i get Calc error presumably because it's too much data, if I understood my conversations with Cubist in this thread.

Yes the delimiter is still semicolon.

I’ll try your change now if I can find it

Update: adding the zero produced an interesting result
Column 1 header reads No
The column is sequential numbers
Column 2 says Subject but appears to be the names!
Column 3 says Name but is sequential numbers
Column 4 says Email but is the subjects

Promisingly it did break the names out to individuals cells while keeping them with the original subject line

The emails do not actually appear in the result

 

[Alex Blakenburg]

Can you give me a picture showing the row and column references.
Include the heading line and at least 1 row of data.

 

[AtariBaby]

Can you give me a picture showing the row and column references.
Include the heading line and at least 1 row of data.

Okay, sorry, working my way through obscuring sensitive data and reducing the size so i can upload it

 

[AtariBaby]

Okay, sorry, working my way through obscuring sensitive data and reducing the size so i can upload it

By the way, this is the result i got after adding a zero. It’s a little cut off at the top but it says A B C D

 

[Alex Blakenburg]

I need to see the same picture for the related rows before the macro is run.
There is something in your data that is different to the test data.

 

[AtariBaby]

I need to see the same picture for the related rows before the macro is run.
There is something in your data that is different to the test data.

The first image is that! I can privately send you the document if you (and the site rules) allow

 

[Alex Blakenburg]

So if you run the macro on sheet6 you get that output right ?
Unfortunately the rules don't allow that.
Can you leave only the first 5 data rows (down to row 6) of sheet 6.
Only use the delete key to hide data (in case there are unusual characters at end and beginning)
Test problem still exists on sample.
Put sample on Google drive or Dropbox etc make it available to anyone with the link and post the link here.

 

[AtariBaby]

That’s correct.