1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Comparing/Matching data across in Excel worksheets & more

Discussion in 'Business Applications' started by tycelchu, Apr 10, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. tycelchu

    tycelchu Thread Starter

    Joined:
    Dec 16, 2005
    Messages:
    17
    Hi,

    Hopefully one of you delightful people can help me with a small problem I'm having pulling together data from multiple reports... I have manually combined 4 separate reports into 1 workbook with 4 worksheets (1 for each report). Each row represents a separate job.

    The 1st worksheet is an export from our own Access database, the 3 others come from external reports.

    Whilst each report/worksheet contains different sets of data, 1 column in each worksheet contains ‘CSR’ or ‘Site ID’ numbers. These numbers can be used to match the jobs - however a) not every worksheet has every number b) some numbers may appear more than once on the same worksheet (these are essentially returns to the same job).

    The 1st worksheet also has a column with OPID numbers (Column A), these are unique numbers that we have paired to a CSR or Site ID number.

    I want to compare the numbers in the CSR column of the 1st worksheet (Column B of Sheet 1) with the CSR columns in the other worksheets and where there is a match, copy/add the corresponding OPID number to a blank column in each worksheet.

    What would be the best way to do this?

    Thanks in advance for any help and advice offered!
     
  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,916
    so on the 1st worksheet only appear once

    if so you could use vlookup to do this

    so on a new column in one of the other worksheets say Column D

    do this

    =VLOOKUP( Cell to compare on the worksheet, Range on the sheet1 worksheets with $ in , column to copy data from , false)

    then copy the formula down the page and onto the other sheets

    assume worksheet 1 all the Site ID numbers are in column B starting at B2 to B500

    then in worksheet 2 the csr is in column A starting at A2

    then the formula say in worksheet 2 column D
    =VLOOKUP(A2,Sheet1!$B$2:$B$500,1,FALSE)

    if it finds the number it will return the number in the column - if not than #n/a
     
  3. tycelchu

    tycelchu Thread Starter

    Joined:
    Dec 16, 2005
    Messages:
    17
    Thanks very much! That (or a slight tweak on it) worked a treat!
     
  4. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,916
    your welcome (y) thanks for letting us know
    You can mark your own threads solved using the [​IMG] button at the top of the page of the thread in the upper left corner. :)
     
  5. tycelchu

    tycelchu Thread Starter

    Joined:
    Dec 16, 2005
    Messages:
    17
    I do have a follow on query if it's ok to ask here?

    I now have all of the reports in one workbook and have been able to remove any jobs from the seperate worksheets without an OPID number thanks to the VLOOKUP.

    My boss has now asked if it is possible compile the data from each of the worksheets into one. The problem with that is that the five worksheets have different amounts of rows/jobs on (1436 on the main worksheet, 84, 331, 124 & 786 on the other four) so a simple copy/paste isn't going to work.

    Essentially what I need to do is cross compare the jobs up via their OPID number, and (by way of example) if an OPID number in Worksheet 2 matches one in Worksheet 1 - copy the entire row (A-O) from Worksheet 2 into empty columns (DD-DR) at the end of the corresponding row in Worksheet 1.

    Hopefully this makes sense, please ask if not.

    Thanks again in advance for any assistance.

    Ty
     
  6. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,916
    you can stilll use vlookup for that

    so in DD
    enter the vlookup formula
    in worksheet 1

    =VLOOKUP(A2,Sheet2!$A$2:$O$500,1,FALSE)

    then in DE
    =VLOOKUP(A2,Sheet2!$A$2:$O$500,2,FALSE)

    then in DF
    =VLOOKUP(A2,Sheet2!$A$2:$O$500,3,FALSE)

    Notice the third element (after the , increases by 1 ) so this looks up the value in A2 and then looks down sheet2 column A until it finds a match
    Then the 1,2,3 etc
    1 returns the matching line in the 1st column
    2 returns the matching line in the 2nd column
    3 returns the matching line in the 3rd column
    etc to column O = 15

    with a slight tweak should work
     
  7. tycelchu

    tycelchu Thread Starter

    Joined:
    Dec 16, 2005
    Messages:
    17
    ACE! Thanks again! I was sure I'd be able to use the VLOOKUP again somehow but couldn't figure out the 'third element' as you put it.

    You're a star!
     
  8. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,916
    your welcome (y) thanks for letting us know
    You can mark your own threads solved using the [​IMG] button at the top of the page of the thread in the upper left corner. :)
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1048695