comparing data

  • Thread starter Thread starter Maurice Samueks
  • Start date Start date
M

Maurice Samueks

I was wondering is there a way to compare data from 2 columns in a side
by side comparison. I have a large list of numbers and what I want to
do is copy another list of numbers into the spreadsheet. But I want the
numbers in cell A1 and B1 to be equal if they are is not a match then
leave cell b1 empty. I want that to continue all the way through the
entire worksheet.
 
Maurice,

Do you want to end up with 2 separate columns, or are you wanting to
merge the 2 lists into 1 list, without duplications?

Dave
 
I want 2 side by side columns. With the numbers that are equal right
next to each other.
 
Maurice,

Give this a try. It assumes 2 worksheets named List1 and List2, the
list of numbers to be compared is in column A of both sheets. Sheet
List1 is the updated sheet. I don't want to claim credit for this
program, it's a modification of Rommert Casimir's reply to "Column
Compare and Update" Feb 16, 2005 in comp.apps.spreadsheets.


Sub SideBySide()

Dim rowLst1 As Integer, rowLst2 As Integer
Sheets("List2").Select
Columns("A:B").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Cells(1, 1).Select
Sheets("List1").Select
Columns("A:B").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Cells(1, 1).Select

rowLst1 = 1: rowLst2 = 1

With Sheets("List2")

Do While Cells(rowLst1, 1) <> "" And .Cells(rowLst2, 1) <> ""
If Cells(rowLst1, 1) = .Cells(rowLst2, 1) Then
Cells(rowLst1, 2) = .Cells(rowLst2, 1)
rowLst1 = rowLst1 + 1
rowLst2 = rowLst2 + 1
ElseIf Cells(rowLst1, 1) < .Cells(rowLst2, 1) Then
rowLst1 = rowLst1 + 1
Else 'Cells(rowLst1,1)>.Cells(rowLst2,1)
Rows(rowLst1).Insert shift:=xlDown
Cells(rowLst1, 2) = .Cells(rowLst2, 1)
rowLst1 = rowLst1 + 1
rowLst2 = rowLst2 + 1
End If
Loop

Do While .Cells(rowLst2, 1) <> ""
Cells(rowLst1, 2) = .Cells(rowLst2, 1)
rowLst1 = rowLst1 + 1
rowLst2 = rowLst2 + 1
Loop
End With
End Sub


Dave Unger
 
Ok I believe that this script might work with some minor modifications,
like if it could put the results in side by side column on a new
worksheet, and also if it can do more then 2 list aslong as it only
uses the same list for the comparison.
 
Just to clarify - the results for the 2nd comparison should be in
column C, 2nd comparison in column D, etc., or , each addtional
comparison on column B of a new sheet?

Dave
 
Maurice,

Try this. A word of explanation - 2 sheets are assumed, Master
contains the master list, and NewList contains the list you're
wanting to compare, column A of both sheets. Now here's the part
that's different. The 1st time you run this macro, in Master sheet,
column A is duplicated in column B. Column A becomes an "index"
list to keep things lined up, and column B becomes the master list.
They start of identical, but on subsequent runs the index list will
grow with every new number encountered, but your main list in column B
maintains the original entries. New lists are entered in the next
available column.

Besides the main routine (SideBySide), the function routine SheetExists
is required to test for existance of required worksheets. I know this
sounds rather convoluted, but running it will make it clear.

Dave



Sub SideBySide()

Dim rowLst1 As Integer, rowLst2 As Integer, NextCol As Integer
Dim wk1 As Worksheet, wk2 As Worksheet

rowLst1 = 1: rowLst2 = 1

' Check for necessary worksheets

If Not SheetExists("Master") Then
MsgBox ("Missing worksheet " & """Master""")
Exit Sub
ElseIf Not SheetExists("NewList") Then
MsgBox ("Missing worksheet " & """NewList""")
Exit Sub
End If

Set wk1 = Worksheets("Master"): Set wk2 = Worksheets("NewList")

' sort NewList
wk2.Select
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select

wk1.Select

' sort Master and create sort index, the 1st time thru only
If ActiveSheet.UsedRange.Columns.Count = 1 Then
Range("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("A1").CurrentRegion.Copy Range("B1")
End If

NextCol = ActiveSheet.UsedRange.Columns.Count + 1
Range("A1").Select

With wk2
Do While Cells(rowLst1, 1) <> "" And .Cells(rowLst2, 1) <> ""
If Cells(rowLst1, 1) = .Cells(rowLst2, 1) Then
Cells(rowLst1, NextCol) = .Cells(rowLst2, 1)
rowLst1 = rowLst1 + 1
rowLst2 = rowLst2 + 1

ElseIf Cells(rowLst1, 1) < .Cells(rowLst2, 1) Then
rowLst1 = rowLst1 + 1
Else 'Cells(rowLst1,1)>.Cells(rowLst2,1)
Rows(rowLst1).Insert Shift:=xlDown
Cells(rowLst1, 1) = .Cells(rowLst2, 1)
Cells(rowLst1, NextCol) = .Cells(rowLst2, 1)
rowLst1 = rowLst1 + 1
rowLst2 = rowLst2 + 1
End If
Loop
' reached the end of Master, process remainder of NewList, if any.
Do While .Cells(rowLst2, 1) <> ""
Cells(rowLst1, 1) = .Cells(rowLst2, 1)
Cells(rowLst1, NextCol) = .Cells(rowLst2, 1)
rowLst1 = rowLst1 + 1
rowLst2 = rowLst2 + 1
Loop
End With

End Sub

Function SheetExists(ByVal SheetName As String) As Boolean
On Error GoTo NoSheet
Sheets(SheetName).Select
SheetExists = True
Exit Function
NoSheet:
SheetExists = False
End Function
 
Back
Top