How can I speed up this??

[wildcat_2000]

Hi all,

I'm currently using this routine to delete doubles. Could someone out there tell me HOW to speed up things?? This get really slow if I have more than 2000 items.

The idea is: I've got 1 list (defined by variables list(x), where x can easily go up to 30,000), I'm just purging it of the doubles that are included.

ttt = 0

baseroutine:

ttt = ttt + 1
If ttt > TotalNumberItemsList Then
    GoTo finish
End If

tttt = ttt

flexroutine:
tttt = tttt + 1
If tttt > TotalNumberItemsList Then
    GoTo baseroutine
ElseIf UCase(list(ttt)) = UCase(list(tttt)) Then
    lista(tttt) = ""
    GoTo flexroutine
End If

GoTo flexroutine

finish:

Thanks in advance for your help,

Cheers,

W.

[iring]

Don't use GOTO !!!

[Jerry Grant]

How is your base data presented?

If obtained from a database then sort on the SELECT.

If it is not sorted, use a sorting algorithm first, though this will be slower.

Then set up a loop, to check the neighouring items, using the StrComp method as follows:

If StrComp(String1, String2, vbBinaryCompare) Then
'Duplicate code in here............
End If

Then put unique items into a new array.

This will speed up the process I think.

[wildcat_2000]

Hi and thanks for your replies.

iring: what should I use instead then?

Jerry: no the database is not sorted, I just have these variables. Why StrComp should be quicker?

Furthermore I'm not sure whether an array can hold 30,000 or more items...

Please help me out! I found programs that delete doubles in a click, mine for 20,000 items is taking something like 10 minutes!!!!!!!!!!!

Thank you,

W.

[Surgeon]

Maybe this will run faster, I guess. Just give it a go and let me know :

    i = 1
    Do While i <= TotalNumberItemsList
            If List(i) <> "" _
            Then
                    j = i + 1
                    Do While j <= TotalNumberItemsList 
                            If (List(j) <> "") And (UCase(List(i)) = UCase(List(j))) _
                            Then
                                    List(j) = ""
                            End If
                            j = j + 1
                    Loop
            End If
            i = i + 1
    Loop

Surgeon

[paulw]

If it is a database, use a SQL query to select distinct records only into a new table, delete the records in the old table and repoipulate it from the temporary table. Check out GROUP BY and FIRST in your DB's SQL help. That will be quicker than any ISAM type code you can run.

Cheers,

P.

[wildcat_2000]

paulw: nope this is not a sql database, these variables result from a program's event.

surgeon: thanks i'll try that as soon as i get home, could you please tell me why you think that this could go quicker? Is it because i avoid using the GoTo function?

Do you believe there's a way to use an array (please remember that there are 30,000 variables or more)...

I really don't get it. This should be simple in practice, i've seen programs performing this operation defenitely in shorter time (i.e. a text list of 20,000 items, and they just delete the doubles).

Hope you can give me additional input, thank you again for being there,

W.