+ Reply to Thread
Results 1 to 7 of 7

Match string values and transfer data

  1. #1
    Registered User
    Join Date
    04-27-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    45

    Match string values and transfer data

    Hi friends:

    Could someone devise a macro to do the following operation? There are 2 workbooks, WorkbookA and WorkbookB. WorkbookA is a fixed reference workbook having 125 rows and columns A: P. WorkbookB can be any workbook having 50 to 300 rows and columns A :D. I want to compare string values in Column B of WorkbookA with string values in A column of WorkbookB and if they match, transfer corresponding data from WorkbookA Columns H, I, L, and P to WorkbookB columns E, F, G, and H respectively. I work in Excel 2003.

    Both workbooks have a title row and data from second row onwards.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,060

    Re: Match string values and transfer data

    Use a formula - in cells E2:H2 of Workbook B, use these four formulas, then copy down - change the workbook names and sheet names as appropriate.

    =VLOOKUP(A2,'[Workbook A.xlsm]Sheet Name'!$B:$P,7,FALSE)
    =VLOOKUP(A2,'[Workbook A.xlsm]Sheet Name'!$B:$P,8,FALSE)
    =VLOOKUP(A2,'[Workbook A.xlsm]Sheet Name'!$B:$P,11,FALSE)
    =VLOOKUP(A2,'[Workbook A.xlsm]Sheet Name'!$B:$P,15,FALSE)
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    04-27-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: Match string values and transfer data

    Hi Bernie Deitrick:

    Thanks for your formula. It took me almost 3 hours to decipher and understand the formula and I was finally able to tweak the same to my changing requirements. Thank you VERY MUCH for suggesting this wonderful formula.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,060

    Re: Match string values and transfer data

    Sorry you spent so much time. I would have explained the steps to quickly create the formulas if you had asked- but I hope you learned a bit more from the exercise

  5. #5
    Registered User
    Join Date
    04-27-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: Match string values and transfer data

    Hi Bernie Deitrick:
    Since you have really encouraged me with your reply, I have a few querries about this formula. Firstly, can we use this formula to highlight either a matching value or a nonmatching value? Secondly, supposing my main workbook, say Workbook 1, has columns A to H and I wish to compare column D values with column B value in Workbook 2, and on match, copy column B value of Workbook 1 to column J of Workbook 2. can we do this?. Here, we are trying to import cell values PRIOR to the reference column (column D) of the main workbook. Kindly explain.

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,060

    Re: Match string values and transfer data

    I will start with your second question first - you need to use an INDEX(MATCH) combination. MATCH returns the position of the matching value within the range being searched:

    =MATCH(Value, Range, False)

    like

    =MATCH(A2,'[Workbook A.xlsm]Sheet Name'!$D:$D,FALSE)

    The False forces an exact match, which is what you want. (this formula returns an error if the value is not found, which you can use to your advantage - more later)

    Then, to return the value from the previous column, use INDEX

    =INDEX(Range2, MATCH(Value, Range, False))

    Like

    =INDEX('[Workbook A.xlsm]Sheet Name'!$B:$B, MATCH(A2,'[Workbook A.xlsm]Sheet Name'!$D:$D,FALSE))

    To build up these formulas, have both workbooks open. Then in the cell where you want the formula, type

    =INDEX(

    then switch to the other workbook, and select the entire column of values, and make sure that you have then $B:$B form of the address by pressing F4 until it appears the way you want. Do the same for the MATCH part.

    For highlighting values that are there, use Conditional Formatting with the Formula is option, with a formula like

    =NOT(ISERROR(MATCH(D2,'[Workbook A.xlsm]Sheet Name'!$D:$D,FALSE)))

    For CF to work,your formula need to return TRUE or FALSE for the cell being formatted, so you can create the formula in a cell to get it working and then copy it to use for the CF. To highlight values that are not there, simply apply formatting to those cells with the CF - if the CF is applied, the formatting is hidden. IF you have other options, you could also use this to show missing values:

    =ISERROR(MATCH(D2,'[Workbook A.xlsm]Sheet Name'!$D:$D,FALSE))

  7. #7
    Registered User
    Join Date
    04-27-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: Match string values and transfer data

    Hi Bernie:

    Thank you for your prompt response. It will take quite some time for me to understand what you have explained with so much details. I will come back to you in due course with my observations. Thanks once again.

+ 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] Match Criteria on Two Sheets and Transfer Values
    By trevor2524 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-28-2014, 08:15 AM
  2. [SOLVED] Need Help..Match A Cell Values and Transfer to Different Sheets
    By trevor2524 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-23-2014, 03:20 PM
  3. [SOLVED] Match Data from Another Sheet and Transfer Values
    By basketball2524 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-20-2013, 11:49 PM
  4. [SOLVED] Match and Transfer data
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-23-2012, 05:46 AM
  5. transfer and match data
    By kant101 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-30-2007, 04:26 AM

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