+ Reply to Thread
Results 1 to 12 of 12

Compare two tables and get missing serial numbers

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Compare two tables and get missing serial numbers

    Hello experts
    I have two tables in sheet1 (Table 1 >> Range B2:E7 & Table2 >> Range H2:K5)

    Table2 is considered the stock or existing items and these items is sequenced From / To (Serials)
    Table1 is the items which are sold (Not existing ..)

    Now I need to have output of the remaining items with their serials (Just still existing)
    I have attached the expected output in range N2:Q9 to be clear
    Thanks advanced for help
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,632

    Re: Compare two tables and get missing serial numbers

    Hi,
    I'd probably go one of two strategies. Either:
    1) expand all rows in table 1 and 2 (20 and 56 respectively)
    2) remove all rows present in table 1 from table 2
    3) collapse table 2
    Or:
    1) for each row in table 1 find appropriate row in table 2
    2) if row from table 1 touches start or end of scope in found row in table 2 (like A 101) change remaining scope in table 2 (to 109-130)
    2a) if it's in the middle - split row of table 2 into 2 rows (like A 112-118) A109-130 in table 2 being split into A109-A111 and A 119-A130)

    PS. I'm writing from smartphone, so have no easy way to write (and especially, to test) the code.
    Best Regards,

    Kaper

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Compare two tables and get missing serial numbers

    Hi YasserKhalil,
    try
    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Compare two tables and get missing serial numbers

    Thanks a lot Mr. Kaper for your suggestion
    Thank you very much Mr. Nilem for this great code

    Could you please put some hints as a guide to me to be able to get these great lines?

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Compare two tables and get missing serial numbers

    I am sorry for disturbing you Mr. Nilem
    Have a look at this sample : I got incorrect results. How can I fix the code to get right results?
    Attached Files Attached Files

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Compare two tables and get missing serial numbers

    Hello YasserKhalil
    try this
    Please Login or Register  to view this content.
    If you have Excel x64 bit, try to use a data type LongLong (like this Dim j^) for post #3 code

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Compare two tables and get missing serial numbers

    Thanks a lot for more help
    I got these serials like that (141465120599.992) .. It is supposed to get it with no factions (decimals)
    Can the output of these serials be of text format so as to appear in proper way?

    Thanks advanced for help

  8. #8
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Compare two tables and get missing serial numbers

    try to change this
    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Compare two tables and get missing serial numbers

    Thank you very much Mr. Nilem
    I appreciate your great and wonderful help
    Best and kind regards

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Compare two tables and get missing serial numbers

    I have tested the results and it is excellent.
    I will apply it to original file and let me post again if I faced any problem
    Thanks a lot for awesome solution
    Last edited by YasserKhalil; 10-02-2016 at 04:49 AM.

  11. #11
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Compare two tables and get missing serial numbers

    sure
    and what about about the LongLong (unfortunately I do not have such a possibility)

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Compare two tables and get missing serial numbers

    You have told that LongLong is for excel 64Bit and I am working now on 32 Bit

+ 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. Replies: 1
    Last Post: 07-20-2016, 03:36 PM
  2. [SOLVED] VBA to compare two tables, copy the missing rows from one table to the other
    By adfo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-14-2015, 04:52 PM
  3. Compare columns from different tables, copy the missing value to another table
    By laalaa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-21-2014, 04:26 AM
  4. Deriving list of missing serial numbers
    By cimmind in forum Excel General
    Replies: 3
    Last Post: 04-14-2010, 04:22 AM
  5. Finding a serial number in a list of serial numbers
    By zocoloco in forum Excel General
    Replies: 2
    Last Post: 02-04-2009, 05:20 AM
  6. 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
  7. to find missing serial numbers in randomly generated numbers
    By B.H. Hadi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-01-2005, 07:00 PM

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