"GS" wrote in message
...
[ ]
??? Rick's revised version as posted works just fine over here.

Did you test using his suggested (non-dupe) data set above?

Well I'll be..! Today it works just fine!



The plot thickens.

Today I am receiving the error you describe with non-repeating data.

Geez.., does this mean we have a virus?<bg ;-)



I did some more testing ... I thought I'd run it against his posted
sample data, but now it fails every time.

(btw; did you happen to see the execution time comparison I posted
elsewhere in this thread?)

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin brought next idea :
"GS" wrote in message
...
[ ]
??? Rick's revised version as posted works just fine over here.

Did you test using his suggested (non-dupe) data set above?

Well I'll be..! Today it works just fine!



The plot thickens.

Today I am receiving the error you describe with non-repeating data.

Geez.., does this mean we have a virus?<bg ;-)



I did some more testing ... I thought I'd run it against his posted sample
data, but now it fails every time.

I added a line to sort colB before loading it into the array so both
cols of data were sorted. I think the error comes with having empty
cells included in the array, but I wouldn't think that should matter
since the loop would just skip over those elements, -right?


(btw; did you happen to see the execution time comparison I posted elsewhere
in this thread?)

Yes, I saw the execution times you posted. Impressive! Not sure whether
I'm interested in testdriving it though. Just can't imagine having that
much data to process in a spreadsheet. Not saying it's not gonna
happen, just not typical of the type of work I do for clients. A MDB
and data controls is a better approach <IMO for large amounts of data.
Even when the data store is a plain text file, it's easier to use data
controls than read/write cells. <..again, IMO

--
Garry

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

[ reply inline ]

"GS" wrote in message
...
Clif McIrvin brought next idea :
"GS" wrote in message
...
[ ]
??? Rick's revised version as posted works just fine over here.

Did you test using his suggested (non-dupe) data set above?

Well I'll be..! Today it works just fine!



The plot thickens.

Today I am receiving the error you describe with non-repeating
data.

Geez.., does this mean we have a virus?<bg ;-)



I did some more testing ... I thought I'd run it against his posted
sample data, but now it fails every time.

I added a line to sort colB before loading it into the array so both
cols of data were sorted. I think the error comes with having empty
cells included in the array, but I wouldn't think that should matter
since the loop would just skip over those elements, -right?

In Rick's code, he copies Col B below A, then sorts ... and uses the
..Find method to ID the Col B values ... so there is no advantage to
pre-sorting Col B. As near as I could tell, the error is due to the
fact that there are *no* empty cells (ie, no duplicated values) after
his first loop.

After some thought, I replaced your suggested ' Resume Next ' with a
boolean flag -- that seemed to me to introduce less execution overhead,
but I really don't know. I added one line below the .Clear in the first
loop, then wrapped the line that errors in an IF:

Dim movedDuplicateValues As Boolean
....
movedDuplicateValues = True
....
If movedDuplicateValues Then
Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete '
remove empty rows
End If


As to the error in my code, it was due to faulty logic in my testing for
end of data. I was already adding an empty cell to the end of my array
to handle running past the end of data, but my attempt at working
regardless of which column had more values wasn't all the way home. I
ended up revising my IF ... ELSEIF construct (reposted below):



(btw; did you happen to see the execution time comparison I posted
elsewhere in this thread?)

Yes, I saw the execution times you posted. Impressive! Not sure
whether I'm interested in testdriving it though. Just can't imagine
having that much data to process in a spreadsheet. Not saying it's not
gonna happen, just not typical of the type of work I do for clients. A
MDB and data controls is a better approach <IMO for large amounts of
data. Even when the data store is a plain text file, it's easier to
use data controls than read/write cells. <..again, IMO


I agree on both points. I guess I was just too curious what the
difference was to leave it alone! <g

Clif

(code snippet)

Do
LastOut = LastOut + 1
ReDim Preserve Out(1 To 2, 1 To LastOut) ' columns, rows _
'because of how preserve works
ThisColAin = ColAin(idxColAin)
ThisColBin = ColBin(idxColBin)
If Not IsEmpty(ThisColAin) And IsEmpty(ThisColBin) Then
' Copy ColA, ColB = Empty
Out(1, LastOut) = ThisColAin
idxColAin = idxColAin + 1
ElseIf Not IsEmpty(ThisColBin) And IsEmpty(ThisColAin) Then
' Copy ColB, ColA = Empty
Out(2, LastOut) = ThisColBin
idxColBin = idxColBin + 1
ElseIf ThisColBin ThisColAin Then
' ColB is Larger: Copy ColA, ColB = Empty
Out(1, LastOut) = ThisColAin
idxColAin = idxColAin + 1
ElseIf ThisColBin = ThisColAin Then
' Same, copy both
Out(1, LastOut) = ThisColAin
Out(2, LastOut) = ThisColBin
idxColAin = idxColAin + 1
idxColBin = idxColBin + 1
Else ' ColA is Larger: Copy ColB, ColA = Empty
Out(2, LastOut) = ThisColBin
idxColBin = idxColBin + 1
End If ' ThisColBin <?? ThisColAin
Loop Until IsEmpty(ThisColAin) And IsEmpty(ThisColBin)


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin submitted this idea :
In Rick's code, he copies Col B below A, then sorts ...

Yes, I know. I added the sort to colB in case the data was not
contiguous. (As is the case after the code is run, then re-run)

--
Garry

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

"GS" wrote in message
...
Clif McIrvin submitted this idea :
In Rick's code, he copies Col B below A, then sorts ...

Yes, I know. I added the sort to colB in case the data was not
contiguous. (As is the case after the code is run, then re-run)



Actually, Rick's code handled that just fine as posted ... I
accidentally did that while testing ... then studied his code some more
to see why it didn't fail <g. His .copy didn't care that there was
dis-contiguous data, and after he sorted the result the data was all
contiguous again.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin has brought this to us :
"GS" wrote in message
...
Clif McIrvin submitted this idea :
In Rick's code, he copies Col B below A, then sorts ...

Yes, I know. I added the sort to colB in case the data was not contiguous.
(As is the case after the code is run, then re-run)



Actually, Rick's code handled that just fine as posted ... I accidentally did
that while testing ... then studied his code some more to see why it didn't
fail <g. His .copy didn't care that there was dis-contiguous data, and after
he sorted the result the data was all contiguous again.

This is only the case for colA. After the code runs, colB is no longer
contiguous and so re-running the code puts the empty cells into the
array, making the process longer than necessary. I put the line to sort
so this didn't happen.<g

--
Garry

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