+ Reply to Thread
Results 1 to 4 of 4

matching string from a list and copying adjacent cells

  1. #1
    Registered User
    Join Date
    07-13-2011
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    2

    matching string from a list and copying adjacent cells

    Hi!

    I've been given an assignment at work that requires tedious manual address list updating, I have a list of new Contacts and old contacts in two excel sheets. I need to update the new contact list.

    I started reading VBA for Dummies and will be updating this thread as I figure something up but I would appreciate any help.

    The Problem:

    I have two address lists, i.e. company names in range(A1:A2990), and sales managers and their info in adjacent cells i.e. range(B1:D2990). The thing is that if I put the two lists side to side, the same company names of old and new lists are not on the same rows, i.e. new list has more companies and some old companies are not in the new list at all. Also, I have some of the sales managers info already listed in the new list and I need to retain that info.

    I think I would need the following kind of program to include all of the old contacts in the new list:

    Pull string out of selected cell (sheet1.A1) of the new list and match it with old list (sheet2.(A1:A2990))
    IF there is a match,
    THEN, IF adjacent cells(sheet1.B1:D1) of the new list are empty, copy cells(sheet2.B1:D1) to (sheet1.B1:D1)
    ELSE, Stop
    ELSE, Stop

    Then move on to cell sheet1.B1 and repeat the procedure.
    Stop the whole loop after cell A2990.

    At this time I know how to build nested IF statements.
    I need to figure out how to pull strings from cells and match them with strings in an array.
    Also I need to figure out how to make a loop that will repeat the whole procedure and then move on to the next cell until A2990.

    Hope someone can give some pointers.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: matching string from a list and copying adjacent cells

    Derivatives,

    Give the following a try:
    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar

  3. #3
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: matching string from a list and copying adjacent cells

    Derivatives,

    Alternatively, you could use the VLOOKUP formula as a non-macro solution. Assuming your data has headers, you would put the following in cell B2 of the 'New Contacts' worksheet:
    =IF(ISERROR(VLOOKUP($A2,'Old Contacts'!$A:$A,1,FALSE)),"",VLOOKUP($A2,'Old Contacts'!$A:$D,COLUMN(),FALSE))

    Then you would just copy over and down. See attached for an example of how it works.

    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-13-2011
    Location
    Finland
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: matching string from a list and copying adjacent cells

    Wow! Thanks a lot, that VBA code worked like a charm! That saved me a lot of time and now I can pick the code apart with VBA help and figure out how it works. Thanks again Tigeravatar, you're amazing!

+ 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