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:

Order begins with

Myformula

number_of_shipments

load_id

is_3pl_vrid_compliant

adhoc_load

late_adhoc_5hr

late_adhoc_4hr

canceled_load

fulfillment_shipment_id

shipper_id

warehouse_id

destination

warehouse_type

lane

stop_number

is_amzl

had_rejected_load

carrier

carrier_zone

ship_method

late_slam

shipped_1hr_late

shipped_on_time_1hr

ship_daytime_pst

ship_day_pst

ship_week

ship_month

original_cpt_pst

original_cpt_utc

cpt_at_slam_pst

cpt_at_slam_utc

cpt_utc

manifest_run_pst

asn_datetime_pst

exsd_pst

exsd_utc

fc_slam_pst

fc_slam_utc

scheduled_arrival_utc

actual_arrival_utc

on_time_arrival

loading_start_utc

loading_complete_utc

on_time_loaded

scheduled_departure_utc

actual_departure_utc

on_time_shipped_load_1hr

root_cause_1hr

root_subcause_1hr

fc_carrier_1hr

prior_day

cpt_at_slam_datetime_az

cpt_at_slam_time_az

ship_datetime_az

ship_time_az

ship_shift

expected_ship_shift

D

Hlookup?

1

114SZPVP7

1

Scheduled

0

0

FALSE

DfL4rHJKn

TBA002810918000

XUSP

HCH1

3PL

XUSP->HCH1

1

1

0

XPOL

HCH1

AMZL_US_SH

0

1

0

4/3/2019 16:03

4/3/2019

2019-14

2019-4

4/3/2019 11:00

4/3/2019 18:00

4/3/2019 15:00

4/3/2019 22:00

4/3/2019 22:00

4/3/2019 15:58

4/3/2019 16:03

4/3/2019 11:00

4/3/2019 18:00

4/3/2019 8:07

4/3/2019 15:07

4/3/2019 22:00

4/3/2019 22:52

0

4/3/2019 22:58

0

4/3/2019 22:00

4/3/2019 23:00

1

LATE_DEPARTURE

LATE_MANIFEST

FC

FALSE

4/3/2019 15:00

8:00

4/3/2019 16:03

16:00

SHIFT_2

SHIFT_1

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