Sommare numeri diversi tra due range

[miko]

salve,
in 2 fogli ho 2 distinti range con numero di righe diverso ma variabile per entrambi ed uguale numero di colonne, 15.
i valori delle celle sono disposti in ordina crescente da sinistra a destra per entrambi i range.
devo confrontare ciascuna riga del range1 di foglio1 con ciascuna e tutte le righe del range2 di foglio2.
quando tra 2 righe dei 2 range, trovo 1 solo numero uguale, oppure 2 ed al massimo 3 numeri uguali,
in qualunque colonna essi si trovano, devo sommare i numeri della riga di range2 che invece sono diversi dalla riga di range1.
si deve saltare il confronto, e quindi saltare la somma, quando il numero dei valori uguali è >= 4,
o quando non c'è neanche un numero uguale tra le 2 righe.
ad esempio per 2 righe che hanno 5 numeri uguali devo saltare il confronto e quindi escludere il calcolo della somma.
io riesco a fare solo un ciclo che confronta cella per cella, ma è molto lenta se le righe sono elevate.
è' possibile sfruttare la potenza e velocità della funzione Evaluate,
ad esempio scrivendo:
valori_diversi = Evaluate(rigaN-range1 confronto rigaX-range2)
risultato = somma(valori_diversi)
o di qualche altra funzione per giungere al risultato?
ciao, grazie

[Anthony47]

Si, ma se hai 100 righe in Foglio1 e 200 righe in Foglio2 allora per ognuna delle 100 righe farai 200 confronti; dove vanno messi i potenziali 200 risultati di ognuna delle 100 righe?
Se vuoi fare il confronto tra una riga di Foglio1 e un'altra di Foglio2 potresti usare all'interno di Evaluate la formula (tipo)

Codice: Seleziona tutto

SOMMA(CONTA.SE(Foglio1!A2:O2;fOGLIO22!A5:O5))

Ma non saprai quali sono le celle non uguali che eventualmente andrebbero sommate; le dovrai individuare con confronto cella-a-cella, per cui qualcosa velocizzeresti ma non in modo risolutivo.
Penso che la soluzione sia nel copiare in due array di memoria i due intervalli e poi fare il confronto + la somma coi dati in memoria.
Sono in viaggio, qualcosa in piu' potrei dirti stasera tardi Ciao

[miko]

ciao, hai ragione ho dimenticato alcuni particolari

...dove vanno messi i potenziali 200 risultati di ognuna delle 100 righe?...

i risultati devono essere inseriti nel foglio2 affianco alle righe del range2
entrambi i range iniziano in cella H10 e finiscono in colonna V, quindi se puoi lascia una colonna vuota
e utilizza la colonna X.
forse inserirli in una matrice colonna è più semplice e più veloce,
poi eventualmente, se sarà necessario, inserirò la matrice in una colonna,
dal momento che il risultato delle somme è temporaneo, successivamente con quei risultati
devo eseguire altri calcoli definitivi.
per me sarebbe meglio una matrice ma non sapendo come è meglio procedere lascio a te la decisione.
per quanto riguarda

..Ma non saprai quali sono le celle non uguali che eventualmente andrebbero sommate..

al momento non è interessante sapere le celle diverse è importante la somma dei valori differenti.

ultima cosa se è possibile e se non è troppo complicato, il calcolo deve avvenire partendo dalla prima riga
del range1 di foglio1 fino all'ultima di range1, confrontandole a partire dall'ultima riga del range2 di foglio2 e procedendo fino alla prima di range2, in pratica per il range2 si deve procedere dall'ultima alla prima riga.
in quest' ultimo caso se confrontiamo prima riga di range1 con ultima riga di range2 il risultato va inserito
affianco dell' ultima riga di range2.
spero nella chiarezza dell'esposizione.
data l'enorme mole di righe da esaminare sarebbe necessaria una macro molto veloce, perciò non ti fermare
alla " prima cosa che ti viene in mente " (citazione di un precedente post), hai tutto il tempo necessario.
grazie, saluti

[Anthony47]

data l'enorme mole di righe da esaminare sarebbe necessaria una macro molto veloce, perciò non ti fermare
alla " prima cosa che ti viene in mente " (citazione di un precedente post), hai tutto il tempo necessario.

Azz... Io veramente pensavo di cavarmela con qualche buon consiglio

i risultati devono essere inseriti nel foglio2 affianco alle righe del range2
entrambi i range iniziano in cella H10 e finiscono in colonna V, quindi se puoi lascia una colonna vuota
e utilizza la colonna X.

devo confrontare ciascuna riga del range1 di foglio1 con ciascuna e tutte le righe del range2 di foglio2.
quando tra 2 righe dei 2 range, trovo 1 solo numero uguale, oppure 2 ed al massimo 3 numeri uguali,
in qualunque colonna essi si trovano, devo sommare i numeri della riga di range2 che invece sono diversi dalla riga di range1.
si deve saltare il confronto, e quindi saltare la somma, quando il numero dei valori uguali è >= 4,

Quindi io capisco che devi confrontare N righe (es 100) di foglio 1 con M righe (es 200) di foglio2; quindi accanto alle 200 righe di foglio2 ci vanno fino a 100 risultati. Come faccio a metterli tutti nella colonna X?

ultima cosa se è possibile e se non è troppo complicato, il calcolo deve avvenire partendo dalla prima riga
del range1 di foglio1 fino all'ultima di range1, confrontandole a partire dall'ultima riga del range2 di foglio2 e procedendo fino alla prima di range2, in pratica per il range2 si deve procedere dall'ultima alla prima riga.
in quest' ultimo caso se confrontiamo prima riga di range1 con ultima riga di range2 il risultato va inserito
affianco dell' ultima riga di range2.

Questo conferma che non ho capito che cosa cerchi; perche' se ogni riga di foglio1 va confrontata con "ciascuna riga di foglio2" che cosa cambia tra cominciare a confrontare la prima di 1 con la prima di 2, l'ultima di 1 con la penultima di 2, la prima di 1 con la terzultima di 2 etc etc INVECE CHE la prima di 1 con la prima di 2, la prima di 1 con la seconda di 2, la prima di 1 con la terza di 2 etc etc?

Ciao

[miko]

buongiorno,
mi rendo conto che la descrizione del quesito ha creato delle confusioni.
per chiarezza

scrivi

...pensavo di cavarmela con qualche buon consiglio...

i tuoi consigli sarebbero senz'altro graditi ma non porterebbero ad alcun risultato immediato per la mia incapacità di realizzare una macro che immagino complessa e che non so neanche come impostare se non come scrivevo il solito confronto cella per cella.
esempio,in precedenza scrivevi:

...potresti usare all'interno di Evaluate la formula (tipo)
SOMMA(CONTA.SE(Foglio1!A2:O2;fOGLIO22!A5:O5))...

io non so come si scrive una istruzione con evaluate, nonostante le mie letture al riguardo ancora non capisco come funziona evaluate, e non saprei neanche in che punto di evaluate inserire il codice che hai suggerito.
Perciò continuerei a chiederti informazioni passo dopo passo su come realizzare la macro.
quindi ho bisogno della macro principale secondo la tua idea progettuale per giungere alla soluzione.

per il dubbio:

...Quindi io capisco che devi confrontare N righe (es 100) di foglio 1 con M righe (es 200) di foglio2; quindi accanto alle 200 righe di foglio2 ci vanno fino a 100 risultati. Come faccio a metterli tutti nella colonna X?...

invece hai capito molto bene cosa cerco
i risultati dei confronti non vanno inseriti tutti e contemporaneamente in colonna X o matriceX, ma un risultato alla volta, confronto dopo confronto.
Esempio, chiamiamo la macro principale Macro Anthony
1) confronto la prima di 1 con la prima di 2, la prima di 1 con la seconda di 2, e cosi via fino all’ultima di 2; (come effettivamente hai capito)
2) inseriamo il risultato in colonna X o matriceX,
3) a questo punto devo inserire, ad esempio con una CALL, che richiama una mia macro, MiaMacro, un mio semplice calcolo che fornisce il risultato finale definitivo,
4) cancello la colonna X o matriceX,
5) ripeto il confronto tra la seconda di 1 con la prima di 2, la seconda di 1 con la seconda di 2, e cosi via fino all’ultima di 2;
6) risultato in colonna X o matriceX
7) CALL MiaMacro
cancello la colonna X o matriceX
9) ripeto il confronto di Macro Anthony tra la terza di 1 con tutte quelle di 2 e così via.

Infine per

... INVECE CHE la prima di 1 con la prima di 2, la prima di 1 con la seconda di 2, la prima di 1 con la terza di 2 etc etc?...

Questa richiesta è dovuta perché in ogni confronto devo eliminare le righe di 2 che non sodisfano un criterio;
se il confronto parte dalla prima di 1 fino all'ultima di 1 e procedo partendo dall’ultima di 2 fino alla prima di 2, evito lo scorrimento di tutte le righe di 2 verso l’alto.
Devo solo calcolare ogni volta, per i successivi confronti, la riga finale di 2.
grazie, saluti

[Anthony47]

Quindi tu vorresti una macro velocissima che confronta una riga di foglio1 con tutte le righe di foglio2; quando hai il risultato scendi dalla bici e a mano cambi la posizione della catena.
Ad esempio:

Codice: Seleziona tutto

 Sub JetCompare()
Dim RR1 As Range, RR2 As Range, kCol As Long, L As Long
Dim I As Long, J As Long, K As Long, mySum As Long, myCnt As Long
Dim oArr(), W1Arr, W2Arr, Lista1 As Range, Lista2 As Range
Dim hArr(), myDbg As Boolean
'
Set RR1 = Sheets("Foglio1").Range("H10")        '<<< Le aree di partenza dei 2 intervalli
Set RR2 = Sheets("Foglio2").Range("H10")
kCol = 15                                       '<<< La larghezza delle liste in Colonne
'
ReDim hArr(1 To kCol)
myDbg = False
Set Lista1 = Range(RR1, RR1.End(xlDown))
W1Arr = Lista1.Resize(, kCol).Value
For I = 1 To UBound(W1Arr)
    Set Lista2 = Range(RR2, RR2.End(xlDown))
    W2Arr = Lista2.Resize(, kCol).Value
    ReDim oArr(1 To UBound(W2Arr), 1 To 1)
    For J = UBound(W2Arr) To 1 Step -1
        For L = 1 To kCol
            hArr(L) = W2Arr(J, L)
        Next L
        mySum = 0: myCnt = 0
        For K = 1 To kCol

                For L = 1 To kCol
                    If W1Arr(I, K) = hArr(L) Then
''                        If myDbg Then Debug.Print I, J, K, L
                        myCnt = myCnt + 1
                        hArr(L) = 0
                    End If
                Next L
        Next K
        If myCnt < 4 And myCnt > 0 Then
            If myDbg Then Debug.Print I, J, myCnt
            oArr(J, 1) = Application.WorksheetFunction.Sum(hArr)
        Else
            If myDbg Then Debug.Print I, J, myCnt * 10
        End If
    Next J
    RR2.Offset(0, kCol + 1).Resize(UBound(oArr), 1) = oArr     '?????
Stop
Next I
End Sub


La macro compara una riga di intervallo1 con tutte le righe di intervallo2.
Completato il controllo, il risultato e' disponibile nella matrice oArr; al momento la macro (vedi istruzione marcata ????) scrive questo risultato in colonna X sul foglio di intervallo2 e si ferma su uno Stop, cosi' sai dove agganciare i tuoi controlli; spero che tu riesca invece al leggere i risultati dalla matrice, così potrai eliminare la riga che scrive in in colonna X.
Poi ripete con riga2 di intervallo1; poi ripete con riga3; etc etc

Trovo che sia fortemente penalizzante, in termine di velocita' di esecuzione, la cancellazione di righe da intervallo2: e' quello che ho descritto come "cambiare a mano la posizione della catena"; sarebbe molto piu' efficiente cancellare il contenuto dalla matrice W2Arr (che e' la copia di intervallo2) che porterebbe a "nessun confronto vero" su quella riga e quindi lo stesso risultato che eliminare la riga.
Vedi tu come riesci a integrare nel tuo progetto

Ciao

[miko]

Buongiorno,
grazie per il tuo contributo e celerità.

… tu vorresti una macro velocissima che…
..quando hai il risultato scendi dalla bici e …

Purtroppo la mia fantasia realizzativa è fortemente limitata dalla scarsa conoscenza di excel e delle sue potenzialità.
Quindi mi limito a ragionare in termini a me noti e delle ricerche sul web.
Se esistono metodi e/o differenti criteri per raggiungere una soluzione non mi è dato sapere.
Se ritieni che il mio metodo

.. sia fortemente penalizzante, in termine di velocita' di esecuzione…
…sarebbe molto più efficiente cancellare…

Allora adottiamo il tuo metodo, così resto sempre in bici.
Al riguardo spiego il semplice criterio di eliminazione delle righe di intervallo2.
In foglio 1 accanto ad ogni riga di intervallo1 nelle colonne X e Z ci sono 2 valori numerici;
La macro “JetCompare”, compara una riga di intervallo1 con tutte le righe di intervallo2.
Esempio: riga1 di intervallo1 con tutte le righe di intervallo2.
Completato il controllo, il risultato è disponibile nella matrice oArr;
per ogni item di oArr verifico se il suo valore è compreso tra X e Z di riga1 di intervallo1;
esempio: riga1 di intervallo1 : H10-V10 >>>> X10 = 1254, Z10 = 3587
item1 di oArr >>> oArr(1) >>> che corrisponde al risultato della prima riga di intervallo2
se oArr(1) >= X10 = 1254 ed oArr(1) <= Z10 = 3587
allora la riga1 di intervallo2 NON DEVE essere eliminata.
Segue confronto sempre di X10 e Z10, di riga1 di intervallo1, ma con risultato di riga2 di intervallo2
se oArr(2) < X10 = 1254 ed oArr(2) > Z10 = 3587
allora la riga2 di intervallo2 DEVE essere eliminata.
Non so se è meglio confrontare riga per riga ed procedere alla eventuale eliminazione, oppure prima popolare tutta la matrice oArr e poi eseguire l’eliminazione.
Inoltre non devono essere eliminate le righe di intervallo2 che non hanno nessun numero in comune
con la riga1 di intervallo1.
Segue confronto di riga2 di intervallo1 : H11-V11 >>>> X11 = 1305, Z11 = 2620
con tutte le righe di intervallo2, che per la eventuale eliminazione precedente, avrà un numero minore di righe.
Procedo come prima al confronto per la eliminazione.
Ripeto i passaggi per tutte le altre righe.
è importante conoscere il contenuto della matrice oArr solo per procedere alla eliminazione delle righe di intervallo2;
alla fine di tutto il calcolo il suo contenuto è inutile, così come non serve sapere quale linea è stata eventualmente eliminata.
Il risultato che interessa è il contenuto finale di intervallo2, intervallo che può essere anche vuoto se vengono eliminate tutte le righe.
Bisogna prevedere che se giunti al confronto tra rigaY di intervallo1 con intervallo2, quest’ultimo è già vuoto, per via delle eliminazioni precedenti, si deve uscire dalla procedura-macro.
Vedi se riesci col tuo criterio a farmi rimanere sempre in bici o meglio ancora su qualcosa di più comodo e veloce.
Ciao e grazie ancora, saluti

[Anthony47]

E quindi il risultato finale sara' un "intervallo2" costituito dalle sole righe che non hanno subito il processo di eliminazione?

[miko]

Buongiorno,
si perfetto, il tutto si riduce ad eliminare righe da "intervallo2" .
il problema è il criterio adottato, la somma dei valori diversi tra le righe dei due intervalli, ed il numero di righe
di intervallo2 che è molto elevato.
grazie, buona domenica

[Anthony47]

Mah, prova con questo codice, in sostituzione della Sub JetCompare precedente:

Codice: Seleziona tutto

Sub JetCompare2()
Dim RR1 As Range, RR2 As Range, kCol As Long, L As Long
Dim I As Long, J As Long, K As Long, mySum As Long, myCnt As Long
Dim oArr(), W1Arr, W2Arr, Lista1 As Range, Lista2 As Range
Dim hArr(), myDbg As Boolean
Dim lLimit As Long, uLimit As Long, cArr(), oRR As Range
'
Set RR1 = Sheets("Foglio1").Range("H10")        '<<< Le aree di partenza dei 2 intervalli
Set RR2 = Sheets("Foglio2").Range("H10")
Set oRR = Sheets("FoglioZ").Range("A1")         '<<< L'area dove sara' scritto il risutato
kCol = 15                                       '<<< La larghezza delle liste in Colonne
'
mytim = Timer
ReDim hArr(1 To kCol)
myDbg = False
Set Lista1 = Range(RR1, RR1.End(xlDown))
W1Arr = Lista1.Resize(, kCol).Value
Set Lista2 = Range(RR2, RR2.End(xlDown))
W2Arr = Lista2.Resize(, kCol).Value
For I = 1 To UBound(W1Arr)
    lLimit = RR1.Cells(I, kCol + 2)
    uLimit = RR1.Cells(I, kCol + 4)
    ReDim oArr(1 To UBound(W2Arr), 1 To 1)
    For J = UBound(W2Arr) To 1 Step -1
    If W2Arr(J, 1) <> "" Then
        For L = 1 To kCol
            hArr(L) = W2Arr(J, L)
        Next L
        mySum = 0: myCnt = 0
        For K = 1 To kCol
                For L = 1 To kCol
                    If W1Arr(I, K) = hArr(L) Then
                        If myDbg Then Debug.Print I, J, K, L
                        myCnt = myCnt + 1
                        hArr(L) = 0
                    End If
                Next L
        Next K
        If myCnt < 4 And myCnt > 0 Then
            If myDbg Then Debug.Print I, J, myCnt
            oArr(J, 1) = Application.WorksheetFunction.Sum(hArr)
            If oArr(J, 1) < lLimit Or oArr(J, 1) > uLimit Then      'Elimina riga
                    W2Arr(J, 1) = ""
            End If
        Else
            If myDbg Then Debug.Print I, J, myCnt * 10
        End If
    End If
    Next J
    RR2.Offset(0, kCol + 1).Resize(UBound(oArr), 1) = oArr    'SOLO per TEST
Stop                                                          'SOLO per TEST
Next I
Call oArrCollapse(W2Arr, cArr)
oRR.Resize(UBound(cArr), UBound(cArr, 2)).Value = cArr
Debug.Print Format(Timer - mytim, "0.00")
MsgBox ("Completato, Sec. " & Format(Timer - mytim, "0.00"))
End Sub



Sub oArrCollapse(ByRef wArr, ByRef oArr)
Dim wInd As Long
ReDim oArr(1 To UBound(wArr), 1 To UBound(wArr, 2))
For I = 1 To UBound(wArr)
    If wArr(I, 1) <> "" Then
        wInd = wInd + 1
        For J = 1 To UBound(wArr, 2)
            oArr(wInd, J) = wArr(I, J)
        Next J
    End If
Next I
End Sub


Ho fatto alcune modifiche al corpo della JetCompare, per "cancellare" dall'immagine in memoria le righe che non rispettano i criteri; e ho aggiunto una Sub che crea dall'immagine residua l'immagine da restituire.

Nota che prevedo che i risultati non vadano a sovrascrivere "intervallo2", ma siano scritti in un'area da definire nella parte iniziale della macro, vedi istruzione Set oRR =etc etc

Ho lasciato lo Stop a conclusione dell'indagine su ogni riga di Intervallo1, e idem la scrittura in colonna X accanto a "intervallo2" dell'esito del confronto; servono solo per i test, poi vanno eliminate ambedue le istruzioni (vedi righe "SOLO per TEST")

Io ho provato 200*5000 righe, i tempi sono abbastanza contenuti.

Prova...

[miko]

Buongiorno,
ho testato l’ultima macro con poche righe e poche colonne per capire il criterio adottato e vedere il risultato, perciò non posso esprimere giudizi sui tempi.
La macro funziona ed il criterio è ottimo.
Ti chiedo alcune cose:
Non capisco la funzione della variabile boleana myDbg, nei miei piccoli test rimane sempre impostata su false e non cambia mai, forse perché nei test non si verifica la condizione per la quale deve cambiare.

Non mi sono chiare le condizioni If myDbg in particolare If myDbg Then Debug.Print I, J, myCnt * 10 .

Riguardo alla Sub oArrCollapse,
invece di ripercorrere la matrice alla fine di tutto il confronto, non si potrebbe popolare una matrice temporanea riga dopo riga durante il ciclo di confronto?
Quando troviamo una riga buona inseriamo la riga nella matrice temporanea, array_temp, successivamente troviamo un’ altra riga che non deve essere eliminata e la aggiungiamo alla array_temp …, alla fine di ogni ciclo I avremo la array_temp sulla quale dobbiamo operare per con il ciclo del successivo indice I.
Cercando nel web ci sono vari esempi per concatenare, combinare array facendo uso di JOIN ed EVALUATE, ma non sono riuscito a modificarli per il mio caso.
ho fatto però queste 2 semplici macro che non riguardano il progetto ma mi servivano solo per verificare
il risultato del concetto.
Questa funziona

Codice: Seleziona tutto

Sub A()
   
    Dim arr1() As Variant, arr2() As Variant
   
    Dim I As Integer   

    With Worksheets("Foglio1")
   
    .Range("H4:L23").ClearContents
   
    arr1 = .Range("B4:F23").Value
 
 ReDim arr2(1 To (UBound(arr1, 1)), 1 To 5)
 
 I = 1

    For J = 1 To (UBound(arr2, 1)) Step 2

      'oppure con un ciclo che scorre le colonne
   
        arr2(I, 1) = arr1(J, 1)
       
        arr2(I, 2) = arr1(J, 2)
       
        arr2(I, 3) = arr1(J, 3)
       
        arr2(I, 4) = arr1(J, 4)
       
        arr2(I, 5) = arr1(J, 5)         
       
        .Range("H4:L23").Value = arr2    'per test
       
    I = I + 1
   
    Next J
   
    .Range("H4:L23").ClearContents
   
    .Range("H4:L23").Value = arr2
   
    End With
   
End Sub

Questa funziona parzialmente, perché popola solo riga per riga e non succede nulla alla riga evidenziata

Codice: Seleziona tutto

Sub B()
 
 Dim ArrSource As Variant, ArrDest As Variant, ArrSum As Variant

 On Error Resume Next
 
    myTim = Timer

    Application.ScreenUpdating = False
   
    Application.EnableEvents = False
   
    Application.Calculation = xlManual
 
  With ThisWorkbook.Worksheets("Foglio1")

    .Range("H4:L23").ClearContents
   
 ReDim ArrSum(1 To 20)
 
    I = 1

    For J = 1 To 20 Step 2
   
    ArrSource = .Range(.Cells(J + 3, "B"), .Cells(J + 3, "F"))

    'qui il range è una qualunque zona del foglio diverso dal range di confronto che deve essere pieno
 'altrimenti non funziona, io ho messo = 0

    ArrDest = .Range(.Cells(1, "B"), .Cells(1, "F"))
   
      ArrSum(I) = Application.WorksheetFunction.MMult(Array(1, 1), _
                                       Array(ArrSource, ArrDest))

     .Range("H" & I + 3 & ":L" & I + 3).Value = ArrSum(I)    'POPOLA IL FOGLIO   ok
   
    I = I + 1
   
    Next J

      .Range("H4:L23").ClearContents
   
    .Range("H4:L23").Value = ArrSum   '<<<< non funziona
   
     Erase ArrSource

     Erase ArrDest

     Erase ArrSum
   
  End With     
 
     Application.Calculation = xlCalculationAutomatic
 
     Application.EnableEvents = True
       
     Application.ScreenUpdating = True

End Sub

Che ne pensi?
intanto sto continuando a provare con join ed evaluate, ma credo che non ricaverò alcun risultato
Grazie per quello che hai realizzato, ciao

[Anthony47]

Ci sono 100 modi per cucinare le carote, e in genere ogni cuoco quando vuole andare sul sicuro si tiene sulle sue ricette...
Nel caso specifico creare un array alla fine o durante il controllo non credo che cambi la ricetta ne' il risultato; tra l'altro mi pare che non esista un momento in cui si puo' dire "ok, la riga e' da mantenere", in quanto puo' essere "cancellata" in qualsiasi riga di Intervallo2. E comunque la cancellazione in memoria serve a evitare la cancellazione sul foglio (penalizzantissima, in termine di tempi di esecuzione).

myDbg e' una tecnica che uso quando potrei aver bisogno di informazioni dalla macro: posizionandola su True abilito la scrittura di queste informazioni, lasciandola su False interferisce ben poco con l'esecuzione della macro. In genere ripulisco il codice pubblicato da queste linee di servizio, stavolta mi e' sfuggito.
Insomma puoi eliminare tutte le righe che cominciano con If myDbg Then, ma comunque se le lasci hanno pochissimo impatto sui tempi di esecuzione.

Ciao