Finding value in a row

[MRN227]

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.

 

[Brombrough]

Re: Need help finding value in a row

Hi MRN227,

Please take a look at the following link. Written today and you could modify same to create your own ordered columns

https://www.mrexcel.com/forum/excel...a-one-sheet-another-when-header-matching.html

Hope this helps and enjoy if it does.

Please don't forget to Thank /Like if it does

 

[MRN227]

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.

 

[Brombrough]

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.

 

[MRN227]

Re: Need help finding value in a row

That did Brombrough. Thank you for the help!