Finding value in a row

M

MRN227

New Member
Joined
Dec 18, 2018
Messages
15
My customer sends a report that is about 5000+ rows of data. This report consistently changes format. I need to extract data regardless of the position of where the data is in the row. For example:

I need to lookup the customer order number based on the criteria of the order number beginning with "D" in a row. See below:


<colgroup><col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:7936;width:163pt" width="217"> <col style="mso-width-source:userset;mso-width-alt:4937;width:101pt" width="135"> <col style="mso-width-source:userset;mso-width-alt:1572;width:32pt" width="43"> <col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" width="40" span="2"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:5412;width:111pt" width="148"> <col style="mso-width-source:userset;mso-width-alt:4242;width:87pt" width="116"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:3949;width:81pt" width="108"> <col style="mso-width-source:userset;mso-width-alt:4973;width:102pt" width="136"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:1901;width:39pt" width="52"> <col style="mso-width-source:userset;mso-width-alt:4352;width:89pt" width="119"> <col style="mso-width-source:userset;mso-width-alt:1718;width:35pt" width="47"> <col style="mso-width-source:userset;mso-width-alt:4790;width:98pt" width="131"> <col style="mso-width-source:userset;mso-width-alt:5485;width:113pt" width="150"> <col style="mso-width-source:userset;mso-width-alt:2340;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4096;width:84pt" width="112"> <col style="mso-width-source:userset;mso-width-alt:5083;width:104pt" width="139"> <col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="mso-width-source:userset;mso-width-alt:3876; width:80pt" width="106" span="2"> <col style="mso-width-source:userset;mso-width-alt:3913; width:80pt" width="107" span="2"> <col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> <col style="mso-width-source:userset;mso-width-alt:4205;width:86pt" width="115"> <col style="mso-width-source:userset;mso-width-alt:3657; width:75pt" width="100" span="4"> <col style="mso-width-source:userset;mso-width-alt:5193;width:107pt" width="142"> <col style="mso-width-source:userset;mso-width-alt:4242;width:87pt" width="116"> <col style="mso-width-source:userset;mso-width-alt:3693;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> <col style="mso-width-source:userset;mso-width-alt:5193;width:107pt" width="142"> <col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:6034;width:124pt" width="165"> <col style="mso-width-source:userset;mso-width-alt:5046;width:104pt" width="138"> <col style="mso-width-source:userset;mso-width-alt:6326;width:130pt" width="173"> <col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="mso-width-source:userset;mso-width-alt:5229;width:107pt" width="143"> <col style="mso-width-source:userset;mso-width-alt:3547;width:73pt" width="97"> <col style="mso-width-source:userset;mso-width-alt:2377;width:49pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:5961;width:122pt" width="163"> <col style="mso-width-source:userset;mso-width-alt:4937;width:101pt" width="135"> <col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> <col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="mso-width-source:userset;mso-width-alt:2413;width:50pt" width="66"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> </colgroup><tbody>
[TD="class: xl65, width: 102"]Order begins with
[/TD]
[TD="class: xl65, width: 81"] Myformula
[/TD]
[TD="class: xl66, width: 217"]number_of_shipments[/TD]
[TD="class: xl65, width: 135"]load_id[/TD]
[TD="class: xl65, width: 43"]is_3pl_vrid_compliant[/TD]
[TD="class: xl65, width: 40"]adhoc_load[/TD]
[TD="class: xl65, width: 40"]late_adhoc_5hr[/TD]
[TD="class: xl65, width: 82"]late_adhoc_4hr[/TD]
[TD="class: xl65, width: 94"]canceled_load[/TD]
[TD="class: xl65, width: 148"]fulfillment_shipment_id[/TD]
[TD="class: xl65, width: 116"]shipper_id[/TD]
[TD="class: xl65, width: 93"]warehouse_id[/TD]
[TD="class: xl65, width: 75"]destination[/TD]
[TD="class: xl65, width: 108"]warehouse_type[/TD]
[TD="class: xl65, width: 136"]lane[/TD]
[TD="class: xl65, width: 88"]stop_number[/TD]
[TD="class: xl65, width: 52"]is_amzl[/TD]
[TD="class: xl67, width: 119"]had_rejected_load[/TD]
[TD="class: xl65, width: 47"]carrier[/TD]
[TD="class: xl65, width: 131"]carrier_zone[/TD]
[TD="class: xl65, width: 150"]ship_method[/TD]
[TD="class: xl67, width: 64"]late_slam[/TD]
[TD="class: xl65, width: 112"]shipped_1hr_late[/TD]
[TD="class: xl65, width: 139"]shipped_on_time_1hr[/TD]
[TD="class: xl65, width: 114"]ship_daytime_pst[/TD]
[TD="class: xl65, width: 88"]ship_day_pst[/TD]
[TD="class: xl65, width: 71"]ship_week[/TD]
[TD="class: xl65, width: 79"]ship_month[/TD]
[TD="class: xl65, width: 106"]original_cpt_pst[/TD]
[TD="class: xl65, width: 106"]original_cpt_utc[/TD]
[TD="class: xl65, width: 107"]cpt_at_slam_pst[/TD]
[TD="class: xl65, width: 107"]cpt_at_slam_utc[/TD]
[TD="class: xl65, width: 100"]cpt_utc[/TD]
[TD="class: xl65, width: 114"]manifest_run_pst[/TD]
[TD="class: xl65, width: 115"]asn_datetime_pst[/TD]
[TD="class: xl65, width: 100"]exsd_pst[/TD]
[TD="class: xl65, width: 100"]exsd_utc[/TD]
[TD="class: xl65, width: 100"]fc_slam_pst[/TD]
[TD="class: xl65, width: 100"]fc_slam_utc[/TD]
[TD="class: xl65, width: 142"]scheduled_arrival_utc[/TD]
[TD="class: xl65, width: 116"]actual_arrival_utc[/TD]
[TD="class: xl65, width: 101"]on_time_arrival[/TD]
[TD="class: xl65, width: 114"]loading_start_utc[/TD]
[TD="class: xl65, width: 142"]loading_complete_utc[/TD]
[TD="class: xl65, width: 103"]on_time_loaded[/TD]
[TD="class: xl65, width: 165"]scheduled_departure_utc[/TD]
[TD="class: xl65, width: 138"]actual_departure_utc[/TD]
[TD="class: xl65, width: 173"]on_time_shipped_load_1hr[/TD]
[TD="class: xl65, width: 123"]root_cause_1hr[/TD]
[TD="class: xl65, width: 143"]root_subcause_1hr[/TD]
[TD="class: xl65, width: 97"]fc_carrier_1hr[/TD]
[TD="class: xl65, width: 65"]prior_day[/TD]
[TD="class: xl65, width: 163"]cpt_at_slam_datetime_az[/TD]
[TD="class: xl65, width: 135"]cpt_at_slam_time_az[/TD]
[TD="class: xl65, width: 114"]ship_datetime_az[/TD]
[TD="class: xl65, width: 86"]ship_time_az[/TD]
[TD="class: xl65, width: 66"]ship_shift[/TD]
[TD="class: xl65, width: 130"]expected_ship_shift[/TD]

[TD="class: xl68"]D
[/TD]
[TD="class: xl65"]Hlookup?[/TD]
[TD="class: xl65, align: right"]1
[/TD]
[TD="class: xl65"]114SZPVP7[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"]Scheduled[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl67"]DfL4rHJKn[/TD]
[TD="class: xl65"]TBA002810918000[/TD]
[TD="class: xl69"]XUSP[/TD]
[TD="class: xl65"]HCH1[/TD]
[TD="class: xl65"]3PL[/TD]
[TD="class: xl65"]XUSP->HCH1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl70"]XPOL[/TD]
[TD="class: xl70"]HCH1[/TD]
[TD="class: xl70"]AMZL_US_SH[/TD]
[TD="class: xl67, align: right"]0[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl70, align: right"]4/3/2019 16:03[/TD]
[TD="class: xl69, align: right"]4/3/2019[/TD]
[TD="class: xl65"]2019-14[/TD]
[TD="class: xl70"]2019-4[/TD]
[TD="class: xl70, align: right"]4/3/2019 11:00[/TD]
[TD="class: xl70, align: right"]4/3/2019 18:00[/TD]
[TD="class: xl70, align: right"]4/3/2019 15:00[/TD]
[TD="class: xl70, align: right"]4/3/2019 22:00[/TD]
[TD="class: xl70, align: right"]4/3/2019 22:00[/TD]
[TD="class: xl70, align: right"]4/3/2019 15:58[/TD]
[TD="class: xl70, align: right"]4/3/2019 16:03[/TD]
[TD="class: xl70, align: right"]4/3/2019 11:00[/TD]
[TD="class: xl70, align: right"]4/3/2019 18:00[/TD]
[TD="class: xl70, align: right"]4/3/2019 8:07[/TD]
[TD="class: xl70, align: right"]4/3/2019 15:07[/TD]
[TD="class: xl70, align: right"]4/3/2019 22:00[/TD]
[TD="class: xl70, align: right"]4/3/2019 22:52[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl70, align: right"]4/3/2019 22:58[/TD]
[TD="class: xl65, align: right"]0[/TD]
[TD="class: xl70, align: right"]4/3/2019 22:00[/TD]
[TD="class: xl70, align: right"]4/3/2019 23:00[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"]LATE_DEPARTURE[/TD]
[TD="class: xl65"]LATE_MANIFEST[/TD]
[TD="class: xl65"]FC[/TD]
[TD="class: xl70, align: center"]FALSE[/TD]
[TD="class: xl70, align: right"]4/3/2019 15:00[/TD]
[TD="class: xl71, align: right"]8:00[/TD]
[TD="class: xl70, align: right"]4/3/2019 16:03[/TD]
[TD="class: xl71, align: right"]16:00[/TD]
[TD="class: xl65"]SHIFT_2[/TD]
[TD="class: xl65"]SHIFT_1[/TD]

</tbody>

Please help. I'm racking my brain trying to get this figured out. My plan is, to produce the value (order#) then use vlookup based on that value... I hope I'm making sense.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Re: Need help finding value in a row

Thank you Brombrough! One quick, dumb, question. Within the macro, would you happen to know where I define the header? Im fairly new to VBA.
 
Upvote 0
Re: Need help finding value in a row

Hi MRN227,

The headers are predefined before macro runs.

Sheet1 is the format of the sheet that comes from the customer.

Sheet2 is the same headers but in the order you want to see them.

Rules.

1) Make sure that column "A" is the same on both sheets
2) Make sure same headers are on both sheets
3) Best before running macro you should delete clear all cells in range "B2:{Last Cell in data} on sheet2. (Just keep headers and Column "A")

Hope this helps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
J
  • Question Question
pulling data from one chart to another
Replies
1
Views
808
jb00976
J
O
  • Question Question
Survey data to capture full month
Replies
5
Views
876
DanteAmor
DanteAmor
C
  • Question Question
Sum
Replies
3
Views
1K
pbornemeier
pbornemeier
T
  • Question Question
VBA to launch userform by selecting a cell
2
Replies
10
Views
2K
Logit
Logit

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

Share this page

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
Back
Top