Finding Duplicate Values

S

starpupil

New Member
Joined
Jan 30, 2012
Messages
10
Okay ! I got a two huge separate excel sheets.
One has values upto 90k (Sheet 1) and other has 7k (Sheet 2)
Now I want to find the duplicate value in sheet 2 present in sheet 1 and have a all unique values to be present in sheet three

It be some what like this

[TABLE="class: grid, width: 192"]
<TBODY>[TR]
[TD]Sheet 1</SPAN>
[/TD]
[TD]Sheet 2</SPAN>
[/TD]
[TD]Sheet 3</SPAN>
[/TD]
[/TR]
[TR]
[TD]Column A </SPAN>
[/TD]
[TD]Column A </SPAN>
[/TD]
[TD]Column A </SPAN>
[/TD]
[/TR]
[TR]
[TD]Adam </SPAN>
[/TD]
[TD]Logan
[/TD]
[TD]Adam</SPAN>
[/TD]
[/TR]
[TR]
[TD]Shawn </SPAN>
[/TD]
[TD]Jane
[/TD]
[TD]Shawn</SPAN>
[/TD]
[/TR]
[TR]
[TD]Logan
[/TD]
[TD]Ajay
[/TD]
[TD]Jose</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jane
[/TD]
[TD]Puta
[/TD]
[TD]Sarah</SPAN>
[/TD]
[/TR]
[TR]
[TD]Sarah </SPAN>
[/TD]
[TD]Gandalf
[/TD]
[TD]Peter</SPAN>
[/TD]
[/TR]
[TR]
[TD]Ajay
[/TD]
[TD]Bruce </SPAN>
[/TD]
[TD]Pedro</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jose </SPAN>
[/TD]
[TD]Clark </SPAN>
[/TD]
[TD]Bruce</SPAN>
[/TD]
[/TR]
[TR]
[TD]Pedro </SPAN>
[/TD]
[TD]Clark</SPAN>
[/TD]
[TD]Magix</SPAN>
[/TD]
[/TR]
[TR]
[TD]Puta
[/TD]
[TD]Tony
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Magix </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gandalf
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tony
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]



Now to be precise, there are two column in each sheet having the Values. I want to erase the value which are duplicate and in the third sheet to reflect the only unique ones

Can someone help me with a formula or a Macro.
Looking forward to quick reply

Thanks in Advance
 
Sort by date Sort by votes
try to select your column you need to investigate
go to conditional formatting
select new rule
there is arrows list
select format only unique or duplicate values
choose duplicate
change formating to the color ex: yellow
apply
ok

now it will change color for repeated values
sort this column according color or value


done
 
Upvote 0
starpupil said:
Okay ! I got a two huge separate excel sheets.
One has values upto 90k (Sheet 1) and other has 7k (Sheet 2)
Now I want to find the duplicate value in sheet 2 present in sheet 1 and have a all unique values to be present in sheet three

It be some what like this

[TABLE="class: grid, width: 192"]
<TBODY>[TR]
[TD]Sheet 1</SPAN>
[/TD]
[TD]Sheet 2</SPAN>
[/TD]
[TD]Sheet 3</SPAN>
[/TD]
[/TR]
[TR]
[TD]Column A </SPAN>
[/TD]
[TD]Column A </SPAN>
[/TD]
[TD]Column A </SPAN>
[/TD]
[/TR]
[TR]
[TD]Adam </SPAN>
[/TD]
[TD]Logan
[/TD]
[TD]Adam</SPAN>
[/TD]
[/TR]
[TR]
[TD]Shawn </SPAN>
[/TD]
[TD]Jane
[/TD]
[TD]Shawn</SPAN>
[/TD]
[/TR]
[TR]
[TD]Logan
[/TD]
[TD]Ajay
[/TD]
[TD]Jose</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jane
[/TD]
[TD]Puta
[/TD]
[TD]Sarah</SPAN>
[/TD]
[/TR]
[TR]
[TD]Sarah </SPAN>
[/TD]
[TD]Gandalf
[/TD]
[TD]Peter</SPAN>
[/TD]
[/TR]
[TR]
[TD]Ajay
[/TD]
[TD]Bruce </SPAN>
[/TD]
[TD]Pedro</SPAN>
[/TD]
[/TR]
[TR]
[TD]Jose </SPAN>
[/TD]
[TD]Clark </SPAN>
[/TD]
[TD]Bruce</SPAN>
[/TD]
[/TR]
[TR]
[TD]Pedro </SPAN>
[/TD]
[TD]Clark</SPAN>
[/TD]
[TD]Magix</SPAN>
[/TD]
[/TR]
[TR]
[TD]Puta
[/TD]
[TD]Tony
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Magix </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gandalf
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tony
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]



Now to be precise, there are two column in each sheet having the Values.
Click to expand...
Lookig at your data the Sheet3 result does not match (where Jose is coming from)
What do you mean "two column".
Could you provide exact data and result?
 
Upvote 0
Tried, but like I said I have 90 K values to go thru, I need to jst come into another sheet without any hassles of looking into it again and again
 
Upvote 0
Code: 
Sub Populate()
    Dim i As Long, j As Long
    For i = 1 To ThisWorkbook.Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
        For j = 1 To ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
            If ThisWorkbook.Sheets(1).Range("A" & i).Value = ThisWorkbook.Sheets(2).Range("A" & j).Value Then _
            ThisWorkbook.Sheets(3).Range("A" & ThisWorkbook.Sheets(3).Range("A" & Rows.Count).End(xlUp).Row + 1).Value = ThisWorkbook.Sheets(1).Range("A" & i).Value
        Next j
    Next i
End Sub


or

Code: 
Sub PopulateFromDuplicate()
    
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, _
            i As Long, j As Long, lastRowA As Long, lastRowB As Long, cnt As Long

    Set ws1 = ThisWorkbook.Sheets(1)
    Set ws2 = ThisWorkbook.Sheets(2)
    Set ws3 = ThisWorkbook.Sheets(3)
    
    lastRowA = ws1.Range("A" & Rows.Count).End(xlUp).Row
    lastRowB = ws2.Range("A" & Rows.Count).End(xlUp).Row

    cnt = 1
    For i = 1 To lastRowB
        For j = 1 To lastRowA
            If ws1.Range("A" & i).Value = ws2.Range("A" & j).Value Then
                ws3.Range("A" & cnt).Value = ws1.Range("A" & i).Value
                cnt = cnt + 1
            End If
        Next j
    Next i
End Sub
 
Last edited by a moderator:
Upvote 0
Hi Robert,
Apologies, I tried to copy paste from excel think something went wrong.


[TABLE="width: 192"]
<TBODY>[TR]
[TD]Sheet 1</SPAN>[/TD]
[TD] Sheet 2</SPAN>[/TD]
[TD]Sheet 3</SPAN>[/TD]
[/TR]
[TR]
[TD]Column A </SPAN>[/TD]
[TD]Column A </SPAN>[/TD]
[TD]Column A </SPAN>[/TD]
[/TR]
[TR]
[TD]Adam </SPAN>[/TD]
[TD]Logan[/TD]
[TD]Adam</SPAN>[/TD]
[/TR]
[TR]
[TD]Shawn </SPAN>[/TD]
[TD]Jane [/TD]
[TD]Shawn</SPAN>[/TD]
[/TR]
[TR]
[TD]Logan[/TD]
[TD]Ajay [/TD]
[TD]Jose</SPAN>[/TD]
[/TR]
[TR]
[TD]Jane [/TD]
[TD]Puta [/TD]
[TD]Sarah</SPAN>[/TD]
[/TR]
[TR]
[TD]Sarah </SPAN>[/TD]
[TD]Gandalf [/TD]
[TD]Peter</SPAN>[/TD]
[/TR]
[TR]
[TD]Ajay [/TD]
[TD]Bruce </SPAN>[/TD]
[TD]Pedro</SPAN>[/TD]
[/TR]
[TR]
[TD]Jose </SPAN>[/TD]
[TD]Clark </SPAN>[/TD]
[TD]Bruce</SPAN>[/TD]
[/TR]
[TR]
[TD]Pedro </SPAN>[/TD]
[TD]Tony[/TD]
[TD]Magix</SPAN>[/TD]
[/TR]
[TR]
[TD]Puta [/TD]
[TD][/TD]
[TD]Clark</SPAN>[/TD]
[/TR]
[TR]
[TD]Magix </SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gandalf [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=3></COLGROUP>[/TABLE]

Basically the example I have give here is a whole three different sheet.
Jose in sheet 3 is coming form Sheet 1 as there is no duplicate of Jose in Sheet 2


The Highlighted ones are the how they appear when I use highlight duplicate condition. I manually wrote the unique values in sheet three.

I would like to have a formula or a marco formula to go through sheet 1 containing 90k + Values in coloumn A & B and compare it to Sheet 2 columns A & B and have the unique values appear in Sheet 3.

hope this was a better clarification of what I was looking for :)
 
Upvote 0
starpupil said:
Hi Robert,
Apologies, I tried to copy paste from excel think something went wrong.


[TABLE="width: 192"]
<tbody>[TR]
[TD]Sheet 1[/TD]
[TD] Sheet 2[/TD]
[TD]Sheet 3[/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column A[/TD]
[TD]Column A[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]Logan[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD]Shawn[/TD]
[TD]Jane [/TD]
[TD]Shawn[/TD]
[/TR]
[TR]
[TD]Logan[/TD]
[TD]Ajay [/TD]
[TD]Jose[/TD]
[/TR]
[TR]
[TD]Jane [/TD]
[TD]Puta [/TD]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Gandalf [/TD]
[TD]Peter[/TD]
[/TR]
[TR]
[TD]Ajay [/TD]
[TD]Bruce[/TD]
[TD]Pedro[/TD]
[/TR]
[TR]
[TD]Jose[/TD]
[TD]Clark[/TD]
[TD]Bruce[/TD]
[/TR]
[TR]
[TD]Pedro[/TD]
[TD]Tony[/TD]
[TD]Magix[/TD]
[/TR]
[TR]
[TD]Puta [/TD]
[TD][/TD]
[TD]Clark[/TD]
[/TR]
[TR]
[TD]Magix[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gandalf [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tony[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Basically the example I have give here is a whole three different sheet.
Jose in sheet 3 is coming form Sheet 1 as there is no duplicate of Jose in Sheet 2


The Highlighted ones are the how they appear when I use highlight duplicate condition. I manually wrote the unique values in sheet three.

I would like to have a formula or a marco formula to go through sheet 1 containing 90k + Values in coloumn A & B and compare it to Sheet 2 columns A & B and have the unique values appear in Sheet 3.

hope this was a better clarification of what I was looking for :)
Click to expand...
try
Code: 
Sub djdj()
Dim d As Object, a, b
Dim u(), q, c As Long

Set d = CreateObject("scripting.dictionary")

With Sheets("sheet1")
    a = Range(.Cells(1), .Cells(Rows.Count, 1).End(3))
End With
With Sheets("sheet2")
    b = Range(.Cells(1), .Cells(Rows.Count, 1).End(3))
End With

For Each q In a
    d(q) = d(q) + 1
Next q

For Each q In b
    d(q) = d(q) + 1
Next q

ReDim u(1 To d.Count, 1 To 1)
For Each q In d.keys
    If d(q) = 1 Then c = c + 1: u(c, 1) = q
Next q

Sheets("sheet3").Cells(1).Resize(c) = u

End Sub
 
Upvote 0
This worked like a charm, but it jst gave me for one coloumn.

I have two columns in each sheet. How can I compare that array to give me the results
 
Upvote 0
starpupil said:
This worked like a charm, but it jst gave me for one coloumn.

I have two columns in each sheet. How can I compare that array to give me the results
Click to expand...
Easy way for that is just include the modifications in red.

Perhaps slightly unsound without further knowledge of whether the second column is B or some other, whether both columns have the same length, etc.
Rich (BB code): 
Sub djdj2()
Dim d As Object, a, b
Dim u(), q, c As Long
Set d = CreateObject("scripting.dictionary")
With Sheets("sheet1")
    a = Range(.Cells(1), .Cells(Rows.Count, 1).End(3)).Resize(, 2)
End With
With Sheets("sheet2")
    b = Range(.Cells(1), .Cells(Rows.Count, 1).End(3)).Resize(, 2)
End With

For Each q In a
    d(q) = d(q) + 1
Next q
For Each q In b
    d(q) = d(q) + 1
Next q
ReDim u(1 To d.Count, 1 To 1)
For Each q In d.keys
    If d(q) = 1 Then c = c + 1: u(c, 1) = q
Next q

Sheets("sheet3").Cells(1).Resize(c) = u

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Finding unique Value of corresponding duplicate value (Brain Teaser)
Replies
1
Views
437
Peter_SSs
Peter_SSs
R
  • Question Question
How to find duplicate value for multiple columns simultaneously
Replies
2
Views
440
MickG
M
K
  • Question Question
Finding unique values in excel column
Replies
3
Views
630
Fluff
Fluff
J
  • Question Question
Find Duplicate Texts among rows using macro
Replies
7
Views
734
admiral100
A
M
  • Question Question
Unique values with Formula NOT Macro
Replies
6
Views
718
mrhstn
M

Share this page

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