Using indirect with result of lookup value from index/match for another index/match!

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
155
Hi all,

Think I'm even going to struggle to explain this one!

I pull some data from a couple of databases using SQL, however, the SQL commands available to the end user are limited, so I must do a large part of the work in excel.

Certain products have different days "validation" before they need consumed, this is static based on the product sub group, however, not recorded in the SQL databases.

e.g.
Ingredients: 1 day
Frozen crated product: 3 days
Frozen boxed product: 5 days
etc...

These are stored in a small table in my excel workbook (the table will be much larger when final)

odbcs 070720.xlsm
DEFGH
1CodeDescriptionValidation TypeTimestamp ColumnValidation Days
2S58Veg 2 - 17780001 Veg BlendsA1
3S57Veg 1 - 17760001 Veg BlendsA1
48914NZ LAMB SKIRTS TEMPERING NZ TemperingAN5
5B880595VL OTM STEAK TRIM FROZEN Frozen CratedAN3
6L8810UK LAMB SKIRTS FROZEN Frozen CratedAN3
78912NZ 75CL TEMPERING NZ TemperingAN5
8B884080VL OTM VALUE TRIM FROZEN Frozen CratedAN3
9B887495VL FROZEN Frozen CratedAN3
10B876495VL ROI STEAK TRIM FROZEN UTM Frozen CratedAN3
11B837498VL UTM IRISH TRIM FROZEN Frozen CratedAN3
12B8767ROI 80VL OTM TRIM FROZEN Frozen CratedAN3
Lookups



I need to lookup up the product code in one of my SQL query result columns against this table to return the column address (A/AN) and then use that value as the indexed column in an index/match formula - in one formula!

e.g

for S58

=INDEX(IMV!A:A,MATCH(A2,IMV!U:U,0))

but for B8805 I would want

=INDEX(IMV!AN:AN,MATCH(A2,IMV!U:U,0))

this value coming from the above indirect/lookup

In short, column O in the below needs to lookup "creation date" for some products and "last modified date" for others, however these are stored in different fields (obviously!) in the SQL db, I need Excel to know which column to look at, by referencing the first table which will be user-maintainable.

odbcs 070720.xlsm
ABCDEFGHIJKLMNOPQRS
1pallet_numberstock_areastock_rackstock_locationmodified_timeTotal_boxesTotal_itemsTotal_box_label_weigMax_lifeProductDescriptionAnalysis_Code2LocationLocation2Orig_TxConsumption Date
2126272A110627110151212120738747S58Veg 2 - 17780001 FROZEN 2/A/1A-B Link Tunnel04/06/2020 10:2407/06/2020 10:2404/6/2020 10:33
3161892A110627118083360.87738229L8810UK LAMB SKIRTS FROZEN FROZEN 2/A/1A-B Link Tunnel22/04/2020 12:0425/04/2020 12:04
4162122A11062706670551367385948912NZ 75CL TEMPERING FROZEN 2/A/1A-B Link Tunnel03/07/2020 15:3506/07/2020 15:35
Union


I really hope that this makes some sense!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,539
Office Version
  1. 2016
Platform
  1. Windows
Hi PureBluff,

There's some sheets missing from your provided data so just let me give a similar example of using INDIRECT.

Here's my test data in the IMV tab:

Book1
ABTUVAMAN
1This is A1DogThis is AN1
2This is A2CatThis is AN2
3This is A3PigThis is AN3
4This is A4MoleThis is AN4
5This is A5GoatThis is AN5
6This is A6SheepThis is AN6
7This is A7CowThis is AN7
8This is A8HorseThis is AN8
9This is A9DeerThis is AN9
10This is A10BadgerThis is AN10
IMV


Here's my INDIRECT statement driven by the contents of the Timestamp column G:

Book1
FGHI
1AnimalTimestampResult
2PigAThis is A3
3GoatAThis is A5
4MoleANThis is AN4
5GoatANThis is AN5
Work
Cell Formulas
RangeFormula
I2:I5I2=INDEX(INDIRECT("IMV!"&G2&":"&G2),MATCH(F2,IMV!U:U,0))
 

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
155
Thanks toadstool, will log onto the remote server and have a look.

I didn't post the IMV sheet as it's huge & largely redundant data, coupled with poor excel formatting (I'm pulling all columns in the db so I have a reference point until I have the result set working) - then I'll reduce the SQL select from "select *" to only the ones I need.
 

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
155
Hi @Toadstool , while the formula works as you suggested, it doesn't work for the additional complexity that I am struggling with; I need to lookup within a lookup for my intended result. I have placed the "looks" data inside the "Union" sheet, so there's less sheets to contend with.

odbcs140720.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
1Ins_timestampIdProd_codeFrom_statusFrom_ord_typeFrom_ord_noFrom_ord_lineFrom_siteFrom_locnFrom_pal_serFrom_pal_noFrom_batchFrom_docTo_statusTo_ord_typeTo_ord_noTo_ord_lineTo_siteTo_locnTo_pal_serTo_pal_noTo_batchTo_docStk_adj_factorMove_datetimeTerminal_portTerminal_IPTerminal_noTrans_serial_noUser_codeTeam_sizeCurr_boxesCurr_act_measureCurr_label_kgCurr_act_kgSourceVer_noExportStatusExtra_detailTrans_datetimeUnit_of_measureTareLabel_detail_idLocal_idLocal_siteDateTypeLocationSiteitemsWeight
204/06/2020 10:33890106789S58PORDER23008S58000STOCK00S5803/A/0010126250GRN110575104/06/2020 10:33FOPS6-UC/000974398060484804804807.888004/06/2020 10:22kg95.0416340795504/06/2020 10:00AcceptedFromPO300848.00480.00
304/06/2020 11:10890119469S58STOCK00S5803/A/0014544798126250STOCK00S5808/A/0044544798126250004/06/2020 11:10FOPS6-UC/000974398060484804804807.888004/06/2020 10:22kg95.0416340795504/06/2020 11:00StockMovement-48.00480.00
405/07/2020 03:49902333190L8897STOCK00L889700/ /000000STOCK00L889708/A/0154574416163650005/07/2020 03:49FOPS6-UC/00097458129JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568317905/07/2020 03:00StockMovement-1.0027.20
505/07/2020 03:49902333192L8897STOCK00L889700/ /000000STOCK00L889708/A/0154574416163650005/07/2020 03:49FOPS6-UC/00097458130JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568318005/07/2020 03:00StockMovement-1.0027.20
605/07/2020 03:52902333398L8897STOCK00L889708/A/0154574416163650STOCK00L889708/A/0084574416163650005/07/2020 03:52FOPS6-UC/00097458129JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568317905/07/2020 03:00StockMovement-1.0027.20
705/07/2020 03:52902333399L8897STOCK00L889708/A/0154574416163650STOCK00L889708/A/0084574416163650005/07/2020 03:52FOPS6-UC/00097458130JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568318005/07/2020 03:00StockMovement-1.0027.20
805/07/2020 03:52902333415L8897STOCK00L889708/A/0154574416163650STOCK00L889708/A/0084574416163650005/07/2020 03:52FOPS6-UC/00097458146JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568319605/07/2020 03:00StockMovement-1.0027.20
905/07/2020 03:52902333416L8897STOCK00L889708/A/0154574416163650STOCK00L889708/A/0084574416163650005/07/2020 03:52FOPS6-UC/00097458147JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568319705/07/2020 03:00StockMovement-1.0027.20
1005/07/2020 03:52902333417L8897STOCK00L889708/A/0154574416163650STOCK00L889708/A/0084574416163650005/07/2020 03:52FOPS6-UC/00097458148JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568319805/07/2020 03:00StockMovement-1.0027.20
1105/07/2020 03:52902333418L8897STOCK00L889708/A/0154574416163650STOCK00L889708/A/0084574416163650005/07/2020 03:52FOPS6-UC/00097458149JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568319905/07/2020 03:00StockMovement-1.0027.20
1206/07/2020 04:059026840608912STOCK0999997891200/ /00045753022030STOCK00891299/A/0010164670006/07/2020 04:05FOPS6-UC/00090624257KASPARS0127.3627.227.367.888006/07/2020 03:45kg0.616574796306/07/2020 04:00AssignedToDO999997-1.00-27.36
1306/07/2020 04:059026840618912STOCK0999997891200/ /00045753022030STOCK00891299/A/0010164670006/07/2020 04:05FOPS6-UC/00090624258KASPARS0127.3627.227.367.888006/07/2020 03:45kg0.616574796406/07/2020 04:00AssignedToDO999997-1.00-27.36
1410/07/2020 23:06904580928B8805PORDER2149277B8805000STOCK00B880506/A/0010169910GRN111186110/07/2020 23:06FOPS6-UC/000100495778010118.4818.4818.4805128805240620310521190620001848897.888010/07/2020 23:03kg1.5816611104710/07/2020 23:00AcceptedFromPO1492771.0018.48
1510/07/2020 23:06904580939B8805PORDER2149277B8805000STOCK00B880506/A/0010169910GRN111186110/07/2020 23:06FOPS6-UC/000100495778120118.9418.9418.9405128805240620310521190620001894897.888010/07/2020 23:03kg1.5816611105810/07/2020 23:00AcceptedFromPO1492771.0018.94
1610/07/2020 23:50904595614B8805STOCK00B880506/A/0014579752169910STOCK00B880503/A/0014579752169910010/07/2020 23:50FOPS6-UC/000100495777680118.6818.6818.687.888010/07/2020 23:02kg1.5816611101310/07/2020 23:00StockMovement-1.0018.68
1710/07/2020 23:50904595615B8805STOCK00B880506/A/0014579752169910STOCK00B880503/A/0014579752169910010/07/2020 23:50FOPS6-UC/000100495777690119.4419.4419.447.888010/07/2020 23:02kg1.5816611101410/07/2020 23:00StockMovement-1.0019.44
1810/07/2020 23:50904595616B8805STOCK00B880506/A/0014579752169910STOCK00B880503/A/0014579752169910010/07/2020 23:50FOPS6-UC/000100495777700118.818.818.87.888010/07/2020 23:02kg1.5816611101510/07/2020 23:00StockMovement-1.0018.80
1910/07/2020 23:50904595617B8805STOCK00B880506/A/0014579752169910STOCK00B880503/A/0014579752169910010/07/2020 23:50FOPS6-UC/000100495777710118.4918.4918.497.888010/07/2020 23:02kg1.5816611101610/07/2020 23:00StockMovement-1.0018.49
2010/07/2020 23:50904595658B8805STOCK00B880506/A/0014579752169910STOCK00B880503/A/0014579752169910010/07/2020 23:50FOPS6-UC/000100495778120118.9418.9418.947.888010/07/2020 23:03kg1.5816611105810/07/2020 23:00StockMovement-1.0018.94
2111/07/2020 03:10904647839B8764PORDER2149270B8764000STOCK00B876406/A/0010170170GRN111200111/07/2020 03:10FOPS6-UC/000100495799010118.4618.4618.4605528764220520220521190520001846607.888011/07/2020 03:07kg1.5616611891211/07/2020 03:00AcceptedFromPO1492701.0018.46
2211/07/2020 03:10904647840B8764PORDER2149270B8764000STOCK00B876406/A/0010170170GRN111200111/07/2020 03:10FOPS6-UC/000100495799020118.3418.3418.3405528764250520250521210520001834607.888011/07/2020 03:07kg1.5616611891311/07/2020 03:00AcceptedFromPO1492701.0018.34
2311/07/2020 03:10904647841B8764PORDER2149270B8764000STOCK00B876406/A/0010170170GRN111200111/07/2020 03:10FOPS6-UC/000100495799030117.9617.9617.9605528764250520250521210520001796607.888011/07/2020 03:07kg1.5616611891411/07/2020 03:00AcceptedFromPO1492701.0017.96
2411/07/2020 03:57904663073B8764STOCK00B876403/A/0014579917170170STOCK00B876402/A/0014579917170170011/07/2020 03:57FOPS6-UA/000100495799100118.6618.6618.667.888011/07/2020 03:07kg1.5616611892111/07/2020 03:00StockMovement-1.0018.66
2511/07/2020 03:57904663074B8764STOCK00B876403/A/0014579917170170STOCK00B876402/A/0014579917170170011/07/2020 03:57FOPS6-UA/000100495799110118.0218.0218.027.888011/07/2020 03:07kg1.5616611892211/07/2020 03:00StockMovement-1.0018.02
2611/07/2020 03:57904663075B8764STOCK00B876403/A/0014579917170170STOCK00B876402/A/0014579917170170011/07/2020 03:57FOPS6-UA/000100495799120118.1818.1818.187.888011/07/2020 03:07kg1.5616611892311/07/2020 03:00StockMovement-1.0018.18
2711/07/2020 03:57904663076B8764STOCK00B876403/A/0014579917170170STOCK00B876402/A/0014579917170170011/07/2020 03:57FOPS6-UA/000100495799130118.9818.9818.987.888011/07/2020 03:07kg1.5616611892411/07/2020 03:00StockMovement-1.0018.98
2811/07/2020 03:57904663077B8764STOCK00B876403/A/0014579917170170STOCK00B876402/A/0014579917170170011/07/2020 03:57FOPS6-UA/000100495799140117.7817.7817.787.888011/07/2020 03:07kg1.5616611892511/07/2020 03:00StockMovement-1.0017.78
IMV


odbcs140720.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1pallet_numberstock_areastock_rackstock_locationmodified_timeTotal_boxesTotal_itemsTotal_box_label_weigMax_lifeProductDescriptionAnalysis_Code2LocationLocation2Orig_TxConsumption Date
2126253A110627196552020200738747S58Veg 2 - 17780001 FROZEN 3/A/1Behind Retail Cranes04/06/2020 10:2209/06/2020 10:22
3163652A110627137013535952738584L8897NZ LAMB FROZEN 75CL FROZEN 2/A/1A-B Link Tunnel05/07/2020 03:4710/07/2020 03:47Lookup data
4164672A110627125493381.67385948912NZ 75CL TEMPERING FROZEN 2/A/1A-B Link Tunnel06/07/2020 03:4511/07/2020 03:45CodeDescriptionValidation TypeIMV ColumnValidation Days
5169912A110627159121313246.31738321B880595VL OTM STEAK TRIM FROZEN FROZEN 2/A/1A-B Link Tunnel10/07/2020 23:0315/07/2020 23:03S58Veg 2 - 17780001 Veg BlendsA1
6170172A110627159175592.981B876495VL ROI STEAK TRIM FROZEN UTM FROZEN 2/A/1A-B Link Tunnel11/07/2020 03:0716/07/2020 03:07S57Veg 1 - 17760001 Veg BlendsA1
7170532A110627168742020368.54738319B880595VL OTM STEAK TRIM FROZEN FROZEN 2/A/1A-B Link Tunnel11/07/2020 15:4116/07/2020 15:418914NZ LAMB SKIRTS TEMPERING NZ TemperingAN5
81709617F710627176113535673.341B837498VL UTM IRISH TRIM FROZEN FROZEN 17/F/7UF Floor11/07/2020 10:3816/07/2020 10:38B880595VL OTM STEAK TRIM FROZEN Frozen BeefAN3
91709717F710627176113838714.821B837498VL UTM IRISH TRIM FROZEN FROZEN 17/F/7UF Floor10/07/2020 22:0315/07/2020 22:03L8110UK LAMB SKIRTS FROZEN Frozen LambAN3
101709917F710627176113535699.541B837498VL UTM IRISH TRIM FROZEN FROZEN 17/F/7UF Floor11/07/2020 02:0116/07/2020 02:018912NZ 75CL TEMPERING NZ TemperingAN5
111710117F710627176113535714.261B837498VL UTM IRISH TRIM FROZEN FROZEN 17/F/7UF Floor11/07/2020 02:0016/07/2020 02:00B884080VL OTM VALUE TRIM FROZEN Frozen BeefG3
121710517F710627177464444821.04738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:1317/07/2020 08:13B8373FINEST 95VL FROZEN Frozen BeefAN3#REF!
131710717F710627177464545843.34738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:2417/07/2020 08:24B876495VL ROI STEAK TRIM FROZEN UTM Frozen BeefAN3#REF!
141710817F710627177464545842.08738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:2917/07/2020 08:29B837498VL UTM IRISH TRIM FROZEN Frozen BeefAN3
151710917F710627177464545842.94738004L8810UK LAMB SKIRTS FROZEN FROZEN 17/F/7UF Floor12/07/2020 08:3017/07/2020 08:30L8806ROI 80VL OTM TRIM FROZEN Frozen BeefAN3
161711017F710627177464545843.16738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:3517/07/2020 08:35
171711217F710627177464545838.78738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:4017/07/2020 08:40
181711317F710627177464545847.78738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:4217/07/2020 08:420
19171833A110627190284242572.16738009L8110NZ TB LEG 3/A/1Behind Retail Cranes13/07/2020 07:4218/07/2020 07:4216365
20171853A110627190354242568.26738010L8110NZ TB LEG 3/A/1Behind Retail Cranes13/07/2020 07:5218/07/2020 07:52
21171873A110627190531919361.38738010L8806NZ LEAN LAMB LOINS 10 PER PACK 3/A/1Behind Retail Cranes13/07/2020 08:1118/07/2020 08:11AN
Union
Cell Formulas
RangeFormula
K2:K21K2=INDEX(Primal!C:C,MATCH([@Product],Primal!B:B,0))
L2:L21L2=INDEX(Primal!BT:BT,MATCH([@Product],Primal!B:B,0))
M2:M21M2=CONCATENATE([@[stock_area]],"/",[@[stock_rack]],"/",[@[stock_location]])
N2:N21N2=INDEX(Lookups!$B$2:$B$30,MATCH([@Location],Lookups!$A$2:$A$30,0))
O2:O21O2=INDEX(IMV!AN:AN,MATCH([@[pallet_number]],IMV!U:U,0))
P2:P21P2=[@[Orig_Tx]]+5
W12W12=INDEX(INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)),MATCH(Table_Query_from_FOPS6ODBC[@[pallet_number]],IMV!U:U,0))
W13W13=INDEX(INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)&":"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)),MATCH(Table_Query_from_FOPS6ODBC[@[pallet_number]],IMV!U:U,0))
S18S18=INDIRECT("IMV!"&ADDRESS(ROW(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0))),COLUMN(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0))),4)&":"&ADDRESS(ROW(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0))),COLUMN(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0))),4))
S19S19=INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4))
S21S21=INDIRECT(ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4))


In the yellow formatted cells you can see what I've been attempting, however I cant get the indirect to parse correctly to return the right field from "IMV"

Cell S21 returns the "AN" from a lookup against the Union Table which is correct
Cell W31 is where I've tried to add it in to an index/indirect against IMV but it fails
Cell S19 is where I tried to debug it, and it's returning what seems to be cell U9 on IMV sheet, and 9 just happens to be the row that @product is found in the lookup table to the right & the column the lookup table is asked to check against.

This suggests to me it's the indirect syntax at the wrong point, influencing the lookups that proceed it but I'm really at a loss.

Thanks
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,539
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Purebluff,

There's still at least one missing sheet and without the named ranges I can't be sure how to recreate you sheet and execute any of the formulae, so let's just try a couple of debug steps.

Take the INDIRECT part off S19 and see what string the ADDRESS gives you.

What does the S18 return if you just take COLUMN part?
=COLUMN(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0))),4)
ADDRESS just wants a column number so it may be easier to have another lookup data column so U with all the A, AN, G is followed by column V with 1,40,7.
 

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
155
Aha, my mistake, I forgot there was hidden sheets, I'll will post back with the results of your debugging suggestions within the hour & also include the additional sheets.
Apologies.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,539
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Please include the Named ranges in your XL2BB extract.
 

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
155
Ok,

IMV (This will be different to before as Im removing large parts of the transactional data / rows due to the 3000 cell limit of xl2bb)

odbcs140720.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
1Ins_timestampIdProd_codeFrom_statusFrom_ord_typeFrom_ord_noFrom_ord_lineFrom_siteFrom_locnFrom_pal_serFrom_pal_noFrom_batchFrom_docTo_statusTo_ord_typeTo_ord_noTo_ord_lineTo_siteTo_locnTo_pal_serTo_pal_noTo_batchTo_docStk_adj_factorMove_datetimeTerminal_portTerminal_IPTerminal_noTrans_serial_noUser_codeTeam_sizeCurr_boxesCurr_act_measureCurr_label_kgCurr_act_kgSourceVer_noExportStatusExtra_detailTrans_datetimeUnit_of_measureTareLabel_detail_idLocal_idLocal_siteDateTypeLocationSiteitemsWeight
204/06/2020 10:33890106789S58PORDER23008S58000STOCK00S5803/A/0010126250GRN110575104/06/2020 10:33FOPS6-UC/000974398060484804804807.888004/06/2020 10:22kg95.0416340795504/06/2020 10:00AcceptedFromPO300848480
304/06/2020 11:10890119469S58STOCK00S5803/A/0014544798126250STOCK00S5808/A/0044544798126250004/06/2020 11:10FOPS6-UC/000974398060484804804807.888004/06/2020 10:22kg95.0416340795504/06/2020 11:00StockMovement-48480
405/07/2020 03:49902333190L8897STOCK00L889700/ /000000STOCK00L889708/A/0154574416163650005/07/2020 03:49FOPS6-UC/00097458129JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568317905/07/2020 03:00StockMovement-127.2
505/07/2020 03:49902333192L8897STOCK00L889700/ /000000STOCK00L889708/A/0154574416163650005/07/2020 03:49FOPS6-UC/00097458130JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568318005/07/2020 03:00StockMovement-127.2
605/07/2020 03:52902333398L8897STOCK00L889708/A/0154574416163650STOCK00L889708/A/0084574416163650005/07/2020 03:52FOPS6-UC/00097458129JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568317905/07/2020 03:00StockMovement-127.2
705/07/2020 03:52902333399L8897STOCK00L889708/A/0154574416163650STOCK00L889708/A/0084574416163650005/07/2020 03:52FOPS6-UC/00097458130JOZEFW0127.227.227.27.888005/07/2020 03:47kg0.616568318005/07/2020 03:00StockMovement-127.2
806/07/2020 21:46902954750S58STOCK00S5808/A/0044544798126250STOCK00S5808/A/0024544798126250006/07/2020 21:46FOPS6-UC/000974398060484804804807.888004/06/2020 10:22kg95.0416340795506/07/2020 21:00StockMovement-48480
907/07/2020 01:27903006492L8897STOCK00L889708/A/0084574416163650STOCK00L889708/A/0074574416163650007/07/2020 01:27FOPS6-UC/00097458129KASPARS0127.227.227.27.888005/07/2020 03:47kg0.616568317907/07/2020 01:00StockMovement-127.2
1007/07/2020 01:27903006493L8897STOCK00L889708/A/0084574416163650STOCK00L889708/A/0074574416163650007/07/2020 01:27FOPS6-UC/00097458130KASPARS0127.227.227.27.888005/07/2020 03:47kg0.616568318007/07/2020 01:00StockMovement-127.2
1109/07/2020 15:01904035931L8897STOCK00L889708/A/0074574416163650STOCK00L889702/A/0014574416163650009/07/2020 15:01FOPS6-UC/000974581310127.227.227.27.888005/07/2020 03:47kg0.616568318109/07/2020 15:00StockMovement-127.2
1209/07/2020 15:01904035932L8897STOCK00L889708/A/0074574416163650STOCK00L889702/A/0014574416163650009/07/2020 15:01FOPS6-UC/000974581320127.227.227.27.888005/07/2020 03:47kg0.616568318209/07/2020 15:00StockMovement-127.2
1309/07/2020 15:01904035963L8897STOCK00L889708/A/0074574416163650STOCK00L889702/A/0014574416163650009/07/2020 15:01FOPS6-UC/000974581630127.227.227.27.888005/07/2020 03:47kg0.616568321309/07/2020 15:00StockMovement-127.2
1410/07/2020 23:06904580891B8805PORDER2149277B8805000STOCK00B880506/A/0010169910GRN111186110/07/2020 23:06FOPS6-UC/000100495777680118.6818.6818.6805128805240620310521190620001868897.888010/07/2020 23:02kg1.5816611101310/07/2020 23:00AcceptedFromPO149277118.68
1510/07/2020 23:06904580892B8805PORDER2149277B8805000STOCK00B880506/A/0010169910GRN111186110/07/2020 23:06FOPS6-UC/000100495777690119.4419.4419.4405128805240620310521190620001944897.888010/07/2020 23:02kg1.5816611101410/07/2020 23:00AcceptedFromPO149277119.44
1610/07/2020 23:06904580893B8805PORDER2149277B8805000STOCK00B880506/A/0010169910GRN111186110/07/2020 23:06FOPS6-UC/000100495777700118.818.818.805128805240620310521190620001880897.888010/07/2020 23:02kg1.5816611101510/07/2020 23:00AcceptedFromPO149277118.8
1710/07/2020 23:06904580939B8805PORDER2149277B8805000STOCK00B880506/A/0010169910GRN111186110/07/2020 23:06FOPS6-UC/000100495778120118.9418.9418.9405128805240620310521190620001894897.888010/07/2020 23:03kg1.5816611105810/07/2020 23:00AcceptedFromPO149277118.94
1810/07/2020 23:50904595614B8805STOCK00B880506/A/0014579752169910STOCK00B880503/A/0014579752169910010/07/2020 23:50FOPS6-UC/000100495777680118.6818.6818.687.888010/07/2020 23:02kg1.5816611101310/07/2020 23:00StockMovement-118.68
1910/07/2020 23:50904595615B8805STOCK00B880506/A/0014579752169910STOCK00B880503/A/0014579752169910010/07/2020 23:50FOPS6-UC/000100495777690119.4419.4419.447.888010/07/2020 23:02kg1.5816611101410/07/2020 23:00StockMovement-119.44
2010/07/2020 23:50904595616B8805STOCK00B880506/A/0014579752169910STOCK00B880503/A/0014579752169910010/07/2020 23:50FOPS6-UC/000100495777700118.818.818.87.888010/07/2020 23:02kg1.5816611101510/07/2020 23:00StockMovement-118.8
2111/07/2020 03:10904647848B8764PORDER2149270B8764000STOCK00B876406/A/0010170170GRN111200111/07/2020 03:10FOPS6-UC/000100495799100118.6618.6618.6605528764250520250521210520001866607.888011/07/2020 03:07kg1.5616611892111/07/2020 03:00AcceptedFromPO149270118.66
2211/07/2020 03:10904647849B8764PORDER2149270B8764000STOCK00B876406/A/0010170170GRN111200111/07/2020 03:10FOPS6-UC/000100495799110118.0218.0218.0205528764250520250521210520001802607.888011/07/2020 03:07kg1.5616611892211/07/2020 03:00AcceptedFromPO149270118.02
2311/07/2020 03:10904647850B8764PORDER2149270B8764000STOCK00B876406/A/0010170170GRN111200111/07/2020 03:10FOPS6-UC/000100495799120118.1818.1818.1805528764210520210521180520001818607.888011/07/2020 03:07kg1.5616611892311/07/2020 03:00AcceptedFromPO149270118.18
2411/07/2020 03:36904657104B8764STOCK00B876406/A/0014579917170170STOCK00B876403/A/0014579917170170011/07/2020 03:36FOPS6/000100495799350118.2418.2418.247.888011/07/2020 03:08kg1.5616611894711/07/2020 03:00StockMovement-118.24
2511/07/2020 03:52904661986B8805STOCK00B880503/A/0014579752169910STOCK00B880502/A/0014579752169910011/07/2020 03:52FOPS6-UC/00010049577770JOZEFW0118.818.818.87.888010/07/2020 23:02kg1.5816611101511/07/2020 03:00StockMovement-118.8
2611/07/2020 03:52904661987B8805STOCK00B880503/A/0014579752169910STOCK00B880502/A/0014579752169910011/07/2020 03:52FOPS6-UC/00010049577771JOZEFW0118.4918.4918.497.888010/07/2020 23:02kg1.5816611101611/07/2020 03:00StockMovement-118.49
2711/07/2020 03:52904661988B8805STOCK00B880503/A/0014579752169910STOCK00B880502/A/0014579752169910011/07/2020 03:52FOPS6-UC/00010049577772JOZEFW0118.5618.5618.567.888010/07/2020 23:02kg1.5816611101711/07/2020 03:00StockMovement-118.56
2811/07/2020 03:52904661989B8805STOCK00B880503/A/0014579752169910STOCK00B880502/A/0014579752169910011/07/2020 03:52FOPS6-UC/00010049577773JOZEFW0118.6218.6218.627.888010/07/2020 23:02kg1.5816611101811/07/2020 03:00StockMovement-118.62
2911/07/2020 03:52904661990B8805STOCK00B880503/A/0014579752169910STOCK00B880502/A/0014579752169910011/07/2020 03:52FOPS6-UC/00010049577774JOZEFW0118.4418.4418.447.888010/07/2020 23:02kg1.5816611101911/07/2020 03:00StockMovement-118.44
3011/07/2020 03:52904661991B8805STOCK00B880503/A/0014579752169910STOCK00B880502/A/0014579752169910011/07/2020 03:52FOPS6-UC/00010049577775JOZEFW0118.7218.7218.727.888010/07/2020 23:02kg1.5816611102011/07/2020 03:00StockMovement-118.72
IMV


Union (changes for debugging in Orange cells) - S18 needed a larger syntax change than suggested

odbcs140720.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1pallet_numberstock_areastock_rackstock_locationmodified_timeTotal_boxesTotal_itemsTotal_box_label_weigMax_lifeProductDescriptionAnalysis_Code2LocationLocation2Orig_TxConsumption Date
2126253A110627196552020200738747S58Veg 2 - 17780001 FROZEN 3/A/1Behind Retail Cranes04/06/2020 10:2209/06/2020 10:22
3163652A110627137013535952738584L8897NZ LAMB FROZEN 75CL FROZEN 2/A/1A-B Link Tunnel05/07/2020 03:4710/07/2020 03:47CodeDescriptionValidation TypeIMV ColumnValidation Days
4169912A110627159121313246.31738321B880595VL OTM STEAK TRIM FROZEN FROZEN 2/A/1A-B Link Tunnel10/07/2020 23:0215/07/2020 23:02L8806Veg 2 - 17780001 Veg BlendsA1
5170172A110627159175592.981B876495VL ROI STEAK TRIM FROZEN UTM FROZEN 2/A/1A-B Link Tunnel11/07/2020 03:0716/07/2020 03:07S57Veg 1 - 17760001 Veg BlendsA1
6170532A110627168742020368.54738319B880595VL OTM STEAK TRIM FROZEN FROZEN 2/A/1A-B Link Tunnel11/07/2020 15:4116/07/2020 15:418914NZ LAMB SKIRTS TEMPERING TWA NZ TemperingAN5
71709617F710627176113535673.341B837498VL UTM IRISH TRIM FROZEN FROZEN 17/F/7UF Floor11/07/2020 10:3816/07/2020 10:38B880595VL OTM STEAK TRIM FROZEN Frozen BeefAN3
81709717F710627176113838714.821B837498VL UTM IRISH TRIM FROZEN FROZEN 17/F/7UF Floor10/07/2020 22:0315/07/2020 22:03L8110UK LAMB SKIRTS FROZEN Frozen LambAN3
91709917F710627176113535699.541B837498VL UTM IRISH TRIM FROZEN FROZEN 17/F/7UF Floor11/07/2020 02:0116/07/2020 02:018912NZ 75CL TEMPERING TWA NZ TemperingAN5
101710117F710627176113535714.261B837498VL UTM IRISH TRIM FROZEN FROZEN 17/F/7UF Floor11/07/2020 02:0016/07/2020 02:00B884080VL OTM VALUE TRIM FROZEN Frozen BeefG3
111710517F710627177464444821.04738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:1317/07/2020 08:13B8373FINEST 95VL FROZEN Frozen BeefAN3
121710717F710627177464545843.34738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:2417/07/2020 08:24B876495VL ROI STEAK TRIM FROZEN UTM Frozen BeefAN3#REF!
131710817F710627177464545842.08738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:2917/07/2020 08:29B837498VL UTM IRISH TRIM FROZEN Frozen BeefAN3#REF!
141710917F710627177464545842.94738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:3017/07/2020 08:30B8767ROI 80VL OTM TRIM FROZEN Frozen BeefAN3
151711017F710627177464545843.16738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:3517/07/2020 08:35
161711217F710627177464545838.78738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:4017/07/2020 08:40
171711317F710627177464545847.78738004B837398VL IRISH H/E STEAK TRIM 17/F/7UF Floor12/07/2020 08:4217/07/2020 08:42
18171873A110627190531919361.38738010L8806NZ LEAN LAMB LOINS 10 PER PACK 3/A/1Behind Retail Cranes13/07/2020 08:1118/07/2020 08:1120
19171943A110627191424040574.98738010L8110NZ TB LEG 3/A/1Behind Retail Cranes13/07/2020 09:3518/07/2020 09:35U8
20172132A110627197843355.6738358B8767ROI 80VL OTM TRIM FROZEN FROZEN 2/A/1A-B Link Tunnel13/07/2020 17:3818/07/2020 17:38
21172142A110627197843333612.7738358B8767ROI 80VL OTM TRIM FROZEN FROZEN 2/A/1A-B Link Tunnel13/07/2020 17:4618/07/2020 17:46AN
Union
Cell Formulas
RangeFormula
K2:K21K2=INDEX(Primal!C:C,MATCH([@Product],Primal!B:B,0))
L2:L21L2=INDEX(Primal!BT:BT,MATCH([@Product],Primal!B:B,0))
M2:M21M2=CONCATENATE([@[stock_area]],"/",[@[stock_rack]],"/",[@[stock_location]])
N2:N21N2=INDEX(Lookups!$B$2:$B$30,MATCH([@Location],Lookups!$A$2:$A$30,0))
O2:O21O2=INDEX(IMV!AN:AN,MATCH([@[pallet_number]],IMV!U:U,0))
P2:P21P2=[@[Orig_Tx]]+5
W12W12=INDEX(INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)),MATCH(Table_Query_from_FOPS6ODBC[@[pallet_number]],IMV!U:U,0))
W13W13=INDEX(INDIRECT("IMV!"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)&":"&ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)),MATCH(Table_Query_from_FOPS6ODBC[@[pallet_number]],IMV!U:U,0))
S18S18=COLUMN(INDEX(Union!$T$4:$T$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],Union!$R$4:$R$15,0)))
S19S19=ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4)
S21S21=INDIRECT(ADDRESS(ROW(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),COLUMN(INDEX($U$4:$U$15,MATCH(Table_Query_from_FOPS6ODBC[@Product],$R$4:$R$15,0))),4))


Lookups (redundant for now, but I'll be moving Union!S:V there in the future)

odbcs140720.xlsm
ABCDEFGHIJKLM
1Fops LocationsPhysicalCodeDescriptionValidation TypeIMV ColumnValidation DaysValidation TypeValidation DaysIMV Column
21/A/1DawsonS58Veg 2 - 17780001 Veg BlendsA1Frozen Beef3AN
32/A/1A-B Link TunnelS57Veg 1 - 17760001 Veg BlendsA1Frozen Lamb3AN
43/A/1Behind Retail Cranes8914NZ LAMB SKIRTS TEMPERING TWA NZ TemperingAN5NZ Tempering5AN
54/A/1UA Warehouse FloorB880595VL OTM STEAK TRIM FROZEN Frozen BeefAN3Ingredients1A
65/A/1UA Racking FloorL8810UK LAMB SKIRTS FROZEN Frozen LambAN3Meat Free1A
76/A/1UC Warehouse Floor8912NZ 75CL TEMPERING TWA NZ TemperingAN5Veg Blends1A
88/A/1Ingredients TrailerB884080VL OTM VALUE TRIM FROZEN Frozen BeefAN3
98/A/2Frozen TrailerB8874FINEST 95VL FROZEN Frozen BeefAN3
108/A/3Frozen TrailerB876495VL ROI STEAK TRIM FROZEN UTM Frozen BeefAN3
118/A/4Frozen TrailerB837498VL UTM IRISH TRIM FROZEN Frozen BeefAN3
128/A/5Stock TrailerB8767ROI 80VL OTM TRIM FROZEN Frozen BeefAN3
Lookups
Cell Formulas
RangeFormula
G2:G12G2=IF(D2="","",INDEX($M$2:$M$35,MATCH(F2,$K$2:$K$35,0)))
H2:H12H2=IF(D2="","",INDEX($L$2:$L$35,MATCH(F2,$K$2:$K$35,0)))
E2:E12E2=INDEX(Primal!C:C,MATCH(D2,Primal!B:B,0))
 

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
155
and finally the primal worksheet (Again, cleansed data due to the size of the table)

odbcs140720.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJEKELEMENEOEPEQERESETEUEVEWEXEYEZFAFBFCFDFEFFFGFHFIFJFKFL
1Product_codeWIPDescriptionReserved5Dcp_codeSite_codeOld_socDump1Dump2Dump3Dump4Dump5Dump6Dump7Dump8Dump9Dump10Dump11Dump12Dump13Dump14Dump15Dump16OldLabFormatOldbcode_lookupPrstkrmfromWeight_rangeText_1Text_2Text_3Text_4Text_5Text_6Text_7Text_8TareHigh_toleranceLow_toleranceDate_type_1Date_advance_1Date_from_1Date_type_2Date_advance_2Date_from_2Date_type_3Date_advance_3Date_from_3Date_type_4Date_advance_4Date_from_4Barcode_dateTransaction_date_refNumber_of_labelsTolerance_onPriceStandard_weightOldNPPPrinterFixed_weightAssign_asBatch_out_suffixLink_codeAuto_group_codePack_codePacks_per_caseStandard_yieldInnersFixed_barcodeProduct_eqBatch_groupAnalysis_code_1Analysis_code_2Analysis_code_3Analysis_code_4Packs_per_InternTrayParentSlicing_lineChillerDefault_order_unitsReserved3Reserved4Min_prod_dateMax_prod_dateSales_order_codeMin_prod_timeMax_prod_timeNominal_codeAbbrv_descriptionBatch_in_suffixBarcode_lookupBarcode_offsetDelivery_exportTeam_SizeImportedProduct_yieldPcsppackPrstkrmtoIssstkrmfromIssstkrmtoPrtupoondelPick_areaPick_rackPick_slotDTP_areaDTP_rackDTP_slotComp_group_pluWt_AdjustJS_SKUAddSapItemPfxCoGrp_SOP_CodeCoGrp_Supp_IdImport_substituteTrace_codes_IdSecond_nominalBarcode_FormatAlt_Desp_LocnAlt_Pick_AreaAlt_Pick_RackAlt_Pick_LocExpBatAs1ItemReOrdStockLevelReOrdQtyReOrdUnitsBoxes_per_palletDump_selectionLean_percentkill_pack_max_diffAlt_stk_analysisExclude_from_ocmTransponder_reqdOCM_bit3OCM_bit4OCM_bit5OCM_bit6OCM_bit7OCM_bit8IntrastatCodeRetainBatchOnPcRetainBatchUntilRcRetainBatchUntilSPRCRBOPC_bit4RBOPC_bit5RBOPC_bit6RBOPC_bit7RBOPC_bit8Exclude_from_FOExclude_from_stockExclude_bit3Exclude_bit4Exclude_bit5Exclude_bit6Exclude_bit7Exclude_bit8Process_unitProcessUnitsPerCaseStd_net_kgProcess_base_wtProcess_base_wt_gainProc_gain_tol_pcentPallet_typeCustomer_codeEquipment2_codeANA_on_palletPack_life_1Pack_life_2Label_formatPlu_number
2B8373 B837398VL IRISH H/E STEAK TRIM 8373 FALSEFALSETRUEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEN K17464480173900001.66231010PD281J01J01111N02060100812 000000083730000000APRIMAL GREEN3 0 00 3100 00 8128373 0304FALSE0FALSE00 FALSE0 00 000 FALSEB8373 FALSE0 0 0 0FALSE00K6020950 FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE TRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE 0206.0135E-1546.0135E-1546.0135E-154 31N 8373
3B8764 B876495VL ROI STEAK TRIM FROZEN UTM 8764 FALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEN K17461801320173900001.682315K0PW241A02J01111N02060100809 000000087640000000APRIMAL FROZEN GREEN3 0 00 0 5000 00 8098764 0304FALSE0FALSE00 FALSE0 00 000 FALSEB8764 FALSE0 0 0 0FALSE00K6016880 FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE TRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE 0206.0135E-1546.0135E-1546.0135E-154 00N 8764
4B8805 B880595VL OTM STEAK TRIM FROZEN 8805 FALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEN K174618018937173900001.682515K0PW121A01J01111N02060100809 000000088050000000APRIMAL FROZEN GREEN3 0 00 0 5000 00 8098805 0304FALSE0FALSE00 FALSE0 00 088050 FALSEB8805 FALSE0 0 0 0FALSE00K6016880 FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE TRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE 0206.0135E-1546.0135E-1546.0135E-154 00N 8805
5L8806 L8806NZ LEAN LAMB LOINS 10 PER PACK 8806 FALSEFALSEFALSEFALSETRUETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEN K174602402173900002.14322L0PA01101D6513111N01560100801 6960000088060000000APRIMAL6 GREEN3 0 000 5000 00 8018806 0304FALSE0FALSE00 FALSE0 00 088060 FALSEL8806 FALSE0 0 0 0FALSE00K604800 FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE 0156.0135E-1546.0135E-1546.0135E-154 51N 8806
6L8810 L8810UK LAMB SKIRTS FROZEN 8810 FALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEN K174622910173900000.6322K0PW241A01J01211N027.260100832 000000088100000000APRIMAL FROZEN GREEN3 0 00 0000 00 8328810 0304FALSE0FALSE00 FALSE0 00 000 FALSEL8810 FALSE0 0 0 0FALSE00K601600 FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE 027.26.0135E-1546.0135E-1546.0135E-154 00N 8810
7B8874 B8874FINEST 95VL FROZEN 8874 FALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEN K174618010173900001.682518K0PW121A01J01111N02060100809 000000088740000000APRIMAL FROZEN GREEN3 0 00 0 0000 00 8098874 0304FALSE0FALSE00 FALSE0 00 000 FALSEB8874 FALSE0 0 0 0FALSE00K6016880 FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE TRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE 0206.0135E-1546.0135E-1546.0135E-154 00N 8874
8B8873 B8873FINEST 85VL FRESH 8873 FALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEN K17461630173900001.72315A0PD301J01J01111N02060100812 000000088730000000APRIMAL GREEN3 0 00 3100 00 8128873 0304TRUE0FALSE00 FALSE0 00 000 FALSEB8873 FALSE0 0 0 0FALSE00K6016800 FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE TRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE 0206.0135E-1546.0135E-1546.0135E-154 31N 8873
Primal
Cell Formulas
RangeFormula
B2:B8B2=TRIM([@[Product_code]])


Thanks for your help on this toadstool, I really do appreciate it.
 

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
155
Please note, there are no named ranges in this, only 3 tables:

1594757872623.png
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,745
Members
418,149
Latest member
amamiche67

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