Jump to content

Recommended Posts

Posted (edited)

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 by Radiohead22
wrong
Posted

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

Posted

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.

,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-.
|(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/
(_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_)
| | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) (
| | | | |)| | \ / | | | | | |)| | `--. | |) \ | |
`-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_|
'-' '-' (__) (__) (_) (__)

Posted
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 :)
Posted

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 :)

Posted (edited)

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 by Nine
Posted

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

Posted (edited)
Posted

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 :)

Posted

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 :)

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...