+ Reply to Thread
Results 1 to 5 of 5

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

  1. 01-15-2017, 09:33 AM #1
    Karnik
    Karnik is offline
    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
    Reply With QuoteRegister To Reply
  2. 01-15-2017, 10:19 AM #2
    Tony Valko
    Tony Valko is offline
    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.
    Reply With QuoteRegister To Reply
  3. 01-15-2017, 10:38 AM #3
    JohnTopley
    JohnTopley is online now
    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,693

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

    UDF

    Please Login or Register  to view this content.
    =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
    Reply With QuoteRegister To Reply
  4. 01-16-2017, 06:15 AM #4
    Karnik
    Karnik is offline
    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
    Reply With QuoteRegister To Reply
  5. 01-16-2017, 10:34 AM #5
    Tony Valko
    Tony Valko is offline
    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 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] 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. [SOLVED] Find missing entries from a list of sequential numbers and letters in a column?
    By ramim in forum Excel General
    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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1