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

 

[estezz]

i dont know what i do wrong

 

[kpark91]

I sent you an email with workbook attached named Book1
from my spambox email :D

For future curious people,

Sub kpark91July21()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Dim LR1&, LR2&, count&, i&, j&
    LR1 = Worksheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row
    LR2 = Worksheets("Sheet2").Range("A" & Rows.count).End(xlUp).Row
    count = 1
    
    For i = 1 To LR1
        Worksheets("Sheet1").Range("D" & i).Value = Worksheets("Sheet1").Range("A" & i).Value _
                                        & Worksheets("Sheet1").Range("B" & i).Value _
                                        & Worksheets("Sheet1").Range("C" & i).Value
    Next i
    For i = 1 To LR2
        Worksheets("Sheet2").Range("D" & i).Value = Worksheets("Sheet2").Range("A" & i).Value _
                                        & Worksheets("Sheet2").Range("B" & i).Value _
                                        & Worksheets("Sheet2").Range("C" & i).Value
    Next i
    
    
    
    For j = 1 To LR2
        For i = 1 To LR1
            If Worksheets("Sheet2").Range("D" & j).Value = Worksheets("Sheet1").Range("D" & i).Value Then
                Worksheets("Sheet2").Range("A" & j & ":C" & j).Copy Worksheets("Sheet3").Range("A" & count)
                count = count + 1
            End If
        Next i
    Next j
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Useless coding.. I know..

 

[markmzz]

Lets try again.

Try the following:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Assuming your data is in:

List1 - Sheet01 - range - A1:C7<o></o>
<o></o>
List2 - Sheet02 - range - E1:G7<o></o>
<o></o>
And that new data (List3 - the data duplicate) will be in the Sheet03.

Then do the following:<o></o>
<o></o>
Important: with Sheet03 Selected.

Click in Data tab, and, in the group Filter and Sort, click in Advanced.<o></o>
Then, type the following (in the indicated fields on the dialog box Advanced Filter):<o></o>
<o></o>

Mark the Select Box Copy to another local.

<o></o>
List range: Sheet01!A1:C7 <o></o>
Criteria range: Sheet02!E1:G7 <o></o>
Copy to: Sheet03!I1

Finally, click OK. <!-- / message -->

 

[estezz]

yes, its ok, your metod is function. thank you
BUT KPARK91 is Geunius, He did exactly what I needed

Lets try again.

Try the following:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Assuming your data is in:

List1 - Sheet01 - range - A1:C7<o></o>
<o></o>
List2 - Sheet02 - range - E1:G7<o></o>
<o></o>
And that new data (List3 - the data duplicate) will be in the Sheet03.

Then do the following:<o></o>
<o></o>
Important: with Sheet03 Selected.

Click in Data tab, and, in the group Filter and Sort, click in Advanced.<o></o>
Then, type the following (in the indicated fields on the dialog box Advanced Filter):<o></o>
<o></o>

Mark the Select Box Copy to another local.

<o></o>
List range: Sheet01!A1:C7 <o></o>
Criteria range: Sheet02!E1:G7 <o></o>
Copy to: Sheet03!I1

Finally, click OK. <!-- / message -->

 

[markmzz]

yes, its ok, your metod is function. thank you
BUT KPARK91 is Geunius, He did exactly what I needed

Estezz,

The important thing is that your problem has been resolved.

Markmzz

 

[angiee]

hi Kpark,

This really helped me, but I want the same with different workbooks. I have two different workboooks with 15 columns and 50k rows in one workbook and 10columns and 1000 rows in another workbook and only 1 column(partnumber) in common. So i want to pull two reports from this.

1. common partnumbered rows in to a different workbook as one report
2. I want to delete the common part numbered rows from first workbook and copy into a different workbook as another report.

Could you please help me with this as i am a new to Excel.

Thanks,
Angiee

I sent you an email with workbook attached named Book1
from my spambox email :D

For future curious people,

Sub kpark91July21()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
 
    Dim LR1&, LR2&, count&, i&, j&
    LR1 = Worksheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row
    LR2 = Worksheets("Sheet2").Range("A" & Rows.count).End(xlUp).Row
    count = 1
 
    For i = 1 To LR1
        Worksheets("Sheet1").Range("D" & i).Value = Worksheets("Sheet1").Range("A" & i).Value _
                                        & Worksheets("Sheet1").Range("B" & i).Value _
                                        & Worksheets("Sheet1").Range("C" & i).Value
    Next i
    For i = 1 To LR2
        Worksheets("Sheet2").Range("D" & i).Value = Worksheets("Sheet2").Range("A" & i).Value _
                                        & Worksheets("Sheet2").Range("B" & i).Value _
                                        & Worksheets("Sheet2").Range("C" & i).Value
    Next i
 
 
 
    For j = 1 To LR2
        For i = 1 To LR1
            If Worksheets("Sheet2").Range("D" & j).Value = Worksheets("Sheet1").Range("D" & i).Value Then
                Worksheets("Sheet2").Range("A" & j & ":C" & j).Copy Worksheets("Sheet3").Range("A" & count)
                count = count + 1
            End If
        Next i
    Next j
 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Useless coding.. I know..