+ Reply to Thread
Results 1 to 6 of 6

Match two same cells accordint to first 7 letters

  1. 04-28-2017, 03:50 AM #1
    mgecelov
    mgecelov is offline
    Registered User
    Join Date
    01-17-2017
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    16

    Match two same cells accordint to first 7 letters

    I have 2 columns, ColumnA and ColumnB (as flollowing example).



    A124 0 171 171 156 1385 86 A124 0 172 172 156 1385 86
    A124 5 173 173 157 1380 878 A124 11 174 174 163 1375 861
    A124 11 174 174 163 1375 861 A124 15 182 182 175 1440 829
    A124 15 180 180 175 1440 829 A124 25 182 182 207 1434 1261
    A124 25 182 182 207 1434 1261 A124 45 233 240 233 1884 1397
    A124 35 230 205 230 1661 1375 A124 65 272 272 272 1925 1841
    A124 65 272 272 272 1925 1841


    I need to find the same cells according to first 7 letters and those same cells has to be next to each other, if there is no match, cell will be empty as following:


    A124 0 171 171 156 1385 86 A124 0 172 172 156 1385 86
    A124 5 173 173 157 1380 878
    A124 11 174 174 163 1375 861 A124 11 174 174 163 1375 861
    A124 15 180 180 175 1440 829 A124 15 182 182 175 1440 829
    A124 25 182 182 207 1434 1261 A124 25 182 182 207 1434 1261
    A124 35 230 205 230 1661 1375
    A124 45 233 240 233 1884 1397
    A124 65 272 272 272 1925 1841 A124 65 272 272 272 1925 1841

    Is it possible to do it?
    Many thanks in advance
    Last edited by mgecelov; 04-28-2017 at 04:38 AM.
    Reply With QuoteRegister To Reply
  2. 04-28-2017, 04:33 AM #2
    davsth
    davsth is offline
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,210

    Re: Match two same cells accordint to first 7 letters

    if your 2 lists are in a2:b8 perhaps

    =IFERROR(INDEX($B$2:$B$8,MATCH(LEFT(A2,7)&"*",$B$2:$B$8,0)),"")
    Reply With QuoteRegister To Reply
  3. 04-28-2017, 04:59 AM #3
    mgecelov
    mgecelov is offline
    Registered User
    Join Date
    01-17-2017
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    16

    Re: Match two same cells accordint to first 7 letters

    The list is actually longer (more then 13000 rows in columnA and 10000 in columnB) ... it was just example.....but still it doesn't really work :/
    Reply With QuoteRegister To Reply
  4. 04-28-2017, 05:14 AM #4
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,518

    Re: Match two same cells accordint to first 7 letters

    Assuming Header in row1
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 04-28-2017, 07:03 AM #5
    mgecelov
    mgecelov is offline
    Registered User
    Join Date
    01-17-2017
    Location
    Brussels, Belgium
    MS-Off Ver
    2010
    Posts
    16

    Re: Match two same cells accordint to first 7 letters

    Amazing....many thanks !!!!!!!!
    Reply With QuoteRegister To Reply
  6. 04-28-2017, 07:10 AM #6
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,518

    Re: Match two same cells accordint to first 7 letters

    You are welcome and thanks for the rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    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. [SOLVED] Using LEFT inside INDEX-MATCH to find values that match first 5 letters of referenced cell
    By Big.Moe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-26-2017, 04:46 PM
  2. Match only the first letters
    By Orongo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-15-2016, 11:14 AM
  3. vba code accordint to the codition the value should display
    By pranav.y in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-27-2013, 05:26 AM
  4. Match inputs in one column with data from another if >50% of their consec letters match
    By TaskinRahman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-24-2012, 01:47 AM
  5. conditional match first 2 letters of 3 cells
    By allgeef in forum Excel General
    Replies: 3
    Last Post: 03-22-2011, 11:27 AM
  6. Pull letters and validating cells below to see if the letters are same
    By Rickage in forum Excel General
    Replies: 9
    Last Post: 07-15-2010, 08:54 PM
  7. Allocate costs accordint following distribution rules
    By mlewit1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2007, 07:21 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