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:
[TABLE="width: 1004"]
<tbody>[TR]
[TD]WO #
[/TD]
[TD]WO Type
[/TD]
[TD]Cust #
[/TD]
[TD]Ordered
[/TD]
[TD]Due
[/TD]
[TD]Address
[/TD]
[TD]City
[/TD]
[TD]State
[/TD]
[TD]Zip
[/TD]
[TD]County
[/TD]
[TD]Area
[/TD]
[/TR]
[TR]
[TD]M10672488
[/TD]
[TD]Continuing REO Service
[/TD]
[TD]160
[/TD]
[TD]1/31/2017
[/TD]
[TD]2/5/2017
[/TD]
[TD]1135 HERNAGE CREEK RD
[/TD]
[TD]EAGLE
[/TD]
[TD]CO
[/TD]
[TD]81631
[/TD]
[TD]EAGLE
[/TD]
[TD]CO-065
[/TD]
[/TR]
[TR]
[TD]M10712427
[/TD]
[TD]Continuing REO Service
[/TD]
[TD]160
[/TD]
[TD]2/14/2017
[/TD]
[TD]2/19/2017
[/TD]
[TD]1135 HERNAGE CREEK RD
[/TD]
[TD]EAGLE
[/TD]
[TD]CO
[/TD]
[TD]81631
[/TD]
[TD]EAGLE
[/TD]
[TD]CO-065
[/TD]
[/TR]
</tbody>[/TABLE]
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Example 2:
[TABLE="width: 926"]
<tbody>[TR]
[TD]917 OVERLOOK LN
[/TD]
[TD]HEBER SPRINGS
[/TD]
[TD]AR
[/TD]
[TD]72543
[/TD]
[TD]23283669
[/TD]
[TD]Work Order
[/TD]
[TD]Other Misc. Svc - 1 - replace vent covers
[/TD]
[/TR]
[TR]
[TD]4903 MAKI ROAD
[/TD]
[TD]BROOKSTON
[/TD]
[TD]MN
[/TD]
[TD]55711
[/TD]
[TD]23260385
[/TD]
[TD]Work Order
[/TD]
[TD]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)
[/TD]
[/TR]
</tbody>[/TABLE]
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Example 3:
[TABLE="width: 827"]
<tbody>[TR]
[TD]PropertyStatus
[/TD]
[TD]Address1
[/TD]
[TD]City
[/TD]
[TD]State
[/TD]
[TD]ZipCode
[/TD]
[TD]WorkOrderNumber
[/TD]
[TD]ItemType
[/TD]
[TD]ServiceList
[/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD]418 N 2ND ST
[/TD]
[TD]BLYTHE
[/TD]
[TD]CA
[/TD]
[TD]92225
[/TD]
[TD]23182997
[/TD]
[TD]Work Order
[/TD]
[TD]FNMA Chimney Cap
[/TD]
[/TR]
[TR]
[TD]Active
[/TD]
[TD]1307 W 11TH ST
[/TD]
[TD]ALTURAS
[/TD]
[TD]CA
[/TD]
[TD]96101
[/TD]
[TD]23203467
[/TD]
[TD]Work Order
[/TD]
[TD]Mandatory Survey, Trip Chg- Other - Confirm Access-Call from site, Wint Inspection - Ensure Wint is intact-Call From Site
[/TD]
[/TR]
</tbody>[/TABLE]
---------------------------------------------------------------------------------------------------------------------------------------------------------------

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!!

[TABLE="width: 943"]
<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]WO#[/TD]
[TD]WO Type[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD]Contact[/TD]
[/TR]
[TR]
[TD]M10727686[/TD]
[TD]Additional Bids[/TD]
[TD]804 Sierra Vista[/TD]
[TD]TWIN PEAKS[/TD]
[TD]CA[/TD]
[TD]92391[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M10727804[/TD]
[TD]Utilities[/TD]
[TD]1880 DERRICK RD[/TD]
[TD]EL CENTRO[/TD]
[TD]CA[/TD]
[TD]92243[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M10728409[/TD]
[TD]Re-Secure[/TD]
[TD]510 D ST[/TD]
[TD]NEEDLES[/TD]
[TD]CA[/TD]
[TD]92363[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23182997[/TD]
[TD]FNMA Chimney Cap[/TD]
[TD]418 N 2ND ST[/TD]
[TD]BLYTHE[/TD]
[TD]CA[/TD]
[TD]92225[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 352"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 6"] Count[/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD]AL[/TD]
[TD="align: right"]0[/TD]
[TD]Montana[/TD]
[TD]MT[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Alaska[/TD]
[TD]AK[/TD]
[TD="align: right"]0[/TD]
[TD]Nebraska[/TD]
[TD]NE[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[TD]AZ[/TD]
[TD="align: right"]0[/TD]
[TD]Nevada[/TD]
[TD]NV[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Arkansas[/TD]
[TD]AR[/TD]
[TD="align: right"]0[/TD]
[TD]New Hampshire[/TD]
[TD]NH[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]CA[/TD]
[TD="align: right"]3[/TD]
[TD]New Jersey[/TD]
[TD]NJ[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Colorado[/TD]
[TD]CO[/TD]
[TD="align: right"]4[/TD]
[TD]New Mexico[/TD]
[TD]NM[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Connecticut[/TD]
[TD]CT[/TD]
[TD="align: right"]3[/TD]
[TD]New York[/TD]
[TD]NY[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Delaware[/TD]
[TD]DE[/TD]
[TD="align: right"]1[/TD]
[TD]North Carolina[/TD]
[TD]NC[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Florida[/TD]
[TD]FL[/TD]
[TD="align: right"]2[/TD]
[TD]North Dakota[/TD]
[TD]ND[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Georgia[/TD]
[TD]GA[/TD]
[TD="align: right"]1[/TD]
[TD]Ohio[/TD]
[TD]OH[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Hawaii[/TD]
[TD]HI[/TD]
[TD="align: right"]0[/TD]
[TD]Oklahoma[/TD]
[TD]OK[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Idaho[/TD]
[TD]ID[/TD]
[TD="align: right"]0[/TD]
[TD]Oregon[/TD]
[TD]OR[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Illinois[/TD]
[TD]IL[/TD]
[TD="align: right"]1[/TD]
[TD]Pennsylvania[/TD]
[TD]PA[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Indiana[/TD]
[TD]IN[/TD]
[TD="align: right"]1[/TD]
[TD]Rhode Island[/TD]
[TD]RI[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Iowa[/TD]
[TD]IA[/TD]
[TD="align: right"]4[/TD]
[TD]South Carolina[/TD]
[TD]SC[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]KS[/TD]
[TD="align: right"]3[/TD]
[TD]South Dakota[/TD]
[TD]SD[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Kentucky[/TD]
[TD]KY[/TD]
[TD="align: right"]0[/TD]
[TD]Tennessee[/TD]
[TD]TN[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Louisiana[/TD]
[TD]LA[/TD]
[TD="align: right"]1[/TD]
[TD]Texas[/TD]
[TD]TX[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Maine[/TD]
[TD]ME[/TD]
[TD="align: right"]2[/TD]
[TD]Utah[/TD]
[TD]UT[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Maryland[/TD]
[TD]MD[/TD]
[TD="align: right"]0[/TD]
[TD]Vermont[/TD]
[TD]VT[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Massachusetts[/TD]
[TD]MA[/TD]
[TD="align: right"]3[/TD]
[TD]Virginia[/TD]
[TD]VA[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Michigan[/TD]
[TD]MI[/TD]
[TD="align: right"]1[/TD]
[TD]Washington[/TD]
[TD]WA[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Minnesota[/TD]
[TD]MN[/TD]
[TD="align: right"]4[/TD]
[TD]West Virginia[/TD]
[TD]WV[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Mississippi[/TD]
[TD]MS[/TD]
[TD="align: right"]0[/TD]
[TD]Wisconsin[/TD]
[TD]WI[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Missouri[/TD]
[TD]MO[/TD]
[TD="align: right"]1[/TD]
[TD]Wyoming[/TD]
[TD]WY[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.