I'm looking for a code that will allow us to compare rows of data and highlight which rows are different. We have 2 lists that we have merged into alphabetical order. We've tried conditional formatting but the problem is we're dealing with families and if there is more than one child in the family then the family name and address will appear more than once in the list. Ideally we want it to look at consecutive lines and do the matching that way as there should be 2 entries for every child - 1 from our list and 1 from the schools list.
Here's an example of the data:
PUPIL LAST NAME PUPIL FIRST NAME FAMILY LAST NAME ADDRESS
Adams Mikey 49 Tovill Rd
ADAMS MIKEY 49 TOVILL ROAD
ADAMS STACEY ADAMS 200 UPPER NORTH ROAD
Adams Stacey Murtagh 22 Kilbirnie Road
Adamson Chance Adamson PO Box 1234
ADAMSON CHANCE ADAMSON PO BOX 1234
Adamson Tim Adamson 123 Josh Road
ADAMSON TIM ADAMSON 1562 ST BENEDICTS ROAD
Aldenbrooke Hamish Aldenbrooke 60 Matthew Terrace
Agnew James Agnew 6 Durwin Crescent
It'd be great if we could get it to search in say column A, B and D looking for matches in 2 consecutive rows - highlighting any rows with entries that don't have match. We thought maybe an If statement but I'm not sure how to write one to search consecutive rows.
Sorry the example text doesn't translate well when posted on here. Just to clarify there are 4 columns shown. Pupil last name, Pupil first name, Family last name and Address. We'd like it to check that the pupils last and first names, and address match and single out those that don't.
rizvisa1
Posts4478Registration dateThursday January 28, 2010StatusContributorLast seenMay 5, 2022766 Jul 5, 2011 at 08:15 AM
Perhaps most efficient way could be that if you joing all 3 columns into one. It can be done on a separate sheet. So you have some thing like this
ADAMS|MIKEY|49 TOVILL ROAD
Then you can use either a countif or other means to identify the records
Yes, that makes sense. Ok, I've located a script and adapted it to my needs. For the most part it works well except for 2 little bugs I can't quite figure out. Here's the script
Sub JoinAndMerge()
Rows("1:1").Select
Do Until ActiveCell Is Nothing
On Error Resume Next
Dim outputText As String
Const delim = ""
For Each Cell In Selection
outputText = outputText & Cell.Value & delim
Next Cell
With Selection
.Clear
.Value = outputText
.Merge
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
End With
First problem is for some reason it's looping the whole thing and not just from the Do Until statement i.e. it keeps incorporating the first statement: Rows("1:1").Select as part of the loop. I've tried a Do while true statement as well with same result ???
The other problem is when it comes to do the merge part it brings up a message each time asking if I'm sure I want to complete the merge. How do I write the script to either ignore that message and continue, or to automatically Ok the message so it continues?
Jul 5, 2011 at 08:15 AM
ADAMS|MIKEY|49 TOVILL ROAD
Then you can use either a countif or other means to identify the records
Jul 5, 2011 at 06:43 PM
Sub JoinAndMerge()
Rows("1:1").Select
Do Until ActiveCell Is Nothing
On Error Resume Next
Dim outputText As String
Const delim = ""
For Each Cell In Selection
outputText = outputText & Cell.Value & delim
Next Cell
With Selection
.Clear
.Value = outputText
.Merge
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
End With
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Select
If ActiveCell = vbNullString Then Exit Do
Loop
End Sub
First problem is for some reason it's looping the whole thing and not just from the Do Until statement i.e. it keeps incorporating the first statement: Rows("1:1").Select as part of the loop. I've tried a Do while true statement as well with same result ???
The other problem is when it comes to do the merge part it brings up a message each time asking if I'm sure I want to complete the merge. How do I write the script to either ignore that message and continue, or to automatically Ok the message so it continues?
Jul 5, 2011 at 07:55 PM
Go to a new cell and type formula =A1&B1
I'm baffled as to why I couldn't find that easily on the net!
We've got this part all sorted now - many thanks for your advice :)