Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

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

  1. #1
    Join Date
    11th March 2008
    Posts
    6

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    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.

  3. #3
    Join Date
    11th March 2008
    Posts
    6

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    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.

  5. #5
    Join Date
    11th March 2008
    Posts
    6

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    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.

  7. #7
    Join Date
    11th March 2008
    Posts
    6

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    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 at 10:12.
    Bill
    Tip: To avoid chasing code always use Option Explicit.

  9. #9
    Join Date
    11th March 2008
    Posts
    6

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    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.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 6
    Last Post: August 19th, 2008, 09:33
  2. Replies: 13
    Last Post: September 28th, 2007, 10:49
  3. Find Match In Column & Offset For Return Value
    By jeremyr_12 in forum EXCEL HELP
    Replies: 5
    Last Post: September 3rd, 2007, 08:47
  4. Lookup A Column And Return Row If Match
    By bryceo in forum EXCEL HELP
    Replies: 1
    Last Post: April 1st, 2007, 05:30
  5. Find Match & Return Same Row Different Column
    By fanku in forum EXCEL HELP
    Replies: 1
    Last Post: November 14th, 2006, 15:38

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
  •  
porno