VBA to Compare 2 sheets and count and color color similarities

[jskasango]

Hi ALL, I have an assignment to compare all of Sheet1 contents with Sheet2 contents (more than 30,000 rows) then count and color similarities in Sheet1.
Both sheets have a header row and I want the counts to be inserted at row2 of each column in sheet1 when the search ends.
Some contents in sheet1 appear more than once and each find should be treated as a new find.
Please help with a VBA code that I can use.
TIA
K

 

[Zot]

You are very correct. But what is in row 11 is not useless. Duplicates are allowed.

I edited my reply a bit late. My assumption was wrong. Please go through it again

 

[Zot]

You are only copying the candidate list. Therefore the row 11 on Master list is never discovered if I start searching from first row again and again, right?

 

[jskasango]

You are only copying the candidate list. Therefore the row 11 on Master list is never discovered if I start searching from first row again and again, right?

Correct.

 

[Zot]

Try this

Sub Segregate()

Dim n As Long
Dim cell As Range, rngData As Range, rngMaster As Range
Dim dictData As Object
Dim wsMaster As Worksheet, wsCand As Worksheet, wsFound As Worksheet, wsNotFound As Worksheet

Application.ScreenUpdating = False

Set wsMaster = ActiveWorkbook.Sheets("Master")
Set wsCand = ActiveWorkbook.Sheets("Candidates")
Set wsFound = ActiveWorkbook.Sheets("Found")
Set wsNotFound = ActiveWorkbook.Sheets("NotFound")

Set dictData = CreateObject("Scripting.Dictionary")
Set rngData = wsCand.Range("A2", wsCand.Cells(Rows.Count, "A").End(xlUp))
Set rngMaster = wsMaster.Range("A2", wsMaster.Cells(Rows.Count, "A").End(xlUp))

For Each cell In rngMaster
    If Not dictData.Exists(cell.Value) Then dictData.Add cell.Value, Nothing
Next
    
For Each cell In rngData
    If dictData.Exists(cell.Value) Then
        n = wsFound.Range("A" & Rows.Count).End(xlUp).Row + 1
        wsCand.Range("A" & cell.Row, "D" & cell.Row).Copy wsFound.Range("A" & n)
    Else
        n = wsNotFound.Range("A" & Rows.Count).End(xlUp).Row + 1
        wsCand.Range("A" & cell.Row, "D" & cell.Row).Copy wsNotFound.Range("A" & n)
    End If
Next

Application.ScreenUpdating = True

End Sub

 

[jskasango]

Great! Can we pick the headers from Row1 of candidates? The total of Found+NotFound should be the sum of Candidates, Right?

 

[Zot]

Great! Can we pick the headers from Row1 of candidates? The total of Found+NotFound should be the sum of Candidates, Right?

Change
Set rngData = wsCand.Range("A2", wsCand.Cells(Rows.Count, "A").End(xlUp))
to
Set rngData = wsCand.Range("A1", wsCand.Cells(Rows.Count, "A").End(xlUp))

It should be.

 

[jskasango]

Hello @Zot , your code has really helped me organize my assignment extremely well. Thank you very much.
I was thinking, is it possible to have the Sheet(Master) saved in a folder, and then have the code open it, use it and close it.
This way, I would avoid having the same MasterSheet in my very many different Candidates lists. If that is possible, then my workbooks will only have 3 sheets ie Candidates,Found and NotFound. It will also reduce the file sizes. FYI, my master has over 34,000 rows!

 

[Zot]

Hello @Zot , your code has really helped me organize my assignment extremely well. Thank you very much.
I was thinking, is it possible to have the Sheet(Master) saved in a folder, and then have the code open it, use it and close it.
This way, I would avoid having the same MasterSheet in my very many different Candidates lists. If that is possible, then my workbooks will only have 3 sheets ie Candidates,Found and NotFound. It will also reduce the file sizes. FYI, my master has over 34,000 rows!

Let me rephrase what you are trying to do here.

You want the Master sheet saved in a folder which means you will have another workbook with just Master sheet.
The workbook with sheets Candidate, Found, NotFound will be the one with macro I suppose.

Once the program runs, a Master workbook (which I'd call it) will be created and saved in a specific folder.

Main question is how you get the Candidate list. If you are copying the list from somewhere into this workbook, then you can have a fix workbook to store your macro. Otherwise you will have to create new macro workbook each time.

Each time Master is created it will be totally new Master. So, unless you move the previous Master workbook out of the directory, then you will have duplicated file name.

All these need to be addressed.

 

[jskasango]

Let me rephrase what you are trying to do here.

You want the Master sheet saved in a folder which means you will have another workbook with just Master sheet.
The workbook with sheets Candidate, Found, NotFound will be the one with macro I suppose.

Once the program runs, a Master workbook (which I'd call it) will be created and saved in a specific folder.

Main question is how you get the Candidate list. If you are copying the list from somewhere into this workbook, then you can have a fix workbook to store your macro. Otherwise you will have to create new macro workbook each time.

Each time Master is created it will be totally new Master. So, unless you move the previous Master workbook out of the directory, then you will have duplicated file name.

All these need to be addressed.

You are partly right except that the masterSheet is going to become a MasterWorkbook stored in a folder. The sheets candidates, Found and NotFound remain as my working lists with varying names for the workbooks. Presently (thanks to you) every time I open any of my lists, I have to copy the masterSheet into the list, then I run the code. The masterSheet is the same throughout, that’s why I suggest we make it a workbook, then have the code open it in the background, do the search and populate the Found and NotFound sheets, then close the MasterWorkbook.

 

[jskasango]

You are partly right except that the masterSheet is going to become a MasterWorkbook stored in a folder. The sheets candidates, Found and NotFound remain as my working lists with varying names for the workbooks. Presently (thanks to you) every time I open any of my lists, I have to copy the masterSheet into the list, then I run the code. The masterSheet is the same throughout, that’s why I suggest we make it a workbook, then have the code open it in the background, do the search and populate the Found and NotFound sheets, then close the MasterWorkbook.

Maybe I should then place the code in personal workbook?