+ Reply to Thread
Results 1 to 8 of 8

VBA code to match patterns

  1. 09-17-2016, 05:35 AM #1
    sandu1
    sandu1 is offline
    Registered User
    Join Date
    06-01-2016
    Location
    Singapore
    MS-Off Ver
    13
    Posts
    55

    VBA code to match patterns

    Hello Team,

    Need your help in doing pattern matching. I have data on two sheets.

    I have to match a column from two sheets and get the corresponding values when the pattern matches
    Sheet 1 Second.PNG and Sheet2 First.PNG

    As per the screen shot, I have column called section name on Sheet 1 and Name on Sheet 2.
    Example: Name "Agentporta" will be appended by random numbers in the front and back of the name like "sec02552Agenportaportno"
    I have compare and if the name matches, I have get the corresponding ticket no from Sheet 2 and paste it into Sheet 1.

    I am confused in matching here... please help
    Reply With QuoteRegister To Reply
  2. 09-17-2016, 06:38 AM #2
    LeoTaxi
    LeoTaxi is offline
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: VBA code to match patterns

    If Columns are A and B

    Please Login or Register  to view this content.
    Kind regards
    Leo
    Reply With QuoteRegister To Reply
  3. 09-17-2016, 09:32 AM #3
    sandu1
    sandu1 is offline
    Registered User
    Join Date
    06-01-2016
    Location
    Singapore
    MS-Off Ver
    13
    Posts
    55

    Re: VBA code to match patterns

    Thank you for the prompt reply. I apologize, columns are A and B. I am trying to use the formula provided above on cell Sheet 1 (B2). But I am not getting any result. What I get is #N/A....Could you please help me?
    ?
    Reply With QuoteRegister To Reply
  4. 09-17-2016, 10:29 AM #4
    LeoTaxi
    LeoTaxi is offline
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: VBA code to match patterns

    i have to see the file

    Kind regards
    Leo
    Reply With QuoteRegister To Reply
  5. 09-17-2016, 10:40 AM #5
    mikerickson
    mikerickson is offline
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: VBA code to match patterns

    Im wondering about names like Bob and Bobby

    Which of those goes with the section name sec06754bobbynaryno

    is the section name always of the format "sec" followed by 5 digits followed by the name followed by "portno" ?

    If so the formula might be =INDEX(Sheet2!$A:$A, MATCH("sec?????" & A1 & "portno", Sheet2!$B:$B, 0), 1)
    Last edited by mikerickson; 09-17-2016 at 10:45 AM.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.
    Reply With QuoteRegister To Reply
  6. 09-19-2016, 09:32 PM #6
    sandu1
    sandu1 is offline
    Registered User
    Join Date
    06-01-2016
    Location
    Singapore
    MS-Off Ver
    13
    Posts
    55

    Re: VBA code to match patterns

    @mikerickson, thank you so much that helped!! Can we have the same code implemented using VBA? Its freezing excel as the data size is more...
    Reply With QuoteRegister To Reply
  7. 09-19-2016, 10:12 PM #7
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,516

    Re: VBA code to match patterns

    VBA
    Assuming both data in col.A
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 09-20-2016, 12:53 AM #8
    mikerickson
    mikerickson is offline
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: VBA code to match patterns

    Quote Originally Posted by sandu1 View Post
    ...Its freezing excel as the data size is more...
    If native Excel is freezing because of the amount of data, then Excel is the wrong program.
    Rather than spending your time working on limping by with workarounds, your time would be better spend finding the right software for your need, and using that.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Code that searches each cell for a match; if no match then conditional format.
    By kev.wong1991 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2014, 06:32 PM
  2. patterns in Excel
    By oopspete in forum Excel General
    Replies: 1
    Last Post: 11-23-2011, 06:35 PM
  3. Converting 2007 VBA color patterns to 2003 color patterns
    By ChemistB in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-13-2011, 12:00 PM
  4. Code req for returning results of a close match if two other columns match exactly
    By davidparkes in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-23-2010, 12:00 PM
  5. How to match data patterns across multiple columns
    By jonathanpc in forum Excel General
    Replies: 9
    Last Post: 04-15-2009, 11:02 AM
  6. Setting patterns
    By Brisbane Rob in forum Excel General
    Replies: 1
    Last Post: 04-28-2006, 07:57 PM
  7. patterns
    By DArinello in forum Excel Formulas & Functions
    Replies: 60
    Last Post: 09-06-2005, 06:05 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1