Data Collection Help

[Jason06]

I'm trying to build a sheet that collects addresses I receive from clients about available work throughout the US. This information comes to me via email from different clients. Each client sends me the information in a different format but all of them have the same information. The information I need is "work order number, Address, City, State, Zip and the email address" from the sender. I then have been taking that information and copy/paste it to an excel file and delete the items I don't need and cut/paste the the ones i do in the correct columns in excel. I then use the CountIF statement to count how many jobs are in each state. How can I make this process easier? I'd like to be able to paste the information I receive from a client into a window or an area in excel and then it pulls the info I want and puts it in the correct cells. Any help you can provided would be greatly appreciated!

Thank you!!

 

[dUBBINS]

Is the information always labeled as "work order number,..."? Can you post a few different examples of what the data may look like that you want it to sort?

 

[FDibbins]

Agree that we need to see some sample data, as well as what your expected outcome would be

 

[Jason06]

Here are some examples of the emails I receive.

Example 1:

WO #	WO Type	Cust #	Ordered	Due	Address	City	State	Zip	County	Area
M10672488	Continuing REO Service	160	1/31/2017	2/5/2017	1135 HERNAGE CREEK RD	EAGLE	CO	81631	EAGLE	CO-065
M10712427	Continuing REO Service	160	2/14/2017	2/19/2017	1135 HERNAGE CREEK RD	EAGLE	CO	81631	EAGLE	CO-065

<tbody>
</tbody>

-------------------------------------------------------------------------------------------------------------------------------------------------------------
Example 2:

917 OVERLOOK LN	HEBER SPRINGS	AR	72543	23283669	Work Order	Other Misc. Svc - 1 - replace vent covers
4903 MAKI ROAD	BROOKSTON	MN	55711	23260385	Work Order	Initial Utilities Data, Lock Box, Mandatory Survey, Rekey, Snow Removal - Initial, VA Board Up, VA Debris, VA Pool/Spa Securing, VA Property Photos, VA Security Door, VA Sump Pump, Winterization (Dry Wint)

<tbody>
</tbody>

--------------------------------------------------------------------------------------------------------------------------------------------------------------
Example 3:

PropertyStatus	Address1	City	State	ZipCode	WorkOrderNumber	ItemType	ServiceList
Active	418 N 2ND ST	BLYTHE	CA	92225	23182997	Work Order	FNMA Chimney Cap
Active	1307 W 11TH ST	ALTURAS	CA	96101	23203467	Work Order	Mandatory Survey, Trip Chg- Other - Confirm Access-Call from site, Wint Inspection - Ensure Wint is intact-Call From Site

<tbody>
</tbody>

---------------------------------------------------------------------------------------------------------------------------------------------------------------

Here is what I've created. I don't necessary need it to look like this. The "Contact" column would be the email address from who I received the email from. I also have a count table that counts the number of WO# (work order number) in each state. I've pasted that below. I was hoping I could attach a screen shot but it wouldn't let me. Let me know if you need anything else. Thanks again everyone!!

WO#	WO Type	Address	City	State	Zip	Contact
M10727686	Additional Bids	804 Sierra Vista	TWIN PEAKS	CA	92391
M10727804	Utilities	1880 DERRICK RD	EL CENTRO	CA	92243
M10728409	Re-Secure	510 D ST	NEEDLES	CA	92363
23182997	FNMA Chimney Cap	418 N 2ND ST	BLYTHE	CA	92225

<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

Count
Alabama	AL	0	Montana	MT	0
Alaska	AK	0	Nebraska	NE	0
Arizona	AZ	0	Nevada	NV	0
Arkansas	AR	0	New Hampshire	NH	0
California	CA	3	New Jersey	NJ	0
Colorado	CO	4	New Mexico	NM	0
Connecticut	CT	3	New York	NY	0
Delaware	DE	1	North Carolina	NC	0
Florida	FL	2	North Dakota	ND	0
Georgia	GA	1	Ohio	OH	0
Hawaii	HI	0	Oklahoma	OK	0
Idaho	ID	0	Oregon	OR	0
Illinois	IL	1	Pennsylvania	PA	0
Indiana	IN	1	Rhode Island	RI	0
Iowa	IA	4	South Carolina	SC	0
Kansas	KS	3	South Dakota	SD	0
Kentucky	KY	0	Tennessee	TN	0
Louisiana	LA	1	Texas	TX	0
Maine	ME	2	Utah	UT	0
Maryland	MD	0	Vermont	VT	0
Massachusetts	MA	3	Virginia	VA	0
Michigan	MI	1	Washington	WA	0
Minnesota	MN	4	West Virginia	WV	0
Mississippi	MS	0	Wisconsin	WI	0
Missouri	MO	1	Wyoming	WY	0

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Jason06]

Not always, Sometimes its labeled as WO#, Work Order Number and other times there is no label. However; from one client it will always start with a M followed by digits. Another client will start with a "2" followed by 7 more digits.