Data transpose from row's to columns - HELP PLEASE

UllrichNumber7

New Member
Joined
Jun 26, 2014
Messages
5
Hey I need some help with some data reformatting via excel. I believe I need to create a set of macros and IF statements for this, but I definitely need some help and do not know where to start.

Here is my situation:

I have a excel document 521,165 Rows in it, all in column A. The issue with this is, its full of data. The entries need to be elongated into 1 row per entry, not anywhere from 3-6 rows per. This is a list of contact information, including company names, address, city state zip, email, and phone number. Each comma is a new row on the spread sheet. That being said, not every entry has a email, phone, or all of the information. What I need to do with it is turn it from:

Jane Doe inc.
Example lane
City, state, 81346
example@gmail.com
(808) 064-2654

But some of the entries may be
Jane Doe inc.
Example lane
City, state, 81346
(808) 064-2654

(missing some, you get the point)

Into 1 row, with each data in its own separate cell. We also need to break the city, state, and zip into their own cells respectively so that we can continue to sort the data by state, zip, city, etc. Can someone please help? I have no idea where to go with this and it is simply not feasible in anyway to copy and past + transpose to reformat this. There must be a different way!

Help is greatly appreciated!

Daniel
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

UllrichNumber7

New Member
Joined
Jun 26, 2014
Messages
5
Essentially I think I would need a logic statement macro, That says select phone number and up, if phone number isnt there, select email and up, if email isnt there select,city state and up, if city state isnt there select street and up, and then copy, special paste transpose to a new sheet then delete selection rows and then repeat.
 

Slimm

New Member
Joined
Jun 26, 2014
Messages
10
In a new worksheet I would map back to this worksheet, if the email option is the only difference i'd use a logic statement looking for the "()" or "@" symbol. Then use Text to column on the City, State, Zip to separate them into their own columns
 

UllrichNumber7

New Member
Joined
Jun 26, 2014
Messages
5
In a new worksheet I would map back to this worksheet, if the email option is the only difference i'd use a logic statement looking for the "()" or "@" symbol. Then use Text to column on the City, State, Zip to separate them into their own columns

There is no way for me to know if email is the only inconsistency without combing through thousands of rows though? I know email is for sure one inconsistency, but I do not know if some of them may be missing address, phone, etc.
 

UllrichNumber7

New Member
Joined
Jun 26, 2014
Messages
5
In a new worksheet I would map back to this worksheet, if the email option is the only difference i'd use a logic statement looking for the "()" or "@" symbol. Then use Text to column on the City, State, Zip to separate them into their own columns

What would this logical statement be?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,033
Messages
5,639,658
Members
417,104
Latest member
Nelsini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top