Finding Duplicate Values

[starpupil]

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

Sheet 1</SPAN>	Sheet 2</SPAN>	Sheet 3</SPAN>
Column A </SPAN>	Column A </SPAN>	Column A </SPAN>
Adam </SPAN>	Logan	Adam</SPAN>
Shawn </SPAN>	Jane	Shawn</SPAN>
Logan	Ajay	Jose</SPAN>
Jane	Puta	Sarah</SPAN>
Sarah </SPAN>	Gandalf	Peter</SPAN>
Ajay	Bruce </SPAN>	Pedro</SPAN>
Jose </SPAN>	Clark </SPAN>	Bruce</SPAN>
Pedro </SPAN>	Clark</SPAN>	Magix</SPAN>
Puta	Tony
Magix </SPAN>
Gandalf
Peter</SPAN>
Tony

<TBODY>
</TBODY>

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

 

[x_ampl1]

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

 

[Robert Mika]

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

Sheet 1</SPAN>	Sheet 2</SPAN>	Sheet 3</SPAN>
Column A </SPAN>	Column A </SPAN>	Column A </SPAN>
Adam </SPAN>	Logan	Adam</SPAN>
Shawn </SPAN>	Jane	Shawn</SPAN>
Logan	Ajay	Jose</SPAN>
Jane	Puta	Sarah</SPAN>
Sarah </SPAN>	Gandalf	Peter</SPAN>
Ajay	Bruce </SPAN>	Pedro</SPAN>
Jose </SPAN>	Clark </SPAN>	Bruce</SPAN>
Pedro </SPAN>	Clark</SPAN>	Magix</SPAN>
Puta	Tony
Magix </SPAN>
Gandalf
Peter</SPAN>
Tony

<TBODY>
</TBODY>

Now to be precise, there are two column in each sheet having the Values.

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?

 

[starpupil]

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

 

[Legacy 234512]

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

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

 

[starpupil]

Hi Robert,
Apologies, I tried to copy paste from excel think something went wrong.

Sheet 1</SPAN>	Sheet 2</SPAN>	Sheet 3</SPAN>
Column A </SPAN>	Column A </SPAN>	Column A </SPAN>
Adam </SPAN>	Logan	Adam</SPAN>
Shawn </SPAN>	Jane	Shawn</SPAN>
Logan	Ajay	Jose</SPAN>
Jane	Puta	Sarah</SPAN>
Sarah </SPAN>	Gandalf	Peter</SPAN>
Ajay	Bruce </SPAN>	Pedro</SPAN>
Jose </SPAN>	Clark </SPAN>	Bruce</SPAN>
Pedro </SPAN>	Tony	Magix</SPAN>
Puta		Clark</SPAN>
Magix </SPAN>
Gandalf
Peter</SPAN>
Tony

<TBODY>
</TBODY><COLGROUP><COL span=3></COLGROUP>

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

 

[mirabeau]

Hi Robert,
Apologies, I tried to copy paste from excel think something went wrong.

Sheet 1	Sheet 2	Sheet 3
Column A	Column A	Column A
Adam	Logan	Adam
Shawn	Jane	Shawn
Logan	Ajay	Jose
Jane	Puta	Sarah
Sarah	Gandalf	Peter
Ajay	Bruce	Pedro
Jose	Clark	Bruce
Pedro	Tony	Magix
Puta		Clark
Magix
Gandalf
Peter
Tony

<tbody>
</tbody>

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

try

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

 

[starpupil]

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

 

[mirabeau]

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

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.

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