+ Reply to Thread
Results 1 to 5 of 5

Tender data and asset Audit comparison Vlookup help

  1. #1
    Registered User
    Join Date
    12-21-2015
    Location
    Sydney
    MS-Off Ver
    excel2010
    Posts
    3

    Tender data and asset Audit comparison Vlookup help

    Hi guys,

    I'm in the middle of an asset audit at work after our client wanted a second audit due to the first one not being in the right format (long story).

    Our client uses barcodes to identify each asset (easily over 150K different assets), and its location, model etc..

    eg: barcode: 301225 Asset Id: Fire Door Mod: FireSafe etc....

    Now I've got the original tender data which was given to the company to work from, and the 1st audit data (I've adjusted the rows/ columns to match the tender format), and we are in the middle of the 2nd audit.

    Now I've seperated each sets of data into their own sheets on the one file and I need to have a 'summary' sheet which displays any duplicate barcodes found in all three audits- as well as their information (location, model etc.), which is in the same row as the barcode itself.

    I'm not sure if a Vlookup would be able to do this in the manner i need. ( not just the barcode- but all the information associate with the barcode as well)

    eg: if barcode #123456 was found in the tender AND the 1st audit, then I need a row for both barcodes and their associated info.


    Barcode Description Location Model


    #123456 Fire door Building f10 FireSafe
    #123456 Fire Door Building f10 FireSafe

    (where plain text is data from the tender sheet and BOLD text is from the 1st audit)

    The reasons it must be this way are to ensure data integrity and completeness.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Tender data and asset Audit comparison Vlookup help

    Hi, welcome to the forum

    VLOOKUP() will probably do this for you, you would just need to adjust the column to return, for each column needed.

    Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-21-2015
    Location
    Sydney
    MS-Off Ver
    excel2010
    Posts
    3

    Re: Tender data and asset Audit comparison Vlookup help

    Not to sound like a fool- but I cant seem to find a way to upload a workbook for you?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Tender data and asset Audit comparison Vlookup help

    Not a problem, many cant find that, the 1st time around

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  5. #5
    Registered User
    Join Date
    12-21-2015
    Location
    Sydney
    MS-Off Ver
    excel2010
    Posts
    3

    Re: Tender data and asset Audit comparison Vlookup help

    Thanks.

    I tried this same thing yesterday and it didn't work- all good now.

    I havent put anything into the Variances/report tab, but basically any duplicates need to be in that tab- in seperate rows (so I can tell which data set they came from [i've made each data set a different coloured font]), and have their information in the same columns.

    so if there is for example- one barcode found in all three, then there would be three rows- each with the same barcode (in their different font colours), with the rest of the information for that row showing as well.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] vlookup to capture asset purchase totals and depreciation
    By equplay in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-24-2015, 01:06 PM
  2. Replies: 2
    Last Post: 09-27-2014, 02:51 PM
  3. Trying to pull 10 percent of data for audit
    By noobexceldude in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-03-2014, 11:40 PM
  4. [SOLVED] VBA to clean and audit data
    By Yg74 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-23-2012, 01:47 PM
  5. Tender bids
    By zokibg in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-01-2012, 06:18 AM
  6. VLOOKUP Audit? Searched, but still need help...
    By aoyamajpn in forum Excel General
    Replies: 1
    Last Post: 04-05-2007, 11:36 AM
  7. Comparison Problem for tender bids
    By bartman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-13-2006, 03:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1