Fun with - Extracting Duplicate entries from an Array.

[MatthewBell]

Hi there.
Long time no thread. But i'm stuck again.

I have an array that has been filled with strings. They are the result of an:
Application.FileSearch.FileName = Blah\blah\blah\*.xls

The wildcard means I get hundreds of FoundFiles.
The bit that the wildcard represents is a string containing an ID number and a date.

What I need to do is somehow sort through the column in the array and extract the date portion of the string, CDate it to make it comparible and then compare the dates of Files with matching ID numbers. I then want to move all but the most recent file for each ID Number to a different folder.

Im fairly good with extracting the bits of string I need and I could put the date bit and the ID number bit in their own columns so all I really need to know is how to extract the duplicate entries in the ID column, compare the dates and move the files.

I could probably extract the duplicate entries with a loop to search through and check for matches but that would be slow with so many rows in the array and it gets run everytime the workbook is updated (often) so I was hoping there would be a command that returns the duplicate entries.

Can anyone help.

Many thanks

Matt

 

[SBendBuckeye]

Hello Matt,

It sounds like you are fairly comfortable with arrays so lets assume you have a 3 by x array and that the sort column is the second column (arrays are zero based) and that is contains combined ID and date data. The following sorts the array using a simple bubble sort. It keeps track of a counter because the highest value will end up at the top each time the loop iterates. It just makes it run a little faster for large arrays.

Dim blnSwapFound As Boolean
Dim lngRow As Long
Dim lngCol As Long
Dim lngCounter As Long
Dim varX As Variant

Do Until lngCounter > 0 And Not blnSwapFound
For lngRow = 0 To UBound(Array, 2) - 1 - lngCounter
blnSwapFound = False
'With zero based arrays the second field is 1
If Array(1, lngRow) > Array(1, lngRow + 1) Then
'Swap the current row values one column at a time
For lngCol = 0 To UBound(Array, 1)
blnSwapFound = True
varX = Array(lngCol, lngRow)
Array(lngCol, lngRow) = Array(lngCol, lngRow + 1)
Array(lngCol, lngRow + 1) = varX
Next lngCol
End If
Next lngRow
lngCounter = lngCounter + 1
Loop

Now that the array is sorted, go through it one more time to process it. If the IDs you mentioned are fixed length then you would test Left$(ArrayColumn2, x). If they are variable and the date was ccyymmdd at the end then you could test Left$(Len(ArrayColumn2) - 8). Let's assume they are fixed and 6 bytes long.

Dim strSaveID As String
Dim lngRow

For lngRow = 0 To UBound(Array, 2)
If Left$(Array(1, lngRow), 6) <> strSaveID Then
strSaveID = Left$(Array(1, lngRow), 6)
Else
'Move your data to whereever, etc
End If
Next lngRow

Hope this helps and good luck!




Next lngRow

 

[venkman]

You could also have excel do a lot of dirty work for you. I tend to think excel is made to do data sorts and things like that. You could copy the contents of your 3 column array into an excel sheet. Sort the data by id and then by date. write a simple macro to take the first entry for every set of ids ... or even make a 4th column where it's value is 1 if the id on that row is not equal to the id of the previous row... that way, you could just do a find on that column looking for 1.

I find that excel can sometimes be a lot faster at doing sorting operations like this than my macros... maybe I'm using the wrong algorithms, but I wouldn't be suprised if excel was somehow optimized for it. anyways, that's not as elegant as programming solutions go, but I think it would be much easier to script and debug.

-Venkman

 

[RobBroekhuis]

I'm with Venkman. Just create a temporary worksheet (or workbook) to do the dirty work.
Rob

 

[bryanbayfield]

There is a way to sort an array in VBA without resorting to writing your own procedure - but it's not well documented.

However you would still need to remove the duplicates from the array.

On balance of things I'd think I'd go with Venkman also - although it's normally considered that working with arrays rather than ranges leads to a faster processing time, here the Excel functionality you need is built-in.

I used the macro recorder to generate the following code:

    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1:A8").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
        "C:C"), Unique:=True

Unfortunately you'll need a couple more steps - a line to create the worksheet, another line to set your range ([a1] ... [a<UBound of array>]), then maybe you could name the range that the data is in to simplify the code I've put in the paragraph above. Two steps to finish - setting the array equal to the value in [C2] ... [range(&quot;C2&quot.end(xldown)] and Worksheets(Worksheets.Count).Delete assuming that you've added teh sheet at the end of the array of sheets.

B-)