+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

  1. #1
    Registered User
    Join Date
    09-22-2009
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    27

    compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

    Dear Friends,

    I have one workbook (Excel 2007) with 2 sheets. Sheet-1 contains 4 columns like the following:

    Excel Sheet 1
    COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4
    1005 10 Sam Royal Blue
    1005 20 Peter Navy Blue
    1006 50 Mary Rose
    1100 40 Sonam Green
    1156 80 Rashmi Pink

    And the Sheet -2 also contains 4 columns. The result should be like the following:

    RESULT
    Excel Sheet 2
    COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4
    997 90 Rupa
    1005 10 Sam Royal Blue
    1100 40 Sonam Green
    1100 40 Sonam Green
    1156 80 Rashmi Pink
    886 30 Vanaja
    775 50 Vivek
    1005 20 Peter Navy Blue
    1006 50 Mary Rose
    1006 60 Varun
    1100 40 Sonam Green
    1005 40 Priya
    1156 80 Rashmi Pink


    What I need now is, if the Sheet-2 (first 3 columns) data match with the Sheet-1 (first 3 columns) data, then the fourth column of Sheet-2 to be automatically filled with Sheet-1 fourth column data.

    Please do help.

    The following works fine with single column checking. I need it with 3 columns checking.

    =VLOOKUP(A14,Sheet2!$A$2:$B$5,2,FALSE)

    Thanks in advance.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

    Post a sample workbook - where spaces are involved in strings it's impossible for us to determine whether a word appears in one column or another.... as it stands I'm not sure you list any values in Column 4 on sheet 1... and it's important we know if said values are numeric or text so to reiterate post a sample file.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

    Perhaps in the meantime you mean along the lines of:

    Please Login or Register  to view this content.
    You will invariably find it easier to concatenate the values of interest into one column and conduct a simple INDEX/MATCH using the concatenation column, eg:

    Please Login or Register  to view this content.
    This is perhaps a little more straightforward to follow than the earlier LOOKUP method and will be more efficient should you have large volumes of data in your real file.
    Last edited by DonkeyOte; 09-24-2009 at 02:53 AM. Reason: added concat approach for benefit of OP

  4. #4
    Registered User
    Join Date
    09-22-2009
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

    Quote Originally Posted by DonkeyOte View Post
    Post a sample workbook - where spaces are involved in strings it's impossible for us to determine whether a word appears in one column or another.... as it stands I'm not sure you list any values in Column 4 on sheet 1... and it's important we know if said values are numeric or text so to reiterate post a sample file.
    Please find attached the workbook.

    Thanks.
    Attached Files Attached Files

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

    Thanks - you should find post # 3 suggestions can be applied to your file.

  6. #6
    Registered User
    Join Date
    09-22-2009
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: compare 3 columns in Sheet-1 with Sheet-2 then add Sheet-1 fourth column data ...

    Quote Originally Posted by DonkeyOte View Post
    Thanks - you should find post # 3 suggestions can be applied to your file.
    Thanks. Could you please write code in my sample sheet so that I can understand better? If you won't mind.

    Thanks again.

+ 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