Splitting/transposing some contact data easily...

so1id

New member
Joined
Oct 23, 2012
Messages
2
Reaction score
0
Points
0
Hi everyone,

Sorry to be "that guy" who shows up with one post to his name asking for help, but I could do with a friendly point in the right direction, I'm not the most advanced of excel users...

Here's my problem:

Where I work I've been given a bit of a side project to do, it's pretty dull but it involves getting contact info for certain companies from one messy spreadsheet into another. The email addresses are listed horizontally across cells in some cases, and in a LOT of the others they are all in one cell split by semi-colons. I need to find a way to quickly transpose these whilst keeping the company names/id's/other relevant info intact corresponding with the email addresses.

I know about "Paste Special>Transpose" but it's still rather time consuming, and the bigger problem is the addresses grouped in one cell split by semi-colons.

I've attached an example workbook with two worksheets showing what I'm starting with and what I hope to end up with...

If anyone has any way this could be done easier than copy and pasting one by one I'd be most appreciative! Thanks a lot...

P.s. I wasn't sure if this was a problem that could be solved with a formula or if there's some other way to do this within the excel interface, as such I hope I'm in the right sub forum!
 

Attachments

  • example worksheet.xlsx
    39.4 KB · Views: 19

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
will the cells with multiple emails always be in column C (Domain 1) or could they show up in any column ?
 

so1id

New member
Joined
Oct 23, 2012
Messages
2
Reaction score
0
Points
0
will the cells with multiple emails always be in column C (Domain 1) or could they show up in any column ?

Yes the cells with multiple emails are all in the "first" email column, i.e domain 1
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
See if this is what you are trying to accomplish.

1. Click button
2. Click yes on the message box that pops up.

This file contains Macro Code
 

Attachments

  • transpose_emails.xlsm
    19 KB · Views: 15

ekavana186

New member
Joined
Mar 28, 2011
Messages
5
Reaction score
0
Points
0
Location
Richmond, VA
Excel Version(s)
2010
Yes the cells with multiple emails are all in the "first" email column, i.e domain 1

Highlight the column the data is in, Go to Data tab, select Text to Columns, select delimited, select ; (semicolon), and if I read you example correctly then TADA!
 
Top