Please see attached file.
In the file I have 2 tables of data, input from the scan gun (A5: up to E2000) and stock file data (H5: up to M2000)
I have the formulas to compare the ids from both lists and report whether there is a match or a blank call (displayed as end of data) which I ignore and if there is a id not in both lists it shows the ID.
What I need is a way to display this info nicely, preferably showing matching data from the tables.
Any ideas? I did think about a pivot table but didn't seem to work for me. below is an example of the main data I have.
Location Stock ID Date Time User Stock ID PRFX Gauge Width Weight Location OnGunNotStock OnStockNotGun 09002 M34707 25/09/2014 105236 chris Q39639 LBP 2.300 1050.000 16.840 sal01 Match Q39639 09002 M34696 25/09/2014 105434 chris B82804 PRL .690 1000.000 5.150 Match B82804 09002 M34695 25/09/2014 105516 chris K85721 LBI .300 1050.000 8.820 Match K85721 09002 M34751 25/09/2014 105625 chris SO4185 LDZ .230 980.000 .640 13000 M34751 Match 09002 M34018 25/09/2014 105710 chris M91739 LDI .230 1050.000 .290 Match M91739 09002 M34710 25/09/2014 105756 chris M04527 LDZ .230 1030.000 3.585 m3002 Match M04527 09002 M34708 25/09/2014 105803 chris PJ5059 BPZ .408 598.000 1.870 13003 Match PJ5059 09002 M34706 25/09/2014 105814 chris Z08107 LBZ .300 400.000 .666 A0801 Match Z08107 09002 M34711 25/09/2014 105823 chris Q35614 2.300 969.000 14.580 M3002 Match Q35614
Make a named range ScanID (col B)
Make another named range ScanData (col A to E)
End of stock add a column = match(h2,ScanId,0) (column N)
after this use columns for the "matched data" = index(ScanData,$N2,x) replacing x with 1 to 4 in subsequent columns
Will need an IF statement in column N and with the index function)
(and I haven't had my morning coffee yet so check the order of the parameters)
Posting Permissions
Reply With Quote
Bookmarks