Duplicates and Omissions

[Heliops]

Hi there, I have 2 spreadsheets (MS Office 2016), one which has 4000 lines of data (component part numbers) and an updated one which has 4500 lines of data. Because of the type of work I am doing it was reasonable for me to assume that the original 4000 items were included in the new spreadsheet together with an additional 500 items which had been added. As I specially wanted to know what the new items were, I combined all the part numbers from the 2 spreadsheets into one column and and did a search for duplicates (using the Conditional Formatting/Highlight Cell Rules/Duplicate Values function on the ribbon). This showed that I had 3800 duplicates which I was able to delete from the updated spreadsheet leaving me just the new items. I had expected to find 4000 duplicates but with only 3800 found, this left me with 700 new items meaning that 200 items must have been deleted from the original spreadsheet (hope that all makes sense). I am not an excel expert in any way but feel that I am able to check for duplicates OK (although the process does seem a bit cumbersome) but I would be most grateful if someone could help to explain how I can compare 2 spreadsheets and find out what data is missing between the 2.

And, if there is a better way of checking for duplicates, that would be a bonus.

 

[GR00007]

Microsoft has a "Spreadsheet Compare" program you can access, click Start and type Spreadsheet Compare and if you have it (2016 does) give it a try.

 

[Heliops]

Thanks, but that doesnt give me what I need

 

[Akuini]

Hi, Heliops
Try this:
In a new sheet put the data (component part numbers) from sheet1 in col A & data (component part numbers) from sheet2 in col B.
Run this macro.

[FONT=lucida console][color=Royalblue]Sub[/color] a1082990a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1082990-duplicates-omissions.htmlDim rr As Long[/color][/i]
[color=Royalblue]Dim[/color] rr [color=Royalblue]As[/color] [color=Royalblue]Long[/color], i [color=Royalblue]As[/color] [color=Royalblue]Long[/color], m [color=Royalblue]As[/color] [color=Royalblue]Long[/color], p [color=Royalblue]As[/color] [color=Royalblue]Long[/color], q [color=Royalblue]As[/color] [color=Royalblue]Long[/color], r [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] x [color=Royalblue]As[/color] [color=Royalblue]Variant[/color]
[color=Royalblue]Dim[/color] d [color=Royalblue]As[/color] [color=Royalblue]Object[/color]

Application.ScreenUpdating = [color=Royalblue]False[/color]
rr = Range([color=brown]"A:B"[/color]).Find([color=brown]"*"[/color], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    va = Range([color=brown]"A1:B"[/color] & rr)
        [color=Royalblue]Set[/color] d = CreateObject([color=brown]"scripting.dictionary"[/color])
            d.CompareMode = vbTextCompare
    
    m = [color=crimson]1[/color]
    [color=Royalblue]For[/color] [color=Royalblue]Each[/color] x [color=Royalblue]In[/color] va
        i = i + [color=crimson]1[/color]
        [color=Royalblue]If[/color] i > rr [color=Royalblue]Then[/color] m = [color=crimson]2[/color]
        x = Trim(x)
        [color=Royalblue]If[/color] [color=Royalblue]Not[/color] d.Exists(x) [color=Royalblue]Then[/color]
            d(x) = m
            [color=Royalblue]Else[/color]
            d(x) = [color=crimson]3[/color]
        [color=Royalblue]End[/color] [color=Royalblue]If[/color]
    [color=Royalblue]Next[/color]
  
[color=Royalblue]If[/color] d.Exists([color=brown]""[/color]) [color=Royalblue]Then[/color] d.Remove [color=brown]""[/color]
    
    [color=Royalblue]For[/color] [color=Royalblue]Each[/color] x [color=Royalblue]In[/color] d
        [color=Royalblue]If[/color] d(x) = [color=crimson]1[/color] [color=Royalblue]Then[/color] p = p + [color=crimson]1[/color]: Cells(p, [color=brown]"D"[/color]) = x
        [color=Royalblue]If[/color] d(x) = [color=crimson]2[/color] [color=Royalblue]Then[/color] q = q + [color=crimson]1[/color]: Cells(q, [color=brown]"E"[/color]) = x
        [color=Royalblue]If[/color] d(x) = [color=crimson]3[/color] [color=Royalblue]Then[/color] r = r + [color=crimson]1[/color]: Cells(r, [color=brown]"F"[/color]) = x
    [color=Royalblue]Next[/color]
    
Application.ScreenUpdating = [color=Royalblue]True[/color]
[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

The result:
Col D is the items that only exist in sheet1.
Col E is the items that only exist in sheet2.
Col F is the items that exist in both sheets.

Example:

Book1
	A	B	C	D	E	F
1	Adonis	Reese		Adonis	Reese	Adrian
2	Adrian	Cameron		Maximo	Cameron	Mario
3	Mario	Josiah			Josiah	Kamryn
4	Kamryn	Adrian
5	Maximo	Mario
6		Kamryn
Sheet3

 

[Heliops]

Thank you Akuini but I am not terribly familiar with Excel so could you please explain what you mean by "run this macro"?

 

[Akuini]

Thank you Akuini but I am not terribly familiar with Excel so could you please explain what you mean by "run this macro"?

Ok, you may learn to do that from this site:

This is from: http://www.contextures.com/xlvba01.html
Copy Excel VBA Code to a Regular Module
Instead of starting from scratch, if you need an Excel macro, you can often find sample code at reputable sites on the internet. To copy that code, and add it to one of your workbooks, follow these steps:
+ Copy the sample code that you want to use
+ Open the workbook in which you want to add the code
+ Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
+ Choose Insert | Module
+ Where the cursor is flashing, choose Edit | Paste

-------
To run the macro:
1. Click anywhere inside the code
2. Hit F5

 

[Heliops]

That is absolutely fantastic many thanks but could I ask you to make a slight amendment to the macro so that my results are better. The way my data is displayed is as below:

Description Column A	Part Number Column B
Gearbox	12345
Cable	123457
Switch	34567

<tbody>
</tbody>

The description is in column A and the associated Part number is in Column B so the 2 need to go together. In your earlier reply you said to create a new sheet and put all my data from my 1st sheet into Column A and from the 2nd sheet into Column B before running the macro, I really need to put the data from the 1st sheet into Columns A and B and from the 2nd sheet into Columns C and D and then I guess my results will be shown in Columns E&F, G&H and I&J. Hope this makes sense.

 

[Akuini]

The description is in column A and the associated Part number is in Column B so the 2 need to go together. In your earlier reply you said to create a new sheet and put all my data from my 1st sheet into Column A and from the 2nd sheet into Column B before running the macro, I really need to put the data from the 1st sheet into Columns A and B and from the 2nd sheet into Columns C and D and then I guess my results will be shown in Columns E&F, G&H and I&J. Hope this makes sense.

Ok, try this:

[FONT=lucida console][color=Royalblue]Sub[/color] a1082990b()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1082990-duplicates-omissions.html[/color][/i]
[color=Royalblue]Dim[/color]  rr [color=Royalblue]As[/color] [color=Royalblue]Long[/color], i  [color=Royalblue]As[/color] [color=Royalblue]Long[/color], m  [color=Royalblue]As[/color] [color=Royalblue]Long[/color], p  [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color]  q [color=Royalblue]As[/color] [color=Royalblue]Long[/color], r  [color=Royalblue]As[/color] [color=Royalblue]Long[/color], j  [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] x [color=Royalblue]As[/color] [color=Royalblue]Variant[/color]
[color=Royalblue]Dim[/color]  d [color=Royalblue]As[/color] [color=Royalblue]Object[/color], e  [color=Royalblue]As[/color] [color=Royalblue]Object[/color]

Application.ScreenUpdating = [color=Royalblue]False[/color]
rr = Range([color=brown]"A:D"[/color]).Find([color=brown]"*"[/color], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    va = Range([color=brown]"A1:D"[/color] & rr)
        [color=Royalblue]Set[/color] d = CreateObject([color=brown]"scripting.dictionary"[/color])
            d.CompareMode = vbTextCompare
        [color=Royalblue]Set[/color] e = CreateObject([color=brown]"scripting.dictionary"[/color])
            e.CompareMode = vbTextCompare

    m = [color=crimson]1[/color]
     [color=Royalblue]For[/color] j = [color=crimson]2[/color]  [color=Royalblue]To[/color] UBound(va, [color=crimson]2[/color])  [color=Royalblue]Step[/color] [color=crimson]2[/color]
         [color=Royalblue]For[/color] i = [color=crimson]1[/color]  [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
            
            x = Trim(va(i, j))
            e(x) = va(i, j - [color=crimson]1[/color])
            [color=Royalblue]If[/color] [color=Royalblue]Not[/color] d.Exists(x) [color=Royalblue]Then[/color]
                d(x) = j
                [color=Royalblue]Else[/color]
                d(x) = [color=crimson]3[/color]
            [color=Royalblue]End[/color] [color=Royalblue]If[/color]
        [color=Royalblue]Next[/color]
    [color=Royalblue]Next[/color]
[color=Royalblue]If[/color] d.Exists([color=brown]""[/color]) [color=Royalblue]Then[/color] d.Remove [color=brown]""[/color]
    
    [color=Royalblue]For[/color] [color=Royalblue]Each[/color] x [color=Royalblue]In[/color] d
         [color=Royalblue]If[/color] d(x) = [color=crimson]2[/color]  [color=Royalblue]Then[/color] p = p + [color=crimson]1[/color]: Cells(p,  [color=brown]"F"[/color]) = x: Cells(p, [color=brown]"E"[/color]) =  e(x)
        [color=Royalblue]If[/color] d(x) =  [color=crimson]4[/color] [color=Royalblue]Then[/color] q = q +  [color=crimson]1[/color]: Cells(q, [color=brown]"H"[/color]) = x:  Cells(q, [color=brown]"G"[/color]) = e(x)
         [color=Royalblue]If[/color] d(x) = [color=crimson]3[/color]  [color=Royalblue]Then[/color] r = r + [color=crimson]1[/color]: Cells(r,  [color=brown]"J"[/color]) = x: Cells(r, [color=brown]"I"[/color]) =  e(x)
    [color=Royalblue]Next[/color]
    
Application.ScreenUpdating = [color=Royalblue]True[/color]
[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

Result:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Abraham</td><td style="text-align: right;;">11</td><td style=";">Roland</td><td style="text-align: right;;">16</td><td style=";">Jeremiah</td><td style="text-align: right;;">12</td><td style=";">Truman</td><td style="text-align: right;;">17</td><td style=";">Abraham</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Jeremiah</td><td style="text-align: right;;">12</td><td style=";">Truman</td><td style="text-align: right;;">17</td><td style=";">Maximo</td><td style="text-align: right;;">13</td><td style=";">Dustin</td><td style="text-align: right;;">18</td><td style=";">Roland</td><td style="text-align: right;;">16</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Maximo</td><td style="text-align: right;;">13</td><td style=";">Dustin</td><td style="text-align: right;;">18</td><td style=";">Ariel</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Rex</td><td style="text-align: right;;">19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Ariel</td><td style="text-align: right;;">14</td><td style=";">Rex</td><td style="text-align: right;;">19</td><td style=";">Sutton</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Luciano</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Sutton</td><td style="text-align: right;;">15</td><td style=";">Luciano</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Roland</td><td style="text-align: right;;">16</td><td style=";">Abraham</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Rex</td><td style="text-align: right;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Luciano</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4,8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br />

 

[Heliops]

Absolutely fantastic, very many thanks for your help

 

[Akuini]

You're welcome & thanks for the feedback.