J
James D. Connelly
I have two lists of text values. I put them into two worksheets (Sheet1)
contains the entire list, (call it a master list), the second sheet (Sheet2)
contains the same data minus about 100 entries. (note the master list is
450 rows long)
Anyway, I know you can take a single range and remove duplicates with an
internal Excel function (can't remember it off hand), but I have no idea,
and cannot find a way to evaluate two columns of data, and have it only
return the data that is NOT included in both columns.
I wrote a proceedure (see below), but it takes a heck of a long time (nearly
2 minutes on my old Pentium 3 computer LOL), but it does the job. What I
wondered, is, does Excel have an internal function or proceedure that would
do this? One thing that takes a huge length of time is the counting of the
number of rows with data to get an integer number to do the looping with.
Surely there must be an easy way to get a number of rows with data in a
column easier than the sledge hammer method I use below.
I am fairly conversant with Excel, use it nearly every day, and have created
small utilities to automate things before, but generally I don't concider
myself a "programmer". My background is robotics, and machine code, with
some C++ programming many years ago, but I am tinkering with VBA and Visual
Basic. If anyone wants to use, modify etc this code for thier own use go
ahead... I don't have illusions of grandeur LOL.
As a final note of total ignorance, why does TotalTime = EndTime - Start in
the code result in a bunch of meaningless numbers. I thought you could
manipulate time or date variables using math functions... but as the final
message box shows it displays the time the routine starts, the time it ends,
but the "total time" is meaningless to me.
Code begins
<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Option Explicit
Private Sub I_Have()
Dim txCardName As String
Dim txOtherName As String
Dim iTotalNumCellsS2 As Integer
Dim iTotalNumCellsS1 As Integer
Dim Start
Dim Endtime
Dim TotalTime
Dim x As Integer
Dim y As Integer
Dim a As Integer
Dim b As Integer
x = 0
Start = Time
Sheets("Sheet1").Select
Range("A1").Select
Do While Not IsEmpty(ActiveCell)
Sheets("Sheet1").Range("A1").Offset(x, 0).Select
x = x + 1
iTotalNumCellsS1 = x - 1
Loop
x = 0
Sheets("Sheet2").Select
Range("A1").Select
Do While Not IsEmpty(ActiveCell)
Sheets("Sheet2").Range("A1").Offset(x, 0).Select
x = x + 1
iTotalNumCellsS2 = x - 1
Loop
y = iTotalNumCellsS1
x = iTotalNumCellsS2
a = 0
Do While a < iTotalNumCellsS2
txCardName = Sheets("Sheet2").Range("A1").Offset(a, 0).Value
b = 0
Do While b < iTotalNumCellsS1
txOtherName = Sheets("Sheet1").Range("A1").Offset(b, 0).Value
If txCardName = txOtherName Then
Sheets("Sheet1").Range("A1").Offset(b, 0).Value = ""
b = iTotalNumCellsS1 + 1
ElseIf txCardName <> txOtherName Then
b = b + 1
End If
Loop
a = a + 1
Loop
Endtime = Time
TotalTime = Endtime - Start
MsgBox (Start & " " & Endtime & " " & TotalTime)
End Sub
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
James D. Connelly
----------------------------------------
James D. Connelly
116 Rice Ave
Hamilton, ON L9C 5V9
Phone (905) 575 0284
----------------------------------------
contains the entire list, (call it a master list), the second sheet (Sheet2)
contains the same data minus about 100 entries. (note the master list is
450 rows long)
Anyway, I know you can take a single range and remove duplicates with an
internal Excel function (can't remember it off hand), but I have no idea,
and cannot find a way to evaluate two columns of data, and have it only
return the data that is NOT included in both columns.
I wrote a proceedure (see below), but it takes a heck of a long time (nearly
2 minutes on my old Pentium 3 computer LOL), but it does the job. What I
wondered, is, does Excel have an internal function or proceedure that would
do this? One thing that takes a huge length of time is the counting of the
number of rows with data to get an integer number to do the looping with.
Surely there must be an easy way to get a number of rows with data in a
column easier than the sledge hammer method I use below.
I am fairly conversant with Excel, use it nearly every day, and have created
small utilities to automate things before, but generally I don't concider
myself a "programmer". My background is robotics, and machine code, with
some C++ programming many years ago, but I am tinkering with VBA and Visual
Basic. If anyone wants to use, modify etc this code for thier own use go
ahead... I don't have illusions of grandeur LOL.
As a final note of total ignorance, why does TotalTime = EndTime - Start in
the code result in a bunch of meaningless numbers. I thought you could
manipulate time or date variables using math functions... but as the final
message box shows it displays the time the routine starts, the time it ends,
but the "total time" is meaningless to me.
Code begins
<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Option Explicit
Private Sub I_Have()
Dim txCardName As String
Dim txOtherName As String
Dim iTotalNumCellsS2 As Integer
Dim iTotalNumCellsS1 As Integer
Dim Start
Dim Endtime
Dim TotalTime
Dim x As Integer
Dim y As Integer
Dim a As Integer
Dim b As Integer
x = 0
Start = Time
Sheets("Sheet1").Select
Range("A1").Select
Do While Not IsEmpty(ActiveCell)
Sheets("Sheet1").Range("A1").Offset(x, 0).Select
x = x + 1
iTotalNumCellsS1 = x - 1
Loop
x = 0
Sheets("Sheet2").Select
Range("A1").Select
Do While Not IsEmpty(ActiveCell)
Sheets("Sheet2").Range("A1").Offset(x, 0).Select
x = x + 1
iTotalNumCellsS2 = x - 1
Loop
y = iTotalNumCellsS1
x = iTotalNumCellsS2
a = 0
Do While a < iTotalNumCellsS2
txCardName = Sheets("Sheet2").Range("A1").Offset(a, 0).Value
b = 0
Do While b < iTotalNumCellsS1
txOtherName = Sheets("Sheet1").Range("A1").Offset(b, 0).Value
If txCardName = txOtherName Then
Sheets("Sheet1").Range("A1").Offset(b, 0).Value = ""
b = iTotalNumCellsS1 + 1
ElseIf txCardName <> txOtherName Then
b = b + 1
End If
Loop
a = a + 1
Loop
Endtime = Time
TotalTime = Endtime - Start
MsgBox (Start & " " & Endtime & " " & TotalTime)
End Sub
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
James D. Connelly
----------------------------------------
James D. Connelly
116 Rice Ave
Hamilton, ON L9C 5V9
Phone (905) 575 0284
----------------------------------------