Looking Up Values on One Workbook and Inserting into Another

FrenchCelt

Board Regular
Joined
May 22, 2018
Messages
141
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm trying to code a macro that will cross reference a value between two workbooks to return another value. For example, on the originating workbook:

Shipment IDStoreCut TimeDestination CitySTOrder IDCP/XD IndicatorLBSCubeLinesLoadCarrierShip LaneWave
22217811777TU9AMEAST TAWASMI843804363050321471096WVAS
22217811777TU9AMEAST TAWASMI8438043831222192WVAS
22217811777TU9AMEAST TAWASMI84366929Central Pick1641081WVAS
2221781 Total337892376
2221782646TU10AMWEST BRANCHMI843811173798621361485WVAS
2221782646TU10AMWEST BRANCHMI84367330Central Pick19317111WVAS
2221782 Total381802154
22217911295TU7PMBEAVER FALLSPA11403555Cross Dock300281USXI
22217911295TU7PMBEAVER FALLSPA84367329Central Pick19416105USXI
22217911295TU7PMBEAVER FALLSPA11403591Cross Dock1000491USXI
22217911295TU7PMBEAVER FALLSPA843804434021121381197USXI
2221791 Total417042231

I want to look up the Ship Lane and Wave from another workbook:

HOME DOOR232231230229228227226225224223222221220219218217216215214213212211WAVE 1
RP 6 SH #2221814222179422218162221804222180922218152221797222178822217852221799USXI
PICK WAVE #WVAS.USXI.WVAS.RVTS.WVAS.USXI.WVAS.WVAS.RVTS
O#WVAS
E# 2021060500164STOP3STOP4STOP4STOP4STOPSTOP 2
LINES / GRABSSTOP 3
ODD-13163319946425706355702448131639STOP 4
EVEN- 6717 / 12259Total
XDXDXDXDXDXD
WEIGHT46212885919836344609652183693473221859323085134ODD
CUBE28822291098217210931197223321962180574EVEN
LINES4142978110861453130987011638Total
CUT TIMETU11PM0TU10PM0TU9PM0TU23590TU10PM000TU11PM0TU9PM0TU10PM0TU8PM0TU10PM0
HOME DOOR232231230229228227226225224223222221220219218217216215214213212211WAVE 2
RP 6 SH #222181422218002221798222181722218162221811222178722218092221781222179022217842221815222181322217802221782222177922218102221808222180322217992221812USXI
PICK WAVE #WVASWVAS.WVASWVAS.WVAS.RVTSWVAS.WVAS.USXI.WVASUSXI.RVTS
O#WVAS
E#4STOP3STOP3STOP4STOPSTOP 2
LINES / GRABSSTOP 3
ODD-64058435111052067646226324481777639237518002067519646226767027312072432441STOP 4
EVEN-3STOP2STOP3STOP3STOP3STOPTotal
XDXDXDXDXDXDXDXDXDXDXDXDXDXD
WEIGHT16312926129004384674550889138000381833789345722953076453799531646381803208411255403714197479529763ODD
CUBE1275624234723014497612333213237621992311442215121592154215739722412125261603EVEN
LINES1497169386129624109812147495818106914851370813781549534Total
CUT TIMETU11PMTU2359TU8PMTU2359TU9PMTU9AMTU9PM0TU10PMTU9AMTU8PMTU9PMTU11PMTU9PMTU11PMTU10AMTU9AMTU11PMTU11PMTU8AMTU10PMTU8AM

The end result should look like this:

Shipment IDStoreCut TimeDestination CitySTOrder IDCP/XD IndicatorLBSCubeLinesLoadCarrierShip LaneWave
22217811777TU9AMEAST TAWASMI843804363050321471096WVAS
22217811777TU9AMEAST TAWASMI8438043831222192WVAS
22217811777TU9AMEAST TAWASMI84366929Central Pick1641081WVAS
2221781 Total3378923762232
2221782646TU10AMWEST BRANCHMI843811173798621361485WVAS
2221782646TU10AMWEST BRANCHMI84367330Central Pick19317111WVAS
2221782 Total3818021542172
22217911295TU7PMBEAVER FALLSPA11403555Cross Dock300281USXI
22217911295TU7PMBEAVER FALLSPA84367329Central Pick19416105USXI
22217911295TU7PMBEAVER FALLSPA11403591Cross Dock1000491USXI
22217911295TU7PMBEAVER FALLSPA843804434021121381197USXI
2221791 Total4170422312274

The Ship Lane and Wave # can be found in the row of HOME DOOR numbers:

HOME DOOR232231230229228227226225224223222221220219218217216215214213212211WAVE 1

Further complicating this task is the presence of what we call double and triple loads. Here is an example of a triple with the Ship Lane and Wave already assigned:

2221814131TU8PMHOUGHTON LAKEMI8438067646212884NoseWVAS1
2221814640TU9PMMOUNT PLEASANTMI11403581Cross Dock2100611MiddleWVAS2
2221814640TU9PMMOUNT PLEASANTMI8438045213060110714MiddleWVAS
2221814640TU9PMMOUNT PLEASANTMI11403577Cross Dock300281MiddleWVAS
2221814640TU9PMMOUNT PLEASANTMI11403590Cross Dock300281MiddleWVAS
2221814640TU9PMMOUNT PLEASANTMI84367510Central Pick2512396MiddleWVAS
2221814640TU9PMMOUNT PLEASANTMI11403582Cross Dock300281MiddleWVAS
2221814273TU11PMGREENVILLEMI11403580Cross Dock2100611TailWVAS3
2221814273TU11PMGREENVILLEMI8438111485996077TailWVAS
2221814 Total316312231232

Each wave number goes to the first instance of the Nose, Middle, and Tail values because the triple load is assigned to three different waves.

I've asked for help with complex macros before, but this one seems beyond the pale, so I wouldn't blame anyone if they just bypassed it. :)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,141,072
Messages
5,704,129
Members
421,328
Latest member
CBL

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
Top