Help Parsing Data to Columns

[redhots4]

I need some guru help for a real challenge I'm facing. I have access to data from a website with 4500-5000 records. Each record has 11-14 rows of data containing demographic details about the people records. I want to manipulate this data into a normalized data file with columns for each data point. (Name, address, email, company name, etc). Not all records have the same number of data points, so creating lookups based on a consistent master field isn't possible. For example, not all records use "nickname", although I want to see the nickname when it's available. Sometimes the Company Name is missing, so in field where I expect to see Company, I see the address value.

I've copied two records into the sample file and shown my desired output. I appreciate any help I can get!

Sunshine Nurse Email Contact Listv2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Raw Data	Full Name	Nickname	Cust #	Email	Company	Address	City	State	Zip	Office	cell	Fax
2	Chris Smith	Chris Smith	Chris	51094	email2@aol.com		50 Edgewater Dr	Coral Gables	FL	33133	305-555-7274	305-555-7284	305-555-0134
3	Chris Smith
4	"Chris"
5	Cust #51094
6	Eligible
7	50 Edgewater Dr
8	Coral Gables, FL 33133-6988
9	Office: 305-555-7274
10	Cell: 305-555-7284
11	Fax: 305-555-0134
12	email2@aol.com
13	Randy Jones	Randy Jones		91186	rjones@ABCmanu.com	ABC Manufacturing Inc.	500 S Jones Ave Ste 100	Fort Lauderdale	FL	33301	954-555-6032	954-555-6032
14	Randy Jones
15	Cust #91186
16	Eligible
17	ABC Manufacturing Inc.
18	500 S Jones Ave Ste 100
19	Fort Lauderdale, FL 33301-4437
20	Office: 954-555-6032
21	Cell: 954-555-6032
22	rjones@ABCmanu.com
Sheet2

 

[Micron]

Unless you can
a) include the field name in the data you're scraping (so it would be the first column), or
b) include all field regardless of whether or not they contain data (and always in the same order)
I'd say the input is too volatile for any non-manual solution. Some of your values contain keywords that could help (e.g. Cell) but not all of them do.

Probably the hardest part after that would be breaking up the addresses as you've shown as I expect they would not always have the same delimiters (cityCommaSpaceStateSpaceZip)

 

[redhots4]

Thanks for replying. I've already spent a lot of time writing formulas to split the data into columns as shown on the sheet here. What I can't figure out is how to get all of that data on one row with the Full Name in the left-hand column as the lookup. (This table contains sensitive data so I've mocked this up to show what it looks like. Therefore you're not seeing the actual formulas used to populate the various columns)

Sunshine Nurse Email Contact Listv2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Raw Data	Full Name	Last Name	First Name	Nickname	Cust #	Email	Company	Address	City	State	Zip	Office	cell	Fax
2	Chris Smith
3	Chris Smith	Chris Smith	Smith	Chris
4	"Chris"				Chris
5	Cust #51094					51094
6	Eligible
7	50 Edgewater Dr								50 Edgewater Dr
8	Coral Gables, FL 33133-6988									Coral Gables	FL	33133
9	Office: 305-555-7274												305-555-7274
10	Cell: 305-555-7284													305-555-7284
11	Fax: 305-555-0134														305-555-0134
12	email2@aol.com						email2@aol.com
13	Randy Jones
14	Randy Jones	Randy Jones	Jone	Randy
15	Cust #91186					91186
16	Eligible
17	ABC Manufacturing Inc.							ABC Manufacturing Inc.
18	500 S Jones Ave Ste 100								500 S Jones Ave Ste 100
19	Fort Lauderdale, FL 33301-4437									Fort Lauderdale	FL	33301
20	Office: 954-555-6032												954-555-6032
21	Cell: 954-555-6032													954-555-6032
22	rjones@ABCmanu.com						rjones@ABCmanu.com
Sheet2

 

[Micron]

I'm not much of a formula guy; was thinking of identifying the components and putting them in the correct column by using code. Main problem is, I see no reliable way to determine which row belongs where. Any cell with "Cust" in it seems obvious; others not so much.