+ Reply to Thread
Results 1 to 5 of 5

Compare two numbers to in column and find which digits are missing

Hybrid View

Karnik Compare two numbers to in... 01-15-2017, 09:33 AM
Tony Valko Re: Compare two numbers to in... 01-15-2017, 10:19 AM
JohnTopley Re: Compare two numbers to in... 01-15-2017, 10:38 AM
Karnik Re: Compare two numbers to in... 01-16-2017, 06:15 AM
Tony Valko Re: Compare two numbers to in... 01-16-2017, 10:34 AM
  1. #1
    Forum Contributor
    Join Date
    06-24-2012
    Location
    Australia
    MS-Off Ver
    Office365
    Posts
    309

    Compare two numbers to in column and find which digits are missing

    Hi Experts,
    I have two sets of columns consisting of 7 digits numbers.
    Both columns are suppose to be have same numbers ; Row B has full 7 digits. Column A has numbers less than 7 digit or some are missing when compared to Column B

    Task to me is to find the missing numbers in Column A . For 1st row, it is 1&7 are missing, 2nd Row No 1 is missing.

    Any guidance given for function is welcome!


    eg
    A B C
    Missing N0.
    33399 1333799 17
    333555 1333555 1
    557777 1557777 1
    335577 3355779 9
    77999 3577999 35
    337799 3357799 5
    7777 3577779 359

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare two numbers to in column and find which digits are missing

    This approach uses brute force!

    Data Range
    A
    B
    C
    1
    33399
    1333799
    17
    2
    333555
    1333555
    1
    3
    557777
    1557777
    1
    4
    335577
    3355779
    9
    5
    77999
    3577999
    35
    6
    337799
    3357799
    5
    7
    7777
    3577779
    359
    8
    ------
    ------
    ------


    This formula entered in C1 and copied down:

    =IF(ISERR(FIND(LEFT(B1),A1)),LEFT(B1),"")&IF(ISERR(FIND(MID(B1,2,1),A1)),MID(B1,2,1),"")&IF(ISERR(FIND(MID(B1,3,1),A1)),MID(B1,3,1),"")&IF(ISERR(FIND(MID(B1,4,1),A1)),MID(B1,4,1),"")&IF(ISERR(FIND(MID(B1,5,1),A1)),MID(B1,5,1),"")&IF(ISERR(FIND(MID(B1,6,1),A1)),MID(B1,6,1),"")&IF(ISERR(FIND(MID(B1,7,1),A1)),MID(B1,7,1),"")

    Something more elegant will require a VBA function or possibly returning each digit to a separate cell.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,767

    Re: Compare two numbers to in column and find which digits are missing

    UDF

    Function comp2numbers(n2 As Range, n1 As Range) As String
    
    missing = ""
    For i = 1 To 7
        m = Mid(n1, i, 1)
        If InStr(1, n2, m) = 0 Then
            missing = missing & m
            GoTo nexti
        Else
            ls1 = Len(n1) - Len(Replace(n1, m, ""))
            ls2 = Len(n2) - Len(Replace(n2, m, ""))
            If ls1 <> ls2 And InStr(1, missing, m) = 0 Then missing = missing & Mid(n1, i, 1)
        End If
    nexti:
    Next i
    
    comp2numbers = missing
    
    End Function
    =comp2numbers($A2,$B2)

    Checks for missing numbers and whether numbers of digits present are same e.g

    33399 1333799 17
    3399 1333799 137 only 2 3s
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-24-2012
    Location
    Australia
    MS-Off Ver
    Office365
    Posts
    309

    Re: Compare two numbers to in column and find which digits are missing

    Hi Tony & John,
    Thanks for looking in to my issue and resolving complex issue
    Thanks
    Karnik

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Compare two numbers to in column and find which digits are missing

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Compare two tables and get missing serial numbers
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-02-2016, 12:45 PM
  2. compare 2 different numbers and copy the last 3 digits.
    By Aranell in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-28-2013, 05:04 AM
  3. [SOLVED] Compare Sheets Find Missing Data
    By ClarkKent88 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-08-2012, 01:51 AM
  4. Replies: 4
    Last Post: 07-29-2012, 08:40 AM
  5. compare cols to find missing IDs
    By TopDogDave in forum Excel General
    Replies: 5
    Last Post: 11-15-2010, 06:08 AM
  6. Find missing numbers in a column
    By excelkeechak in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-26-2009, 12:04 AM
  7. A Macro to find missing serial numbers in a column
    By Khoshravan in forum Excel General
    Replies: 9
    Last Post: 08-06-2006, 05:40 AM

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