Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Match Text In Column Of Another Workbook & Return Nth Column Same Row

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Match Text In Column Of Another Workbook & Return Nth Column Same Row

    Hello OZgrid,
    Awsome that I found this place through Google search. Thanks Dave.

    I have two excel files.
    File #1 is a varying length and has 4 columns with ID #s in Column A.
    File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B.

    I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2.

    I've tried using the substitute function but I could only replace one found ID at a time.

    I hope someone will be able to help me out.

    Thanks
    JB
    Attached Files

  • #2
    Re: Match Text In 2 Columns And Replace With A 3rd

    John, Welcome to ozGrid Tell your friends about us.

    In file1 insert a blank column B
    In cell B1 enter this formula and copy down

    =VLOOKUP(A1,[file2.xls]Sheet1!$A:$B,2,FALSE)

    Hope this helps y'all
    Bill
    Tip: To avoid chasing code always use Option Explicit.

    Comment


    • #3
      Re: Match Text In 2 Columns And Replace With A 3rd

      WOW! You guys really are SUPER FAST!

      Bill.
      Thanks for your super fast reply!
      Your formula works, but maybe I wasn't specific enough. I need to replace the values in column A on file#1 with what is returned by the formula. Is that possible?

      I'll be using this file to import into a different software which I have no control over. So I'm not able to add another column to it. (I wasn't specific file parameters not being modifiable, sorry.)



      Thanks
      JB

      Comment


      • #4
        Re: Match Text In 2 Columns And Replace With A 3rd

        After inserting column B and the formula. Then select column B, Copy and paste special values. This will convert the formulas in column B to the values the formula produced. Then Delete column A.
        Bill
        Tip: To avoid chasing code always use Option Explicit.

        Comment


        • #5
          Re: Match Text In Column Of Another Workbook & Return Nth Column Same Row

          Bill,
          Thanks a mint! It works. And will help me move my data around tomorrow.

          I'm glad I signed up on this forum! Accurate answers and friendly folks offering the advice.

          Thanks again!
          JB

          Comment


          • #6
            Re: Match Text In Column Of Another Workbook & Return Nth Column Same Row

            Your welcome, Glad to help Tell a friend about ozGrid
            Bill
            Tip: To avoid chasing code always use Option Explicit.

            Comment


            • #7
              Re: Match Text In Column Of Another Workbook & Return Nth Column Same Row

              Can this process be automated like a Macro? Since the columns will have varying lengths, I'm afraid a recording a Macro won't work.
              I'd really like it to be run from a button or better yet run when file#1 is opened. Now THAT would be convenient.

              Should I post this as a seperate thread? I don't want to break any rules or annoy anybody here. There's too much info here to get banned from it.

              Thanks again.

              Comment


              • #8
                Re: Match Text In Column Of Another Workbook & Return Nth Column Same Row

                Sorry for the delay in getting back to you.

                Attached are your two sample files. There is a button on fille2 to map file1.

                Hope this works for you.
                Last edited by Bill Rockenbach; May 5th, 2009, 10:12.
                Bill
                Tip: To avoid chasing code always use Option Explicit.

                Comment


                • #9
                  Re: Match Text In Column Of Another Workbook & Return Nth Column Same Row

                  Bill.
                  THANKS!
                  I've been real busy with a lot of other things, but this looks awsome! All I have to do is put in the real info!

                  I hope someone (like me) who searches is able to find this thread! A great solution to this problem...

                  Thanks
                  JB

                  Comment


                  • #10
                    Re: Match Text In Column Of Another Workbook & Return Nth Column Same Row

                    Glas I could help, John
                    Bill
                    Tip: To avoid chasing code always use Option Explicit.

                    Comment

                    Trending

                    Collapse

                    There are no results that meet this criteria.

                    Working...
                    X