How to find un match in excel from VBA

[aajaydee]

I have a spreadsheet and two of the columns are called SiteDeveloper and WFDeveloper

SiteDeveloper WFDeveloper
ID 2658
1. Cortright, John Cortright, John
2. Goldberg, Glenn Berberich, Joel P
3. Linden, Evan McMullin, David
4. McMullin, David

ID 2659
1. Atienza, Gerard Atienza, Gerard
2. Priese, Fred Priese, Fred
3. Westberry, Carey Schroer, Brian
4. Thompson, Martin


I’m getting these values from two different database ( database A, DatabaseB) and I’m matching against Id

(e.g id from database A.table1.id =databaseb.table1.id)

A.Table.id can any number of SiteDeveloper
As B.table1.id can have any number of WFDeveloper
Mean I’m doing query in Access

Which I’m getting successfully and I’m placing on excel spread sheet


Because this is more than 20,000 records
Is there any way I can put color code red where data is not matching ?


Like
In ID 2658
Like


2. Goldberg, Glenn Berberich, Joel P
3 Linden, Evan

And in id 2659


3. Westberry, Carey Schroer, Brian
4. Thompson, Martin

In Red color because they are not matching



Using VBA in excel. I know I have to use some sort of loop, but I have not any program in VBA, so a small code will help me. Thanks in advnace

 

[mikej336]

Here is some old code of mine that does something simular. Except I removed duplicates and did some coloring. Might give you some Ideas.

with my test data

NATIONAL INS GALVESTON 0000047
NATIONAL INS GALVESTON 0000047
LIMOUSINE VANCE 0000062
TOWER CORPORATION ATLANTA 0000094

Private Sub CommandButton5_Click()
'***************************************************
'*
'* Identify Duplacates
'*
'*
'***************************************************
Dim ACell, FirstCell As Range
Dim oldacct, newacct As String
Dim oldname, newname As String
Dim poldname, pnewname As String
Dim pnamel As Integer

Application.ScreenUpdating = False

Worksheets("Sheet1").Columns("A").Replace _
What:=",", Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=False

Dim RowIndex As Integer

RowIndex = 9

Set ACell = Range("A" & Trim(Str(RowIndex)))
ACell.Select

oldacct = Trim(Range("C" & Trim(Str(RowIndex))).Value)
oldname = Trim(Range("a" & Trim(Str(RowIndex))).Value)
pnamel = Len(Trim(oldname))

RowIndex = RowIndex + 1
Set ACell = Range("a" & Trim(Str(RowIndex)))

Do While Not IsEmpty(ACell)
newacct = Trim(Range("C" & Trim(Str(RowIndex))).Value)
newname = Trim(Range("A" & Trim(Str(RowIndex))).Value)


If oldacct = newacct Then
ACell.Select
Range("C" & Trim(Str(RowIndex)) & ":C" & Trim(Str(RowIndex - 1))).Font.Color = vbRed
Range("C" & Trim(Str(RowIndex)) & ":C" & Trim(Str(RowIndex - 1))).Font.Bold = True
End If
If Left(newname, pnamel) = Trim(oldname) Then
ACell.Select
Range("a" & Trim(Str(RowIndex)) & ":a" & Trim(Str(RowIndex - 1))).Font.Color = RGB(128, 0, 128)
Range("a" & Trim(Str(RowIndex)) & ":a" & Trim(Str(RowIndex - 1))).Font.Bold = True
End If
If oldname = newname Then
ACell.Select
Range("a" & Trim(Str(RowIndex)) & ":a" & Trim(Str(RowIndex - 1))).Font.Color = vbBlue
Range("a" & Trim(Str(RowIndex)) & ":a" & Trim(Str(RowIndex - 1))).Font.Bold = True
ynfound = True
End If



oldacct = Trim(Range("C" & Trim(Str(RowIndex))).Value)
oldname = Trim(Range("A" & Trim(Str(RowIndex))).Value)
pnamel = Len(oldname)

RowIndex = RowIndex + 1
Set ACell = Range("A" & Trim(Str(RowIndex)))
Loop

Range("D" & Trim(Str(RowIndex))).Formula = "=sumif(D9" & Trim(Str(RowIndex - 1)) & "," & Chr(34) & ">1" & Chr(34) & ")"
Range("D" & Trim(Str(RowIndex))).Font.Bold = True
Range("A" & Trim(Str(RowIndex))).Value = "Number Combined"
Range("A" & Trim(Str(RowIndex))).Font.Bold = True

Range("a9").Select


GoSub EndIt

'*************************************************************************************
EndIt:
Application.ScreenUpdating = True
End Sub



Your problem is going to be like your McMullin, David example. You will need a loop with in a loop and read all the names in the first column then compare that with each name in the second.

ID 2658
3. Linden, Evan McMullin, David
4. McMullin, David

One quick way to do that is to concanticante all your first column in to a variable then do and instr to find a match

Simplified example

test="Cortright, John~Goldberg, Glenn~Linden, Evan~McMullin, David"

instr(1,test,"Glenn~Linden",1)

Good Luck

Uncle Mike

 

[Gavona]

Why not create helper columns that concatenate name and ID for sitedeveloper and for WFDeveloper. Then you just look for an exact match in the entire column. The match would be a condition within a conditional format.

Whilst this could be done from the data as you describe it, if you wre to re-specified the info from the databases you could make it even simpler (you want the ID and name on every row and don't want the records numbered).



Gavin

 

[mikej336]

Well, technically if all you wanted to do was find out who did not match, you could append both tables to a new table. Then do a group query on that and count groupings. Then only take the ones with 1.

Both is a combined column name with both SiteDeveloper and WFDeveloper combined into a single column.

If it is necessary to know which table they were from you could just add a column to tell you which table they came from when you import it.

ex
ID Both table
2658 Cortright, John WFDeveloper
2658 Berberich, Joel P WFDeveloper
2658 McMullin, David WFDeveloper
2659 Atienza, Gerard WFDeveloper
2659 Priese, Fred WFDeveloper
2659 Schroer, Brian WFDeveloper
2658 Cortright, John SiteDeveloper
2658 Goldberg, Glenn SiteDeveloper
2658 Linden, Evan SiteDeveloper
2658 McMullin, David SiteDeveloper
2659 Atienza, Gerard SiteDeveloper
2659 Priese, Fred SiteDeveloper
2659 Westberry, Carey SiteDeveloper
2659 Thompson, Martin SiteDeveloper


SELECT new.ID, new.Both, Count(new.Both) AS CountOfBoth
FROM new
GROUP BY new.ID, new.Both
HAVING (((Count(new.Both))=1));

 

[mikej336]

Then just do a join to find which table they are in.

Ex

SELECT Query1.ID, new.table, Query1.Both
FROM Query1 INNER JOIN new ON (Query1.Both = new.Both) AND (Query1.ID = new.ID)
ORDER BY new.table, Query1.Both;

and your done.

Uncle Mike