Solved: Just How?

[Wilkens]

I have searched high and low for an answer to this question but nothing so far.

Basically I need a VBA code to do a couple of IF functions and then if all is correct to then input certain data from the same row into a specific column where there is no data already. Only problem is i have about 14,000 rows of data and some have data in the colomn i need data being input to.... I bet that made no sense but i will try explain it a bit better...

Make shift Excel Sheet:
Data from 1st database | Data from 2nd Database
__A__B____C_________|_D___E______F
1_ID_Name_Ref number_|_ID__Name___Ref Number
____________________|________________________
2_001_Bob__134568___|_001_Bob___134568
3_002_Henry_________|_002_Henry__145845
4_005_Jack_ 215848___|_005_Jack___215848
5_007_Wiliam_215485__|_007_William_215486
6_012_Peter__________|_012_Peter__321584
7_254_John__542158___|_254_John__542158

So column 'A' and 'D' are the main link as each person has an ID number and they can easily be linked. Column 'B' and 'E' have to match and if they don't match it somehow needs to be flagged up for example row 5 'Wiliam' and 'William' are not the same so would need to be flagged up somehow. Now the tricky part, column 'C' and 'F': I don't know if this is possible but if column 'A' and 'D' match 100%, and 'B' and 'E' match 100%, and there is no data within column 'C' can columns 'F' data be matched up and be placed in column C?

<-- Been doing this for days now!

If anyone can help thank you in advance

[Wilkens]

I'm not sure if its any easier but i have done an 'IF' formula on the worksheet itself to work out the problems of matching up the columns:

This is in a cell next to the information for example cell 'H2'
=IF(C2=F2,"",IF(AND(A2=D2,FIND(B2,(E2))),F2,"N"))

This then gives me 3 different answers:
1. If everything is right and i need to do nothing it comes up with " ".
2. If everything matches but there is no number in column 'C' it comes up with the number i need in 'H'
3. If something doesn't match up i get the '#Value' come up.

I'm just wondering if a VBA could be made to use the number from this to simply put in the 'Ref number' in column 'C', but not input in column C if either the number is already in column 'C' or '#Value' comes up.

May have just made things more confusing but hopefully not.....

[georgiboy]

Welcome to the forum

Try this:
[VBA]Sub MatchData()
Dim rCell As Range, EndData As Long

EndData = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

For Each rCell In Sheet1.Range("A2:A" & EndData).Cells

If rCell.Offset(, 1).Value <> rCell.Offset(, 4).Value Then
Range(rCell, rCell.Offset(, 5)).Interior.ColorIndex = 3 'to point out diff name
End If

If rCell.Value = rCell.Offset(, 3).Value Then
If rCell.Offset(, 1).Value = rCell.Offset(, 4).Value Then
If rCell.Offset(, 2).Value = "" Then
rCell.Offset(, 2).Value = rCell.Offset(, 5).Value 'to fill blank value
End If
End If
End If

Next rCell

End Sub[/VBA]

Hope this helps

[Wilkens]

Hey Georgiboy,

That is perfect, there is only one error due to my imcompitence ! With the name columns from 1 database they are all uppercase and from the other database they are proper case.

Is there any way of inbedding another bid of code into the VBA to make it check the name regardless of what type of case it is, or change both columns to all Uppercase? (There is a reason not to change it to propercase as some names are for example McDonald and if it was to be changed to proper case it would be put to Mcdonald which would make the VBA code make everything go red :P)

Thanks for the VBA code before though 100% brilliant!

[georgiboy]

[VBA]Sub MatchData()
Dim rCell As Range, EndData As Long

EndData = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

For Each rCell In Sheet1.Range("A2:A" & EndData).Cells

If UCase(rCell.Offset(, 1).Value) <> UCase(rCell.Offset(, 4).Value) Then
Range(rCell, rCell.Offset(, 5)).Interior.ColorIndex = 3 'to point out diff name
End If

If rCell.Value = rCell.Offset(, 3).Value Then
If UCase(rCell.Offset(, 1).Value) = UCase(rCell.Offset(, 4).Value) Then
If rCell.Offset(, 2).Value = "" Then
rCell.Offset(, 2).Value = rCell.Offset(, 5).Value 'to fill blank value
End If
End If
End If

Next rCell

End Sub[/VBA]

[Wilkens]

Thanks again for the help, but the error is still coming up. Looking at the differences between your two VBA's have you put the UCase bits on the number columns instead of the Name columns? I'm a begginner at this sort of stuff so i could be very wrong.

Thanks again

[georgiboy]

hmmmmm no they are in the right place... Maybe, if this data is imported from different systems i would guess from my experience tha it brings trailing spaces in the text fields. Try this:
[VBA]Sub MatchData()
Dim rCell As Range, EndData As Long

EndData = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

For Each rCell In Sheet1.Range("A2:A" & EndData).Cells

If Trim(UCase(rCell.Offset(, 1).Value)) <> Trim(UCase(rCell.Offset(, 4).Value)) Then
Range(rCell, rCell.Offset(, 5)).Interior.ColorIndex = 3 'to point out diff name
End If

If rCell.Value = rCell.Offset(, 3).Value Then
If Trim(UCase(rCell.Offset(, 1).Value)) = Trim(UCase(rCell.Offset(, 4).Value)) Then
If rCell.Offset(, 2).Value = "" Then
rCell.Offset(, 2).Value = rCell.Offset(, 5).Value 'to fill blank value
End If
End If
End If

Next rCell

End Sub[/VBA]

[mdmackillop]

Is there any way of inbedding another bid of code into the VBA to make it check the name regardless of what type of case it is

Head your code with [vba]Option Compare Text[/vba]

Imported data often has the non-printing character Chr(160). Trim will not remove this. Try
[vba]
Cells.Replace Chr(160), ""

[/vba]

[georgiboy]

Thanks for the info, I will try to remember it

[Wilkens]

Thank you for your hard work on this but it still isn't working so instead of typing like an idiot I'm attaching a spreadsheet which is an example of what i have. Hope it will make more sense when you see the spreadsheet.

Thank you

(Sorry for being such a pain)

[mdmackillop]

Your sample is comparing Names with Numbers, and appears to compare First Name with Names. Can you tidy things up so we can see the real problem?

[Wilkens]

I do apologise for the rubbish details, I have now changed the attachment. Hopefully it makes more sense.

I did write it in a formula but i couldn't then enter the answer from this into the column i needed without causing some problems with the data already in the cells.

=IF(D4=F4,"",IF(AND(A4=E4,FIND(C4,UPPER(G4))),F4,"N"))

So basically this but fill in column 'D' with the number from column 'F' if there isn't a number in 'D' already.

Reading it over and over again to try and make it more simple but just sounds more confusing every time i read it! Sorry

[Bob Phillips]

[vba]


Public Sub ProcessData()
Dim lastrow As Long
Dim matchrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Range("A1").CurrentRegion.Rows.Count
For i = 2 To lastrow

matchrow = 0
On Error Resume Next
matchrow = Application.Match(.Cells(i, "A").Value, .Columns("E"), 0)
On Error GoTo 0
If matchrow > 0 Then

If LCase(Left$(.Cells(i, "B").Value, 1) & " " & .Cells(i, "C").Value) = LCase(.Cells(i, "G").Value) Then

.Cells(i, "D").Value = .Cells(i, "F").Value
Else

With .Cells(i, "B").Resize(, 2)

.Font.ColorIndex = 3
.Font.Bold = True
End With
End If
End If
Next i
End With
End Sub
[/vba]

[Wilkens]

[vba]


Public Sub ProcessData()
Dim lastrow As Long
Dim matchrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Range("A1").CurrentRegion.Rows.Count
For i = 2 To lastrow

matchrow = 0
On Error Resume Next
matchrow = Application.Match(.Cells(i, "A").Value, .Columns("E"), 0)
On Error GoTo 0
If matchrow > 0 Then

If LCase(Left$(.Cells(i, "B").Value, 1) & " " & .Cells(i, "C").Value) = LCase(.Cells(i, "G").Value) Then

.Cells(i, "D").Value = .Cells(i, "F").Value
Else

With .Cells(i, "B").Resize(, 2)

.Font.ColorIndex = 3
.Font.Bold = True
End With
End If
End If
Next i
End With
End Sub
[/vba]

This vba code is brilliant! It works with most of the lines but for some reason won't work on any with a first name, it only works with people that have a first name initial in column 'G'. For example it will work with someone named D Beckham but won't work with someone that has David Beckham...... Is there a way of skipping the first name and just match up with the last name if its a problem? Or is this problem solvable?

Thank you to all three of you for helping me on this! Would be so lost without you guys!

[Bob Phillips]

Do you mean like this?

[vba]

Public Sub ProcessData()
Dim lastrow As Long
Dim matchrow As Long
Dim i As Long

With ActiveSheet

lastrow = .Range("A1").CurrentRegion.Rows.Count
For i = 2 To lastrow

matchrow = 0
On Error Resume Next
matchrow = Application.Match(.Cells(i, "A").Value, .Columns("E"), 0)
On Error GoTo 0
If matchrow > 0 Then

If LCase(Left$(.Cells(i, "B").Value, 1) & " " & .Cells(i, "C").Value) = LCase(.Cells(i, "G").Value) Or _
LCase(.Cells(i, "B").Value & " " & .Cells(i, "C").Value) = LCase(.Cells(i, "G").Value) Then

.Cells(i, "D").Value = .Cells(i, "F").Value
Else

With .Cells(i, "B").Resize(, 2)

.Font.ColorIndex = 3
.Font.Bold = True
End With
End If
End If
Next i
End With
End Sub
[/vba]

[Wilkens]

Thank you!!! That seems to have done the trick Thanks for your help much appreciated!