How do I compare 2 lists and show duplicates as a new list? PLEASEEEE

[estezz]

I have 2 lists of several part numbers, text and price { each row has 3 cells}. I need to compare both lists and extract all the duplicate ROWS, copy them and place them in a new list.
Does anyone have a macro that can do this task that they are willing to share?
Please help me, for advice on how to create macros and I'll be grateful, i need help TODAY
Maria from Czech republic :-*
estezz@seznam.cz

 

[kpark91]

Is anything on column D?
What version of XL do you use?

Assuming u have ur part numbers in columnsA,B,C in Sheet1 and Sheet2
Algorithm#1:
Sort by C, then B, then A in both lists
Check A, then B, then C
Copy duplicates onto new list

Algorithm#2:
Concatentate A,B,C in both lists
Check each element from both lists
Copy duplicates onto new list


And it might be a good idea to erase your email or put it in a different format because if you don't, you will get tons of spam mails.

 

[estezz]

i have XL 2007
colum d is empty

please help me with it
Maria

for example

sheet one
A B C
1 158 martin 20/07/2010
2 178 martin 20/07/2010
3 148 pavel 20/07/2010
4 199 jan 20/07/2010
5 148 martin 20/07/2010
6 718 martin 20/07/2010
...... about 10000 rows

sheet two
1 108 mdartin 20/07/2010
2 198 martin 20/07/2010
3 1778 sad 20/07/2010
4 199 jan 20/07/2010
5 148 martin 20/07/2010
6 718 martin 20/07/2010
about 100 rows

and i need duplicates on the new sheet 3

can you help me?

 

[kpark91]

Sure I'll Try. Just hold on for like 5 minutes.

 

[mikerickson]

If both lists have the same headers, you could use Advanced Filter with one of the lists as the ListRange, the other list as the CriteriaRange and the CopyTo range of your choice.

 

[estezz]

but appear to list three if the same cell line on the sheet 1 and second .. same number, same name, same date

I am beginner in Excel, I do not understand, I need more detailed directions

 

[estezz]

What is meant by the same header? the same order as the columns?

 

[mikerickson]

The entries in the first row of both lists has to be the same, not nessesarily in the same order.

 

[kpark91]

If both lists have the same headers, you could use Advanced Filter with one of the lists as the ListRange, the other list as the CriteriaRange and the CopyTo range of your choice.

Oh... Right lol. there's an easier one...
Oh well.. I already made the code;;; so try

Sub kpark91July21()
    Dim LR1&, LR2&, count&, dataWS1 As Worksheet, dataWS2 As Worksheet, i&, j&, destWS As Worksheet
    Set dataWS1 = ThisWorkbook.Worksheets("Sheet 1")
    Set dataWS2 = ThisWorkbook.Worksheets("Sheet 2")
    Set destWS = ThisWorkbook.Worksheets("Sheet 3")
    LR1 = dataWS1.Range("A" & Rows.count).End(xlUp).Row
    LR2 = dataWS2.Range("A" & Rows.count).End(xlUp).Row
    concatenateABC dataWS1, LR1, dataWS2, LR2
    count = 1
    
    For j = 1 To LR2
        For i = 1 To LR1
            If dataWS2.Range("D" & j).Value = dataWS1.Range("D" & i).Value Then
                dataWS2.Range("A" & j & ":C" & j).Copy destWS.Range("A" & count)
                count = count + 1
            End If
        Next i
    Next j
End Sub
Function concatenateABC(dataWS1 As Worksheet, LR1 As Long, dataWS2 As Worksheet, LR2 As Long)
    Dim i&
    For i = 1 To LR1
        dataWS1.Range("D" & i).Value = dataWS1.Range("A" & i).Value _
                                        & dataWS1.Range("B" & i).Value _
                                        & dataWS1.Range("C" & i).Value
    Next i
    For i = 1 To LR2
        dataWS2.Range("D" & i).Value = dataWS2.Range("A" & i).Value _
                                        & dataWS2.Range("B" & i).Value _
                                        & dataWS2.Range("C" & i).Value
    Next i
End Function

 

[estezz]

uuuuu thank, i try it now....
wait please