GS expressed precisely :
Your code took 46 secs to run your sample data on my machine. That's amazing!
I'm curious, now, how it performs if we did similar using a Collection so we
can have duplicates in ColA!

I modified my sub to use your idea to use Dictionary, but NOT put colA
in a dictionary and it shaved 11 secs off the ET...

Sub StripDupes()
Dim i&, j&, lRows1&, lRows2& lMatchesFound& 'as long
Dim vRngA, vRngB, vRngOut() 'as variant
Dim dRngB As Dictionary

lRows1 = Cells(Rows.Count, "A").End(xlUp).Row
lRows2 = Cells(Rows.Count, "B").End(xlUp).Row
vRngA = Range("A1:A" & lRows1): vRngB = Range("B1:B" & lRows2)

Set dRngB = New Dictionary
Debug.Print Now()
For j = LBound(vRngB) To UBound(vRngB)
With dRngB
If Not .Exists(Key:=vRngB(j, 1)) Then _
.Add Key:=vRngB(j, 1), Item:=vRngB(j, 1)
End With
Next 'j
sTemp = Mid$(sTemp, 2)
' Debug.Print Now()

For i = LBound(vRngA) To UBound(vRngA)
If dRngB.Exists(Key:=vRngA(i, 1)) Then _
vRngA(i, 1) = "": lMatchesFound = lMatchesFound + 1
Next 'i
' Debug.Print Now()

j = 0: ReDim vRngOut(UBound(vRngA) - lMatchesFound, 1 To 1)
For i = LBound(vRngA) To UBound(vRngA)
If Not vRngA(i, 1) = "" Then
vRngOut(j, 1) = vRngA(i, 1): j = j + 1
End If
Next 'i
' Debug.Print Now()

Range("A1:A" & lRows1).ClearContents
Range("A1").Resize(UBound(vRngOut), 1) = vRngOut

Debug.Print Now()
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

ET on my machine was 35 secs as per timing method used as shown. I
didn't think this task deserved the trouble to setup and use
cHiResTimer class.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Sun, 15 Jan 2012 19:58:40 -0500, GS wrote:

I modified my sub to use your idea to use Dictionary, but NOT put colA
in a dictionary and it shaved 11 secs off the ET...

That should preserve the duplicates in col A also, to answer your previous question.

Ron Rosenfeld wrote :
On Sun, 15 Jan 2012 19:58:40 -0500, GS wrote:

I modified my sub to use your idea to use Dictionary, but NOT put colA
in a dictionary and it shaved 11 secs off the ET...

That should preserve the duplicates in col A also, to answer your previous
question.

Hhm.., that's quite true where non-matches occur. I suppose that might
be a better way to go when comparing 2 or more items. Won't help my
data logger file parser, though. It just loops 1 array, but it does
parse each element into a temp array for the test. It runs blazingly
fast on my machine (1.6Ghz Intel dual-core on a Dell Precision series
laptop w/2GB RAM).

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,
More FWIW...
I ran Ron's code on my XP, i3 machine (xl2007) against 600,000 6 digit random numbers.
360,000 in col A and 240,000 in col B. It took about 7 1/2 seconds.
It returned ~87000 numbers not in col B.
'---
Jim Cone





"GS" wrote in message ...
Ron Rosenfeld wrote :
On Sun, 15 Jan 2012 19:58:40 -0500, GS wrote:

I modified my sub to use your idea to use Dictionary, but NOT put colA in a dictionary and it
shaved 11 secs off the ET...

That should preserve the duplicates in col A also, to answer your previous question.

Hhm.., that's quite true where non-matches occur. I suppose that might be a better way to go when
comparing 2 or more items. Won't help my data logger file parser, though. It just loops 1 array,
but it does parse each element into a temp array for the test. It runs blazingly fast on my
machine (1.6Ghz Intel dual-core on a Dell Precision series laptop w/2GB RAM).

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Jim Cone explained on 1/15/2012 :
Garry,
More FWIW...
I ran Ron's code on my XP, i3 machine (xl2007) against 600,000 6 digit random
numbers.
360,000 in col A and 240,000 in col B. It took about 7 1/2 seconds.
It returned ~87000 numbers not in col B.
'---
Jim Cone





"GS" wrote in message ...
Ron Rosenfeld wrote :
On Sun, 15 Jan 2012 19:58:40 -0500, GS wrote:

I modified my sub to use your idea to use Dictionary, but NOT put colA in
a dictionary and it shaved 11 secs off the ET...

That should preserve the duplicates in col A also, to answer your previous
question.

Hhm.., that's quite true where non-matches occur. I suppose that might be a
better way to go when comparing 2 or more items. Won't help my data logger
file parser, though. It just loops 1 array, but it does parse each element
into a temp array for the test. It runs blazingly fast on my machine
(1.6Ghz Intel dual-core on a Dell Precision series laptop w/2GB RAM).

-- Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



Thanks, Jim. Can you run my final version on your sample data and
report back. I'd be curious to know the results. I'm running XP SP3 and
did the test in xl2007. Thanks in advance...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,
It may turn out to be one of those days, especially if the snow sticks.

I plugged in a new set of sample data into xl2010 this morning.
It appears that your code is returning mismatched items... items in col A that are not in Col B.
But it is not eliminating duplicates.
Column A has 360,000 random 6 digit numbers.
Column B has 240,000 random 6 digit numbers.

Ron's code returns 231,414 unique entries.
Your code returns 279,200 entries: 231,514 unique and 47,686 duplicates.
(i ran my own unique counter on your returned data)

It's too early in the day for me to try to figure out why. <g
'---
Regards,
Jim Cone



"GS"
wrote in message
...
Ron Rosenfeld wrote :
Thanks, Jim. Can you run my final version on your sample data and report back. I'd be curious to
know the results. I'm running XP SP3 and did the test in xl2007. Thanks in advance...

--
Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



"GS" wrote in message ...
Jim Cone explained on 1/15/2012 :
Garry,
More FWIW...
I ran Ron's code on my XP, i3 machine (xl2007) against 600,000 6 digit random numbers.
360,000 in col A and 240,000 in col B. It took about 7 1/2 seconds.
It returned ~87000 numbers not in col B.
'---
Jim Cone

On Mon, 16 Jan 2012 07:49:46 -0800, "Jim Cone" wrote:

Garry,
It may turn out to be one of those days, especially if the snow sticks.

I plugged in a new set of sample data into xl2010 this morning.
It appears that your code is returning mismatched items... items in col A that are not in Col B.
But it is not eliminating duplicates.
Column A has 360,000 random 6 digit numbers.
Column B has 240,000 random 6 digit numbers.

Ron's code returns 231,414 unique entries.
Your code returns 279,200 entries: 231,514 unique and 47,686 duplicates.
(i ran my own unique counter on your returned data)

It's too early in the day for me to try to figure out why. <g
'---
Regards,
Jim Cone

Jim,

I believe I mentioned that in response to Gary's posting a version where he did not use the dictionary for column A. He actually WAS looking for a way to PRESERVE the duplicates in Column A, and I opined that that particular version should do so.

The "why" is because by not using a dictionary to collect the non-matches for column A, the duplicates do not get filtered.

So, if preserving duplicate entries in Column A is a requirement, Gary's version will do so.

-- Ron

on 1/16/2012, Jim Cone supposed :
Garry,
It may turn out to be one of those days, especially if the snow sticks.

I plugged in a new set of sample data into xl2010 this morning.
It appears that your code is returning mismatched items... items in col A
that are not in Col B.
But it is not eliminating duplicates.
Column A has 360,000 random 6 digit numbers.
Column B has 240,000 random 6 digit numbers.

Ron's code returns 231,414 unique entries.
Your code returns 279,200 entries: 231,514 unique and 47,686 duplicates.
(i ran my own unique counter on your returned data)

It's too early in the day for me to try to figure out why. <g
'---
Regards,
Jim Cone



"GS"
wrote in message
...
Ron Rosenfeld wrote :
Thanks, Jim. Can you run my final version on your sample data and report
back. I'd be curious to know the results. I'm running XP SP3 and did the
test in xl2007. Thanks in advance...

-- Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



"GS" wrote in message ...
Jim Cone explained on 1/15/2012 :
Garry,
More FWIW...
I ran Ron's code on my XP, i3 machine (xl2007) against 600,000 6 digit
random numbers.
360,000 in col A and 240,000 in col B. It took about 7 1/2 seconds.
It returned ~87000 numbers not in col B.
'---
Jim Cone

Jim,
Ron explains the what/why of my version of the task fairly well. What
I'm more interested in is how long it took on your machine to process
the same amount of data as when you ran Ron's version.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Sun, 15 Jan 2012 19:58:40 -0500, GS wrote:

Next 'i

GS,
Technique question:
Why, on the "Next" line, do you comment out the counter variable that you are looping on?
I've not commented out, and have had the VBE help me out when I might be using nested loops.

-- Ron

Ron Rosenfeld submitted this idea :
On Sun, 15 Jan 2012 19:58:40 -0500, GS wrote:

Next 'i

GS,
Technique question:
Why, on the "Next" line, do you comment out the counter variable that you
are looping on? I've not commented out, and have had the VBE help me out when
I might be using nested loops.

-- Ron

Ron,
Just something I picked up from the Classic VB crowd. It falls in the
same bucket as the dif using Mid() and Mid$(), and how VB handles this
at runtime. Sorry, but I can't give you technical details about these
without going back over a few years of posts. I include the comment for
notation purposes so I know which counter is repeating in nested or
long loops. Otherwise, I don't see any problem with leaving the
apostrophe out if desired. My choice to use it was formed a long time
ago because I didn't want to lose the notation. (You'll see other code
samples that use a similar technique for Select Case, If, While, and Do
constructs as well. I also do similar for end of procedures because
it's helpful when reading through modules in a text editor outside the
VBE.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc