Duplicates and Omissions

Heliops

New Member
Joined
Nov 16, 2018
Messages
10
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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.


Code:
[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
ABCDEF
1AdonisReeseAdonisReeseAdrian
2AdrianCameronMaximoCameronMario
3MarioJosiahJosiahKamryn
4KamrynAdrian
5MaximoMario
6Kamryn
Sheet3
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:

Code:
[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 />
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top