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 have contents on each sheet in rows but you want to counts to be inserted at row 2?

You will get count for each sample in Sheet1 when comparing to Sheet2. How you want to put this?

I think create posting few lines of examples and how you want the final result to be presented will help others to understand better.

 

[jskasango]

sampleData.xls
	A	B	C	D
1	JINA	KWAO	ANWANI	KUTOKA
2	KAS 138 Y	Mwanzo	Siri	Jina
3	KBA 043 P	Mwanzo	Siri	Jina
4	KAZ 616 C	Mwanzo	Siri	Jina
5	KBJ 078 G	Origin	sara	Jina
6	KBE 060 G	Origin	sara	Fdfd
7	KBD 419 N	Origin	sara	dsFD
8	KBP 075V	Origin	sara	Jina
9	KBP 077V	Mwanzo	sara	Jina
10	KAZ 616 C	Mwanzo	Siri	Jina
11	KBJ 078 G	Origin	ben	Jina
12	KBE 060 G	Mwanzo	ben	sdasdasd
13	KBD 419 N	Mwanzo	ben	Jina
14	KBP 075V	Mwanzo	ben	Jina
15	Thomas nganga	Kas	ben	Jina
16	Scholastica kasango	Mwanzo	Siri	Jina
17	Emelda nzau	fsdfSF	Siri	Jina
18	Blandina nthenya	Mwanzo	Siri	Jina
candidates

What I need is a VBA code that will run through the candidates and check in the masterfile and come up with two sheets "FOUND" and "NOT FOUND" copying the entire row of the candidates sheet.

 

[Zot]

sampleData.xls
	A	B	C	D
1	JINA	KWAO	ANWANI	KUTOKA
2	KAS 138 Y	Mwanzo	Siri	Jina
3	KBA 043 P	Mwanzo	Siri	Jina
4	KAZ 616 C	Mwanzo	Siri	Jina
5	KBJ 078 G	Origin	sara	Jina
6	KBE 060 G	Origin	sara	Fdfd
7	KBD 419 N	Origin	sara	dsFD
8	KBP 075V	Origin	sara	Jina
9	KBP 077V	Mwanzo	sara	Jina
10	KAZ 616 C	Mwanzo	Siri	Jina
11	KBJ 078 G	Origin	ben	Jina
12	KBE 060 G	Mwanzo	ben	sdasdasd
13	KBD 419 N	Mwanzo	ben	Jina
14	KBP 075V	Mwanzo	ben	Jina
15	Thomas nganga	Kas	ben	Jina
16	Scholastica kasango	Mwanzo	Siri	Jina
17	Emelda nzau	fsdfSF	Siri	Jina
18	Blandina nthenya	Mwanzo	Siri	Jina
candidates

What I need is a VBA code that will run through the candidates and check in the masterfile and come up with two sheets "FOUND" and "NOT FOUND" copying the entire row of the candidates sheet.

It is not cleat still.

Copy to where? Another sheet?

You mentioned that the content may appear more than one. By content, you meant just column A or the whole column A to D duplicated?

If duplicated, then what to do? Ignore? You also want to count how many duplication and insert in 2nd row? You have already copy the matching into result sheet (I presumed) and you have a list of matching contents, how do I put in 2nd row? This is why you need to show how your output looks like.

 

[jskasango]

Thank you for your quick response. The search column is A. Also, the search column in MasterSheet is A. If found the entire row should be copied from Sheet "Candidates" to Sheet "Found", if not found, the entire row should be copied from Sheet "Candidates" to Sheet "NotFound".
The outputs should look like this:-

These two snippets are just an example of what I wish to achieve. I have done them manually so they are not accurate. Duplicates are allowed.

 

[Zot]

I see that you cannot just compare column A because other columns can be different. So, I'm comparing all columns

Try this

Sub Segregate()

Dim strData As String
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

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
    strData = cell & cell.Offset(, 1) & cell.Offset(, 2) & cell.Offset(, 3)
    If Not dictData.Exists(strData) Then dictData.Add strData, Nothing
Next
    
For Each cell In rngData
    strData = cell & cell.Offset(, 1) & cell.Offset(, 2) & cell.Offset(, 3)
    If dictData.Exists(strData) 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

End Sub

 

[jskasango]

I see that you cannot just compare column A because other columns can be different. So, I'm comparing all columns

Try this

Sub Segregate()

Dim strData As String
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

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
    strData = cell & cell.Offset(, 1) & cell.Offset(, 2) & cell.Offset(, 3)
    If Not dictData.Exists(strData) Then dictData.Add strData, Nothing
Next
   
For Each cell In rngData
    strData = cell & cell.Offset(, 1) & cell.Offset(, 2) & cell.Offset(, 3)
    If dictData.Exists(strData) 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

End Sub

Let me test it. My Master file has only one column. I believe it will work.

 

[jskasango]

The search should be only on column A of Candidates and Master, if found or NOT found, copy entire row to the appropriate sheet (ie Sheet(FOUND) or Sheet(NOTFOUND). thank you very much for your efforts.

 

[Zot]

The search should be only on column A of Candidates and Master, if found or NOT found, copy entire row to the appropriate sheet (ie Sheet(FOUND) or Sheet(NOTFOUND). thank you very much for your efforts.

Let's see if I understood you right.

You are just comparing column A in both Candidates and Master list. You want to go through Candidates list and if there is match in Master, you want you copy the whole matched row on Candidates to Found sheet, otherwise just copy the whole row into NotFound sheet. Is this correct?

I found that there are similar strings in Master (column A) such as KBE 060 G which appears on row 6 and row 11, but the rest contents on their corresponding rows are not the same. So, that was why I compared all the contents instead of just column A. If you use LookUp or Find functions, it will match twice on row 6 and 11 and matched Candidates row will be copied twice on Found sheet. Please clarify.

 

[jskasango]

Let's see if I understood you right.

You are just comparing column A in both Candidates and Master list. You want to go through Candidates list and if there is match in Master, you want you copy the whole matched row on Candidates to Found sheet, otherwise just copy the whole row into NotFound sheet. Is this correct?

I found that there are similar strings in Master (column A) such as KBE 060 G which appears on row 6 and row 11, but the rest contents on their corresponding rows are not the same. So, that was why I compared all the contents instead of just column A. If you use LookUp or Find functions, it will always matched on row 6. The row 11 would be useless data I suppose. Please clarify

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