+ Reply to Thread
Results 1 to 5 of 5

Matching cells in order to merge data across multiple sheets

  1. #1
    Registered User
    Join Date
    01-27-2010
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2002
    Posts
    7

    Matching cells in order to merge data across multiple sheets

    I have two worksheets put out from the (arguably archaic) software my company uses. In one it has the account numbers and their outstanding balance. The second has the account numbers, addresses, an other pertinent information.

    I would like a way for spreadsheet one to receive the addresses and contact information from spreadsheet two (in their own columns of course) by matching the account numbers.
    Last edited by quickie; 10-06-2011 at 01:23 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Matching cells in order to merge data across multiple sheets

    Any advice here is going to be very limited if we don't get to see your workbook.

    Suppose Sheet1 has account numbers in column A and balance in column B.

    Sheet2 has account numbers in column A, addresses in column B, and other pertinent information in other pertinent rows.

    I assume there is a header row in each sheet.

    In Sheet1!C2, use this formula to get the address, and copy down:

    =VLOOKUP(A2,Sheet2!A:B,2,FALSE)

    For the next column:

    =VLOOKUP(A2,Sheet2!A:C,3,FALSE)

    For each succeeding column to populate, modify the formula by increasing the width of the range and adding 1 to the number.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    01-27-2010
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2002
    Posts
    7

    Re: Matching cells in order to merge data across multiple sheets

    That didn't work and it is completely the fault of my own for not scrubbing and uploading a test sheet.

    As you can see in sheet 2 there are more results than in sheet 1. This is true for the actual spreadsheets as well. So if possible the code will need to be able to search sheet 2 as the data will not be on the same lines as in sheet 1.

    Again, I apologize for not providing all the data necessary and wasting time. Thank you in advance for your help.
    Attached Files Attached Files

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Matching cells in order to merge data across multiple sheets

    The attached solution is the same as described in my earlier post, tailored to your data layout.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-27-2010
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2002
    Posts
    7

    Re: Matching cells in order to merge data across multiple sheets

    I want to be perfectly clear in my next statement.

    I love you.

    Testing now on my much longer sheet to ensure all is well, again thank you for your help. Will post as solved when tested to ensure it is.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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