FORMULE O MACRO

[Lucio Peruggini]

Buona serata a tutti.
Ciao Flash, Anthony, o chiunque voglia aiutarmi con una macro o formule adatte al quesito. Ho cercato di risolverlo con quel poco che conosco di Excel, ma gli esiti ottenuti non rispecchiano quanto vorrei ricavare.
Allego file e immagine

https://dl.dropboxusercontent.com/u/182 ... oni%20.PNG

https://dl.dropboxusercontent.com/u/182 ... IE%20.xlsx

Rispettando l’ordinamento della colonna “E” (piccolo-grande) che non deve assolutamente cambiare, vorrei ottenere quanto segue:

Partendo dal primo numero in “B8” = 37 al quale è aggregata una cinquina “F8” e scendendo giù escludendo i doppioni nelle due colonne, alla fine si dovrebbe ottenere:
Nella colonna “B” novanta numeri (1-90).
Nella colonna “F” novanta cinquine diverse le une dalle altre.
Si potrebbe anche solamente colorare in giallo il numero nella colonna “B”; l’importante è seguire l’ordine impostato della colonna “E” come da esempio riportato nell’immagine.

Grazie molte

[Anthony47]

Con formule:
In una colonna libera, io ho usato M, in M8 inserisci la formula

Codice: Seleziona tutto

 =(CONTA.SE(B$8:B8;B8)<2)+(CONTA.SE(F$8:F8;F8)<2)

Poi seleziona i dati in colonna B ed F e applica una formattazione condizionale con la formula

Codice: Seleziona tutto

=$M8=2

Risultato come da immagine:


Uploaded with ImageShack.us
(io l' ho applicata alle colonne C ed F, per mostrare il risultato rispetto a quanto avevi evidenziato tu)

Con macro:

Codice: Seleziona tutto

Sub highl()
Dim LastB As Long, I As Long, CIB As Boolean, CIF As Boolean
'
LastB = Cells(Rows.Count, "B").End(xlUp).Row
        Range("B8").Resize(LastB - 7).Interior.ColorIndex = xlNone
        Range("B8").Resize(LastB - 7).Interior.ColorIndex = xlNone

For I = 8 To LastB
    CIB = Application.WorksheetFunction.CountIf(Range("B8").Resize(I - 7), Cells(I, "B")) < 2
    CIF = Application.WorksheetFunction.CountIf(Range("F8").Resize(I - 7), Cells(I, "F")) < 2
    If CIB And CIF Then
        Cells(I, "B").Interior.Color = RGB(255, 255, 0)
        Cells(I, "F").Interior.Color = RGB(255, 255, 0)
    End If
Next I
End Sub


[Lucio Peruggini]

Ciao Anthony, è sfuggito probabilmente un passaggio oppure non mi sono spiegato correttamente.
Ho provato la macro e non colora la spia 68 "B14" e di conseguenza l'implementazione va avanti in modo errato.
Ai primi quattro numeri gialli che trovi in colonna "B" bisogna aggiungere anche il 68 che, pur avendo una cinquina identica a quella precedente (riga 11) è riferita a una spia non presa.
Se la cinquina del 68 fosse stata uguale a una delle altre precedenti con numero spia già marcato, in tal caso si bai passava.
Mi spiace, non avevo tenuto conto anche di questa possibilità che poteva verificarsi.

Grazie come sempre per l'aiuto, ciao.

DIMENTICANZA

La macro si è fermata alla riga 219 e non capisco il perché.

[Lucio Peruggini]

Penso che il giusto procedimento sia:

Si processa la colonna "B" prendendo il primo numero utile con la cinquina di pertinenza. Andando avanti e supponendo di aver già marcato cinque numeri e quindi cinque cinquine si passa alla sesta.
Se buona, cioè non ancora presa in precedenza, si controlla che la cinquina d'appartenenza non sia presente in numeri già smarcati, (colorati precedentemente).
Ciao e perdona la mia mancanza.

[Flash30005]

Ho modificato la macro per come ho inteso e spero vada bene

Codice: Seleziona tutto

Sub Colora()

Dim LastB As Long, I As Long

LastB = Cells(Rows.Count, "B").End(xlUp).Row
        Range("B8").Resize(LastB - 7).Interior.ColorIndex = xlNone
        Range("F8").Resize(LastB - 7).Interior.ColorIndex = xlNone

For I = 8 To LastB
    CIF2 = Application.WorksheetFunction.CountIf(Range(Cells(7, 6), Cells(I - 1, 6)), Cells(I, 6))
    CIB2 = Application.WorksheetFunction.CountIf(Range(Cells(7, 2), Cells(I - 1, 2)), Cells(I, 2))
    If CIF2 = 0 And CIB2 = 0 Then
        Cells(I, "B").Interior.Color = RGB(255, 255, 0)
        Cells(I, "F").Interior.Color = RGB(255, 255, 0)
        GoTo SaltaCol
    End If
    If CIB2 = 0 And CIF2 > 0 Then
        For NN = 8 To I - 1
            If Cells(NN, 6) = Cells(I, 6) And Cells(NN, 6).Interior.Color = RGB(255, 255, 0) Then GoTo SaltaCol
        Next NN
        Cells(I, "B").Interior.Color = RGB(255, 255, 0)
        Cells(I, "F").Interior.Color = RGB(255, 255, 0)
    End If
SaltaCol:
Next I
End Sub

Ciao

[Lucio Peruggini]

Grazie Anthony e Flash

Flash hai capito bene e il tutto funziona ma la macro deve andare avanti fintanto che la colonna "B" sia completa di tutti i novanta numeri (da uno a novanta).
Ora si ferma alla riga 218 con il 72.

Grazie molte

https://dl.dropboxusercontent.com/u/182 ... SPIE.1.PNG

[Flash30005]

Si ferma perché o la cinquina è già uscita con spia (diverso) trovato oppure il numero spia è già stato utilizzato con altra cinquina.
Trovami una condizione per la quale dovrebbe essere evidenziato un altro numero spia e io ci lavorerò su.

ciao

[Lucio Peruggini]

https://dl.dropboxusercontent.com/u/182 ... giunte.PNG

Ciao Flash
Quanto dici non è giusto! E' impossibile non chiudere il ciclo con le novanta spie complete e rispettive cinquine d'appartenenza l'una diversa dall'altra.

Nel foglio completo che ho inviato ci sono 9000 righe utili composte da cento cinquine diverse l'una dall'altra e quindi il ciclo è obbligato a chiudersi. Ciò non toglie che può anche succedere il contrario ma non certamente con sole 14 cinquine trovate dalla macro; ma non credo possa succedere.

Come vedi da immagine, io ne ho trovate sei (dalla spia 35 e prima dell'ultima marcata dalla macro che è la 72); intervallo riga 105-217. Marcate in arancione nella colonna "G".

Ciao

[Flash30005]

Questa dovrebbe fare al caso tuo ma...

Codice: Seleziona tutto

Sub Colora()

Dim LastB As Long, I As Long

LastB = Cells(Rows.Count, "B").End(xlUp).Row
        Range("B8").Resize(LastB - 7).Interior.ColorIndex = xlNone
        Range("F8").Resize(LastB - 7).Interior.ColorIndex = xlNone

For I = 8 To LastB
    CIF2 = Application.WorksheetFunction.CountIf(Range(Cells(7, 6), Cells(I - 1, 6)), Cells(I, 6))
    CIB2 = Application.WorksheetFunction.CountIf(Range(Cells(7, 2), Cells(I - 1, 2)), Cells(I, 2))
    If CIF2 = 0 And CIB2 = 0 Then
        Cells(I, "B").Interior.Color = RGB(255, 255, 0)
        Cells(I, "F").Interior.Color = RGB(255, 255, 0)
        GoTo SaltaCol
    Else
        For NN = 8 To I - 1
            If Cells(NN, 6) = Cells(I, 6) And Cells(NN, 6).Interior.Color = RGB(255, 255, 0) Then GoTo SaltaCol
            If Cells(NN, 2) = Cells(I, 2) And Cells(NN, 2).Interior.Color = RGB(255, 255, 0) Then GoTo SaltaCol
        Next NN
    End If
        Cells(I, "B").Interior.Color = RGB(255, 255, 0)
        Cells(I, "F").Interior.Color = RGB(255, 255, 0)
SaltaCol:
Next I
End Sub


...con il mio Pc impiego circa 5 minuti per elaborare

ciao

[Lucio Peruggini]

Grazie Flash, la provo.

Sapevo già che avrebbe impiegato del tempo ma non ha importanza; l'importante è avere i dati richiesti.
Domani ti faccio sapere, ciao.

Grazie anche a te Anthony.

[Lucio Peruggini]

Buona giornata

Ciao Flash, ha impiegato di sicuro più di due ore poi sono andato a nanna (questione di computer). Ad ogni modo svolge il suo lavoro egregiamente, i dati sono corretti. Si può aggiungere un timer, ma forse allungherebbe ancor più i tempi?

Grazie molte

[Anthony47]

Non sapendo nulla ne' di numeri spia ne' di figli di maria non ho afferrato bene il lavoro della macro, ma sono contento che funziona tutto.
Se queste due ore sono da aspettare spesso potrebbe essere utile cercare un algoritmo di piu' veloce esecuzione; cosa che potrei fare, senza vincolo di obiettivo, se mi esponete il problema in termini excel, lavorando sul file gia' pubblicato.

Ciao

[Flash30005]

@Anthony

Suppongo che riusciresti a farlo con funzioni confronto o mismatch che mi sembra permette di risalire alla rigarelativa.
Ma cerco di spiegare (se non si capisce dalla macro postata) cosa dovrebbe fare.
In colonna B ci sono i cosiddetti Numeri Spia e in colonna F le cinquine
Vanno evidenziati i numeri spia se la cinquina non è mai uscita prima (iniziando dalla riga 8 a scendere)
quindi sia B8 che F8 saranno evidenziati in quanto prima non c'è nulla
in riga 9 sarà evidenziato Spia (B9) e cinquina (F9) in quanto la cinquina non è mai uscita prima
e così in B10
In riga 11 non va evidenziata la spia in quanto già uscita (48 in B10), ricordarsi questo passo...
in riga 12 non verrà evidenziata né la spia (81) né la cinquina in quanto la cinquina è già uscita in riga 9 e ha il numero spia 12 evidenziato.
Riga 13 spia e cinquina da evidenziare in quanto la cinquina non è mai uscita e quindi nessun numero spia l'ha rilevata.
Riga 14 (qui difettava la tua macro) il 68 numero spia va evidenziato in quanto la cinquina che era già uscita in riga 11 non è stata rilevata dal numero 48 (B11) infatti non è evidenziato per i motivi precedenti (il 48 non era stato evidenziato in quanto già trovato riga 10 quindi la cinquina in F11 non è da considerare come rilevata da numero spia), non essendo stata considerata si dovrà considerare la cinquina in F14 con numero spia in B14 (quindi si deve evidenziare il numero 68 e la cinquina corrispondente).

Dopo questo penso che il resto venga da se...
ciao

[Flash30005]

aggiungo in sintesi quanto detto nel post precedente
Se la cinquina non è mai uscita va evidenziata (colorata con fondo giallo) a condizione che il valore in colonna B non è già stato evidenziato prima, altrimenti non va evidenziata.
Se la cinquina è già uscita ed è stata evidenziata, non è da considerare.
Se la cinquina è già uscita e non è stata evidenziata prima, va evidenziata purché in B (riga in considerazione) non ci sia un valore che è già stato evidenziato prima.

Mi sembra di aver detto tutte le condizioni.

ciao

[Lucio Peruggini]

Ciao Anthony, mi fai scoppiare dal ridere con le tue battute che fanno pure rima.

Stavo inviando questo messaggio e mi sono accorto che Flash ha già risposto.


Magari si riuscisse a velocizzare!

Penso però: il più adatto alle spiegazioni in questo caso sia Flash che ha capito il meccanismo da impostare.
Io potrei provarci ma non so se poi riesco nelle spiegazioni appropriate rischiando di farti perdere del tempo prezioso.
Vediamo se gentilmente Flash ha voglia e tempo per descrivere quanto occorre sapere.

Grazie molte.

GRAZIE FLASH!!!

[Lucio Peruggini]

https://dl.dropboxusercontent.com/u/182 ... 0MACRO.PNG

Ciao Anthony, cerco con questa immagine di spiegare a modo mio quanto già egregiamente fatto e detto da Flash; nella speranza e quando avrai tempo, che i tempi di elaborazione si possano restringere.

Non l'avrei chiesto ma il lavoro che mi sto apprestando a fare, è molto lungo.
Comunque se non si riesce, pazienza!

Ciao

[Anthony47]

Nonostante le ricche spiegazioni non ho capito la logica complessiva; mi sono limitato usare lo stesso algoritmo di Flash ma con qualche variante per velocizzare; il risultato e' questa macro, che migliora abbastanza:

Codice: Seleziona tutto

Sub Colora2()
' 16.6
Dim LastB As Long, I As Long, myArrB(), myArrF(), myColB, myColF, CI6 As String, CI2 As Long, myRGB As Long
'
LastB = Cells(Rows.Count, "B").End(xlUp).Row
    Range("B8").Resize(LastB - 7).Interior.ColorIndex = xlNone
    Range("F8").Resize(LastB - 7).Interior.ColorIndex = xlNone
myTim = Timer
ReDim myArrB(8 To LastB)
ReDim myArrF(8 To LastB)
'
myColB = Range("B1").Resize(LastB, 1).Value      '*3
myColF = Range("F1").Resize(LastB, 1).Value
myRGB = RGB(255, 255, 0)         '*4
For I = 8 To LastB
    CIF2 = Application.WorksheetFunction.CountIf(Range(Cells(7, 6), Cells(I - 1, 6)), Cells(I, 6))
    CIB2 = Application.WorksheetFunction.CountIf(Range(Cells(7, 2), Cells(I - 1, 2)), Cells(I, 2))
    If CIF2 = 0 And CIB2 = 0 Then
        myArrB(I) = myRGB
        myArrF(I) = myRGB
        GoTo SaltaCol
    Else
        CI6 = Cells(I, 6): CI2 = Cells(I, 2)    '*2
        For NN = 8 To I - 1
            If myColF(NN, 1) = CI6 And myArrF(NN) = myRGB Then GoTo SaltaCol
            If myColB(NN, 1) = CI2 And myArrB(NN) = myRGB Then GoTo SaltaCol
        Next NN
    End If
        myArrB(I) = myRGB
        myArrF(I) = myRGB
SaltaCol:
Next I
For I = 8 To LastB
    If myArrB(I) > 0 Then Cells(I, "B").Interior.Color = myArrB(I)
    If myArrF(I) > 0 Then Cells(I, "F").Interior.Color = myArrF(I)
Next I
MsgBox ("Completato in " & Timer - myTim)
End Sub


Ciao

[Lucio Peruggini]

Ciao Anthony, ma che cavolo hai combinato!

In trentasette secondi mi ha fatto la scansione; Flash ne impiegherà cinque o sei con il suo PC.

TROPPO FORTE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

GRAZIE MOLTE A ENTRAMBI
CIAO E BUONA GIORNATA

[Flash30005]

In 7,1 secondi netti

Sapevo che l'intervento di Anthony sarebbe stato risolutivo!

[Flash30005]

Però adesso non mi spiego questa riduzione del tempo perché le righe codice della macro sono le stesse cambia solo la "forma"
inoltre la macro di Anthony ha un ciclo for... next in più e riduce di 50 volte il tempo di esecuzione!?