+ Reply to Thread
Results 1 to 5 of 5

Finding exact number match in column. If no match, remove one number and check next column

  1. 02-19-2013, 12:20 PM #1
    v0idern
    v0idern is offline
    Registered User
    Join Date
    02-18-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Finding exact number match in column. If no match, remove one number and check next column

    Hello fellas,

    I have a problem that i haven't seen a solution for anywhere.

    I have 6 columns with numbers: (A2:A400 ... G2:G400) that is a check list of numbers.

    $H$1 contains a cell with a number value that should be checked against the check list columns, and always contains 8 digits, 65126548 for instance

    However:
    Column A contains 2-digit numbers
    Column B contains 3-digit numbers
    Column C contains 4-digit numbers
    Column D contains 5-digit numbers
    Column E contains 6-digit numbers
    Column F contains 7-digit numbers
    Column G contains 8-digit numbers

    I'm trying to find a neat formula that first checks for a perfect matcht for H1 against check list column G2:G400, then removes one digit from the right and checks against column F (against 651265)... etc. and then stops after checking column A for 2-digit match (against 65).

    Does anyone know a neat way to do this without using data bases?

    Appreciate your help!
    Reply With QuoteRegister To Reply
  2. 02-19-2013, 01:20 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Finding exact number match in column. If no match, remove one number and check next co

    do you want all matches ?
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 02-19-2013, 01:38 PM #3
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Finding exact number match in column. If no match, remove one number and check next co

    For instance H1=12345678
    Creating a list of 1-7 digits from the right: {12345678,1234567,123456,12345,1234,123,12}
    INT($H$1*10^-{0,1,2,3,4,5,6})
    Check with COUNTIF to figure out where in A2:G4 meets any of above list:
    COUNTIF($A$2:$G$4,INT($H$1*10^-{0,1,2,3,4,5,6}))
    and position:
    MATCH(1,COUNTIF($A$2:$G$4,INT($H$1*10^-{0,1,2,3,4,5,6})),0)
    Finally with INDEX to show the first match from the right columns:
    Please Login or Register  to view this content.
    File attached
    Not sure it meets yours, but this may be a start.
    Attached Files Attached Files
    Quang PT
    Reply With QuoteRegister To Reply
  4. 02-19-2013, 01:57 PM #4
    dredwolf
    dredwolf is offline
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Finding exact number match in column. If no match, remove one number and check next co

    Maybe Something like this :MulticolumnNumSearch.sol1.xlsx
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post
    Reply With QuoteRegister To Reply
  5. 02-19-2013, 02:06 PM #5
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Finding exact number match in column. If no match, remove one number and check next co

    I took a different approach (using Martin's sheet (thank you Martin)), in H2
    =LEFT($H$1,9-ROW())+0 copied down

    In I1 copied down
    =MATCH(H1,INDEX($A$2:$G$7, ,8-ROW()),0)+1
    gives row where match is found.
    See attachment
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  6. 02-19-2013, 02:19 PM #6
    v0idern
    v0idern is offline
    Registered User
    Join Date
    02-18-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Finding exact number match in column. If no match, remove one number and check next co

    Thanks a lot for your kind replies, all!

    I can see that i was a bit unclear in my questions, and bebo021999 you hit it almost spot on! Thanks!

    Is it possible to alter the function easily so that it just produces a 1 value if a match, and 0 if not? (I actually needed both that and the returning value)
    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)

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