New here so be gentle

[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
----------------------------------------

 

[Ken Johnson]

Hi James,
Have you included Application.ScreenUpdating = False to speed up the
code.
I had a quick glance and my old eyes didn't spot it. If you haven't
used it just type it in as a new line any where before the code starts
changing values on the sheet. Should result in big improvement in code
speed.
Also advanced filter has a "Unique records only option" that could be
useful.
Ken Johnson

 

[Darren Hill]

Hi James (and Ken, I appear to be stalking you...)

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.

There are a few. For example:
NumberRows = Range("A1").CurrentRegion.Rows.Count
Using .End(xlDown) is another:
NumberRows = Range("A1").End(xldown).Row

You're using the .Select method a lot - this is really slow (it makes your
loop much slower), and you can usually avoid it by addressing the cell you
want directly, rather than selecting it.directly. As Ken mentioned,
switching off application.screenupdating can be very useful.

You said you wanted to compare two ranges and find the unique values. It
can be done using Find (though I'll admit this isn't tested).


Sub Test()
Dim myCell As Range, rngFoundItems As Range
Dim firstRange As Range, secondRange As Range

Set firstRange = Sheets("Sheet1").Range("a1").CurrentRegion.Resize(, 1)
Set secondRange = Sheets("Sheet2").Range("a1").CurrentRegion.Resize(, 1)
For Each myCell In firstRange
Set rngFound = secondRange.Find(What:=myCell.Value, LookAt:=xlPart)
If Not rngFound Is Nothing Then
' this means theres a duplication
' the current cell is matched by a cell in the secondrange
' enter appropriate code here, if any

Else
' these means mycell.value is not duplicated in secondrange
' it's unique.
MsgBox (myCell.Address & " is unique")
End If
Next myCell

' You then repeat the process, switching the ranges. This allows you to
find the
' cell values in both ranges that are unique
' In your case, this isn't needed so snip, snip, snip
For Each myCell In secondRange
Set rngFound = firstRange.Find(What:=myCell.Value, LookAt:=xlPart)
If Not rngFound Is Nothing Then
' this means theres a duplication
' the current cell is matched by a cell in the secondrange
' enter appropriate code here, if any

Else
' these means mycell.value is not duplicated in secondrange
' it's unique.
MsgBox (myCell.Address & " is unique")
End If
Next myCell

 

[James D. Connelly]

Ken

The turning off of Screen Updating, did in fact make a significant
improvment in speed. Not great but I can argue with the following numbers.
27 seconds is a pretty significant improvement in performance. Thanks. Now
to try the next suggestions, after I figure out what the code snippets are,
what functions they are using (why I never saw them before LOL) etc. That I
think is the beauty of programming at any level, be it batch files, machine
language, etc... it always has 100 ways of doing everything, usually based
on how much experience the person programming has with the language or
peculiaraties of the program he/she is creating. Those who have a lot of
skill in doing text manipulation will always know esoteric ways of finding
and changing text whereas the number cruncher will know the math formula and
statistical analysis inside out. I appreciate the help from both
respondants so far. However both of you failed to mention why my attempt to
find the execution time of the sub results in crap. (the totaltime =
endtime - start equation). LOL

The first is with Screen Updating turned off
9:56:40
9:53:55
2:45

This is with the Updating left on (default)
10:02:51
9:59:39
3:12


James D. Connelly

 

[James D. Connelly]

Darren

WOW!

I re-wrote the sub (see below) using your suggestion. The execution dropped
to 20 seconds! The result was exactly the same in that I got left with only
those items in sheet1 that were unique between the two sheets! I am going
to definitely have to remember those attributes to the Range object!
Thanks.

The re-written code is below (just in case anyone wants to import and use it
LOL). What I do is a lot of hobby related stuff, where I have lists to
sports cards, and this procedure sure helps a lot!

You obviously have a lot of experience and know where to look to find
answers (which is always more than half the battle).

10:38:34
10:38:14
0:0:20
The time did not change whether ScreenUpdating was True or False... That is
simply amazing. Thanks.
Public Sub I_Have_2()
Application.ScreenUpdating = False
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

iTotalNumCellsS1 = Range("A1").CurrentRegion.Rows.Count

Sheets("Sheet2").Select
Range("A1").Select

iTotalNumCellsS2 = Range("A1").CurrentRegion.Rows.Count

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
Application.ScreenUpdating = True
MsgBox (Start & " " & Endtime & " " & TotalTime)


End Sub

 

[Ken Johnson]

Hi James,
I'm only guessing here, butI think that the value returned (a really
small number?) is the elapsed time in days.
I typed the following into the Immediate Window of the VBA editor:

x= time

then about 10 seconds after pressing enter I typed:

y= time

then after pressing Enter I typed:

?(y-x)*24*60*60

after pressing Enter a value close to 10 appeared. *24*60*60 converts
days to seconds.

If you need to time your code I think it is easier to use the Timer
function which returns the number of seconds since midnight, then your
difference calculation is in seconds and not days (crap!).

Ken Johnson

 

[James D. Connelly]

Ken

I was not aware of the timer function LOL, but I have learned more tonight
in this message forum than in a week of reading vba books LOL. Actually the
books were quite helpful in that they gave me the imputus to play around.

I will try the timer function as well as doing the math you did and see if
the time is the same

James D. Connelly

 

[Darren Hill]

Yes, as Ken mentions, Excel handles dates and times in its own format.
Basically, dates are in a format like this:
3456.0235687098
(actual number of digits will be different)

Just to explain: the integer is a number of days since sometime early last
century, and the bit after the decimal point is the fraction of a full day.
If you were subtracting two times, you get rid of the integers and end up
with a very small number which, as Ken points out, is almost useless.

Darren

 

[Darren Hill]

Hi, James.
The Screenupdating method didn't change the time on this version, because
it wasn't using the Select method, or writing values to a worksheet. If
you have ScreenUpdating = True, Excel has to update the spreadsheet every
time a macro moves the mouse cursor, or changes a cell. This is the
slowest part of any macro.
Setting to False means Excel only updates the screen when the macro
finishes - and so saves a lot of time.

You also said:

You obviously have a lot of experience and know where to look to find
answers (which is always more than half the battle).

Almost everything I know, I learned on this list. The people here are
great.

Darren

Darren

WOW!

I re-wrote the sub (see below) using your suggestion. The execution
dropped
to 20 seconds! The result was exactly the same in that I got left with
only
those items in sheet1 that were unique between the two sheets! I am
going
to definitely have to remember those attributes to the Range object!
Thanks.

The re-written code is below (just in case anyone wants to import and
use it
LOL). What I do is a lot of hobby related stuff, where I have lists to
sports cards, and this procedure sure helps a lot!

You obviously have a lot of experience and know where to look to find
answers (which is always more than half the battle).

10:38:34
10:38:14
0:0:20
The time did not change whether ScreenUpdating was True or False...
That is
simply amazing. Thanks.
Public Sub I_Have_2()
Application.ScreenUpdating = False
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

iTotalNumCellsS1 = Range("A1").CurrentRegion.Rows.Count

Sheets("Sheet2").Select
Range("A1").Select

iTotalNumCellsS2 = Range("A1").CurrentRegion.Rows.Count

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
Application.ScreenUpdating = True
MsgBox (Start & " " & Endtime & " " & TotalTime)


End Sub

 

[Ken Johnson]

Hi James,
I've sent you an email with some ideas about code speed. Unfortunately
I neglected to type a topic message. It's not spam. Just thought I'd
let you know here in case there is a problem, which there shouldn't be
since it is a response to your original email.
Ken Johnson