Results 1 to 2 of 2

Thread: data display

  1. #1

    data display

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365
    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

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts