How do I compare 2 workbooks columns and show duplicates as a new list?

I have two different workbooks with approx 15 columns and 50k rows in one work开发者_JAVA技巧book and 10columns and 1000 rows in another workbook and only 2 columns(partnumber, changelevel)are in common. So I want to pull two reports from these two workbooks.

1. Records with common partnumber & changelevel in to a different workbook as one report.
2. I want to delete the common part number & changelevel records from first workbook and copy all the remaining records into a different workbook as another report.

Angiee . . .

You have a couple of questions that will need to answered before anyone can help you with this.

1. Is this a one time deal where you are trying to clean up data and come up with a new starting point and you won't need to run this process over and over again?

2. Can we assume that the data rows are not in the same Worksheet row in both Workbooks?

If the answer to both questions is YES then I would have to say that Excel is decidedly NOT the Office Application that you should be using. I would suggest that you import both Workbooks into an Access Database as separate tables. That way you can use SQL to perform the matches and lookups with little or no code needed. You can easily export the query results back to an Excel Workbook once you have the results you want. You could probably have your answer in an hour. If you go with this option you can also link the Worksheets into the Access DB and avoid importing them. It won't be as fast but it will work.

Otherwise, if you are stuck with Excel then you either have a significant amount of code to write that pretty much consists of looping through all of the records in one Workbook, looking up the values in the other Workbook then generating the output in still more Workbooks. . . or . . . you could try copying the Worksheet with the 1000 records into the other Workbook and then using the Worksheet Functions VLOOKUP and/or HLOOKUP to create a lot of formulas. (I can't in good conscience endorse this second approach but if you are not very experienced at VBA then it may be the better approach for you).

Either way you go with the Excel solution there'll be a lot of work invloved.

If you have any specific coding issues then you are in the right spot. But you will need to pick an approach first.

Good luck!

Doug

The question is an easy one to answer, the problem comes into two fold.

1. How fast is your computer?
2. How often do you need to run this code?

The reason I ask these questions are because to run any code on 50,000 lines no matter how small the code to actually make this work is... you need to have a computer that is rather robust, otherwise this code is going to stall your computer, or at least excel for a good minute to three minutes+ depending on how fast and how much memory you actually have.

Without seeing your workbook you need some very simple formulas, but what you are going to have to do is add another line into the workbook. In Column P, you need a verification formula. This formula is simple, but it will depend on how many points of reference you require.

=COUNTIFS('Sheet2'!$A:$A,$A3,'Sheet2'!$E:$E,$E3)

From there you can see what are duplicates or not. You can then have in column Q a formula like this:

=IF($P3,"SAME","")

And it will tell you if the data is the same or not. Basically it says if there is anything but 0 in the cell P3 it will say there is something the same, otherwise it's not.

From there you need a code sort of like this:

Sub Update_TNOOR()
    Dim wsS1 As Worksheet
    Dim wsS2 As Worksheet
    Dim lastrow As Long, fstcell As Long

    Set wsS1 = Sheets("Sheet1")
    Set wsS2 = Sheets("Sheet2")

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    With wsS1
        wsS1.Columns("P:Q").ClearContents
        ThisWorkbook.Sheets("Sheet1").Cells(1, 16).Value = “=COUNTIFS('Sheet2'!$A:$A,$A3,'Sheet2'!$E:$E,$E3)"
        ThisWorkbook.Sheets("Sheet1").Cells(1, 17).Value = “=IF($P3,"Same",””””)"
    wsS2.Columns("P:Q").ClearContents
        ThisWorkbook.Sheets("Sheet2").Cells(1, 16).Value = “=COUNTIFS('Sheet1'!$A:$A,$A3,'Sheet1'!$E:$E,$E3)"
        ThisWorkbook.Sheets("Sheet2").Cells(1, 17).Value = “=IF($P3,"Same",”Different”)"

    End With


    With Intersect(wsS1, wsS1.Columns("Q"))
        .AutoFilter 1, "<>Same"
        With Intersect(.Offset(2).EntireRow, .Parent.Range("B:Q"))
            .EntireRow.Delete
        End With
        .AutoFilter
    End With

    'Blow away rows that are useless
    lastrow = wsS2.Range("A2").End(xlDown).Row
    wsS2.Range("P1:Q1").Copy wsS2.Range("P2:Q" & lastrow)

    With Intersect(wsS2.UsedRange, wsS2.Columns("Q"))
        wsS2.Range("P:Q").Calculate
        .AutoFilter 1, "<>Different"
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    With wsS2
        lastrow = wsS2.Range("A1").End(xlDown).Row
        Intersect(.UsedRange, .Range("A1:N" & lastrow)).Copy wsS1.Cells(Rows.Count, "B").End(xlUp).Offset(1)
    End With


    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

this should get you on your way... if I read what you are attempting to do correctly.

As people have said though, what you want done can be done in excel, should it, I don't know... people here seem to think not, but if you need to use excel, this should get you on your way.

Again, I don't know what your workbook looks like, so I hope this helps. This compares data and merges it into the first sheet. IT won't do everything you want to do... but this should get you on your way.