Results 1 to 2 of 2

Thread: Macro code for matching and replacing the values

  1. #1

    Macro code for matching and replacing the values



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear Sir /Madam,
    In the given file, where I have to match between column A and F. in F column few values are missing. According to A col, the cols F,G,H,I,J,K,L has to replaced in matching with A col. For easy understanding , I have did manually in the 2 sheet (required output sheet) for your kind reference.
    please do help me... like this I have to do for 10 more files...

    I need very urgently...please oblige and do the needful...
    please find the attachment...

    Thanks & Regards,
    Dharani.
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Hello, and welcome to the forum.
    I think that this can be done with a VLOOKUP function. If you have Excel 2007 or later you can use the IFERROR function (1) below, which is a much shorter way to deal with look up errors than the pre-2007 IF(ISERROR(.
    Which makes a much longer formula (2) below. On your "required input" sheet copy either 1 or 2 below across and down columns F to L starting at F2. Im assuming that the number of data rows on both sheets will be the same, on the final version.
    Before copying extend array F2:L433 to cover your full data set.

    Formula (1) - Excel 2007 and later

    =IFERROR(VLOOKUP($A2,'input sheet'!$F$2:$L$433,COLUMN(A$1),FALSE), "")

    Formula (2) Excel 2003 and earlier:

    =IF(ISERROR(VLOOKUP($A2,'input sheet'!$F$2:$L$433,COLUMN(A$1),FALSE)),"",(VLOOKUP($A2,'input sheet'!$F$2:$L$433,COLUMN(A$1),FALSE)))

    If you can live with the VLOOKUP error reporting, you can leave out the error coding.

    Good luck,

    Hercules

    Last edited by Hercules1946; 2013-09-30 at 08:29 PM.

Posting Permissions

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