Speed ​​up sequence comparison

[Radiohead22]

Ciao a tutti ragazzi

Devo rendere il mio script un po 'più veloce nell'elaborazione dei dati. 

Questa riga è errata nello script :

$ aResult2 = _Excel_RangeRead ($ oWorkbook1, Predefinito, "A" e $ a & ": A" e $ a + 5)

 sostituirlo con:

 $ aResult2 = _Excel_RangeRead ($ oWorkbook1, Default, "C" e $ a & ":C" e $ a + 5)   SEQUENCE.au3

Lo script cerca lo stesso valore nelle colonne A e C.

Confronta "A1" - "A6" con "C1" - "C6" / "A1" - "A6" con "C2" - "C7" / "A1" - "A6" con "C3" - "C8" e così Strada.

Al termine della colonna C confrontare "A2" - "A7" con "C1" - "C6" / "A2" - "A7" con "C2" - "C7" e così via.

Il ciclo della colonna C dura circa 1 minuto.

L'intero script richiede poco più di 2 ore.

 

$ e = 1

Mentre 1

$ aResult = _Excel_RangeRead ($ oWorkbook1, Predefinito, "A" e $ e & ": A" e $ e + 5)

$ SEQUENCE1 = $ aResult [0] e $ aResult [1] e $ aResult [2] e $ aResult [3] e $ aResult [4] e $ aResult [5]

$ aResult2 = _Excel_RangeRead ($ oWorkbook1, Predefinito, "A" e $ a & ": A" e $ a + 5)

$ SEQUENCE2 = $ aResult2 [0] e $ aResult2 [1] e $ aResult2 [2] e $ aResult2 [3] e $ aResult2 [4] e $ aResult2 [5]

Se $ SEQUENCE1 = $ SEQUENCE2 Quindi

bla bla bla 

wend

 

C'è un modo per accelerare la sceneggiatura?

Grazie in anticipo

saluti dall'italia

 

ESEMPIO DI SEQUENZA.xlsx

Edited December 18, 2019 by Radiohead22
wrong

[Earthshine]

yeah, do it in vba inside of excel

https://www.excel-easy.com/vba/examples/compare-ranges.html

[Radiohead22]

In vba will it be faster?

With autoit I am more familiar and can manage better in output data and change the incoming data.
Is there a way to make it faster using autoit?

thank you very much

[iamtheky]

in most every array comparison thread you will find an SQL solution that smokes everyone, might as well start there if you are going to end up there.

[FrancescoDiMuro]

@Radiohead22
Could you please be more specific?
How is this comparsion done?
What is the expected result?
Why is there a white column between A and C in the attached file?
By the way, ciao!

[Radiohead22]

7 minutes ago, FrancescoDiMuro said:

@ Radiohead22
Potresti essere più specifico?
Come viene fatta questa comparazione?
Qual è il risultato atteso?
Perché c'è una colonna bianca tra A e C nel file allegato?
A proposito, ciao!

is a simple comparison between the 2 columns with a range of 6 cells, as I explained at the beginning of the post in more detail,
the expected result is to see how many times a sequence of 6 cells in column A is repeated in column C.
The fact that column B is empty is only a visual fact, it has nothing to do with the operation of the script. Thanks :)

[Radiohead22]

is a simple comparison between the 2 columns with a range of 6 cells, as I explained at the beginning of the post in more detail,
the expected result is to see how many times a sequence of 6 cells in column A is repeated in column C.
The fact that column B is empty is only a visual fact, it has nothing to do with the operation of the script. Thanks

[xcaliber13]

Radiohead22   at one time I did use excel to do my compares.   Very slow.   Read your file to an array and do your compare within the array.  A lot faster.    

[Radiohead22]

 

Isn't that what I did in my script? confused🙄.

Could you give me an example of "reading a range of cells in an array"?

Thank you very much

[Nine]

Here in less than 2 seconds :

#include <Excel.au3>

Local Const $SERIAL = 6

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\SEQUENCE EXAMPLE.xlsx", False)

Local $aArray1 = _Excel_RangeRead ($oWorkbook, 1, "A1:A139")
Local $aArray2 = _Excel_RangeRead ($oWorkbook, 1, "C1:C139")

For $i = 0 to UBound($aArray1)-$SERIAL
  For $j = 0 to UBound($aArray2)-$SERIAL
    If Compare ($aArray1, $aArray2, $i, $j, $SERIAL) Then
      ConsoleWrite ("Found match at " & $i+1 & "/" & $j+1 & @CRLF)
      $j += $SERIAL
    EndIf
  Next
Next

Func Compare (ByRef $aArray1, ByRef $aArray2, $i, $j, $n)
  For $x = 0 to $n-1
    If $aArray1[$i+$x] <> $aArray2[$j+$x] Then Return False
  Next
  Return True
EndFunc

btw : I thought your OP was in english originally. Did you translate it ?

edit : corrected few typos 

edit2 : corrected ConsoleWrite

Edited December 18, 2019 by Nine

[Radiohead22]

this is great

the only thing I noticed is:

Local Const $ SERIAL = 6

in this way it counts 5 cells

if I replace it with:

Local Const $ SERIAL = 5

counts 6 cells

I don't understand why.

However it is a great job thanks indeed

2 hours of work in 2 seconds

You are a wizard

ps. yes my post was originally in italian I translated it into english but when i posted it the forum automatically translated it again in italian then i modified it again in ahhahah

[Radiohead22]

RECTIFY with this script some matches are wrong I can't understand why

[Nine]

No it should be 6 if serial is 6. Do you have an example where it didn't work ?

[Nine]

Maybe you look it at 0 based instead of 1 based.  Console write should add 1 to indexes, if you want to compare with xls.

Just corrected it.

Edited December 18, 2019 by Nine

[Radiohead22]

Yes the problem was just that

I'm sorry, I tried again with:
Local Const $ SERIAL = 6 and it works fine.

I was wrong to read the data, I have to count + 1 compared to the number of cells found.
I'm sorry for the confusion, I'm testing it with other excel files but it's working fine.

thank you very much for your help

[Radiohead22]

so Local Const $ SERIAL = 6 defines the range of cells to be compared right?

if instead of 6 I put 7 compare 7 cells - 8 compare 8 cells and so on?

[Nine]

Yes, it is the number of cells to compare.  One thing I wasn't sure is to skip cells in column C if a match is found.  Not sure if it is alright or not ?

[Radiohead22]

I'm still testing it with other excel files to see if it works but so far it's fine, i modified ConsoleWrite ("Found match at" & $i + 1 & "/" & $j + 1 & @CRLF) for cell matching so I don't get confused anymore

[Radiohead22]

thank the whole forum and thank you very much NINE for your precious help

[Nine]

 glad I could help, make sure skipping in Column C is alright tho...