FUnzione Utente o altro?

[papiriof]

Ho due colonne (come da immagine) la prima contiene dei codici la seconda dei numeri. Nella prima colonna ogni codice può essere
ripetuto ma, se ripetuto,nella seconda colonna corrispondono numeri diversi;viceversa nella seconda colonna ci possono essere
numeri uguali ma a numeri uguali corrispondono codici diversi.
La richiesta , magari può sembrare semplice ma non ci riesco, vorrei trovare una correlazione tra due codici .
Esempio :in una ipotetica funzione utente chiamata "CorrelazioneEsito" ( qui gli argomenti necessari,tra cui i due codici da confrontare)
se metto a confronto il codice "1BA01" e "1BA10" la funzione dovrebbe restituirmi 3 in quanto i due codici hanno 3 numeri in comune
se invece metto a confronto il codice "1BA01" e "1BA05" la funzione dovrebbe restituirmi 1 in quanto i due codici hanno solo 1 numero in comune
come si vede anche dall'immagine.
Avevo pensato a MATR.SOMMA.PRODOTTO ma non riesco a formularla ,forse con una funz utente ?!
PosizNum RigaEsito 1BA04 8684
1BA01 8690 1BA01 8690 1BA04 8714
1BA01 8702 1BA01 8702 1BA04 8717
1BA01 8715 1BA01 8715 1BA04 8719
1BA01 8685 1BA01 8685 1BA04 8722
1BA02 8717 1BA02 8717 1BA04 8736
1BA02 8719 1BA02 8719 1BA04 8743
1BA02 8743 1BA02 8743
1BA03 8690
1BA03 8714 1BA03 8690
1BA03 8714 1BA03 8714 1BA05 8690
1BA03 8739 1BA03 8717 1BA05 8727
1BA03 8755 1BA03 8739 1BA05 8758
1BA04 8684 1BA03 8755
1BA04 8714
1BA04 8717
1BA04 8719
1BA04 8722
1BA04 8736 1BA06 8697
1BA04 8743 1BA06 8774
1BA05 8690 1BA06 8788
1BA05 8727
1BA05 8758 1BA09 8776
1BA06 8697 1BA09 8797
1BA06 8774
1BA06 8788 1BA10 8690
1BA07 8690 1BA10 8702
1BA07 8769 1BA10 8715
1BA09 8776 1BA10 8731
1BA09 8797 1BA10 8741
1BA10 8690
1BA10 8702
1BA10 8715
1BA10 8731
1BA10 8741

[papiriof]

scusate per la mancanza dell'immagine le prime due colonne(PosizNum RigaEsito ) rispettivamente con sotto i codici e i numeri le altre 4 col (impaginate male) rappresentano una specifica per meglio evidenziare quanto volevo esporre.

[CANAPONE]

Ciao

in H1=1BA01
in H2=1BA10

per contare quanti sono i numeri distinti in comune

Codice: Seleziona tutto

=SOMMA(--(VAL.NUMERO(CONFRONTA(SE($A$1:$A$34=H1;B1:B34);($A$1:$A$34=H2)*$B$1:$B$34;0))))

da confermare con control+maisc+invio.


Saluti

[Zer0Kelvin]

Ciao.
In attesa del riscontro alla risposta di Canapone, posto una probabile UDF.
Visto che non hai specificato cosa dovrebbe fare la UDF in caso di cifre duplicate non ho approfondito.
La UDF conta le cifre di var1 che sono anche presenti in var2, anche se ripetute; per es il confronto fra 102232 e 659528 (oppure "102232 " e "659528") restituisce 3.

Codice: Seleziona tutto

Public Function CountEqualDigit(var1 As Variant, var2 As Variant) As Long
' conta le cifre di var1 che sono presenti in var2
Dim L1 As Long, L2 As Long, S1 As String, S2 As String, c1 As Long, c2 As Long, digit1 As Byte, digit2 As Byte
CountEqualDigit = -1 '(valore di errore; restituito in caso di parametri errati o vuoti
    If IsNumeric(var1) And IsNumeric(var2) Then
        S1 = Trim(CStr(var1))
        S2 = Trim(CStr(var2))
        L1 = Len(S1)
        L2 = Len(S2)
        If L1 > 0 And L2 > 0 Then
            CountEqualDigit = 0
            For c1 = 1 To L1
                digit1 = Val(Mid(S1, c1, 1))
                For c2 = 1 To L2
                    digit2 = Val(Mid(S2, c2, 1))
                    If digit1 = digit2 Then
                        CountEqualDigit = CountEqualDigit + 1
                        Exit For
                    End If
                Next c2
            Next c1
        End If
    End If
End Function

[papiriof]

Grazie CANAPONE funziona!!!
Ti chiedo troppo di commentare come funziona questo uso combinato di dierse formule (SOMMA,VAL.NUMERO,CONFRONTA e SE) soprattutto i due "- -" che funzione hanno ? ancora grazie p.

[CANAPONE]

Ciao,

il segmento

Codice: Seleziona tutto

=SE($A$1:$A$34=H1;B1:B34)

seleziona i numeri corrispondenti ad H1

CONFRONTA cerca questi numeri nella matrice di numeri prodotta nel secondo argomento

Codice: Seleziona tutto

($A$1:$A$34=H2)*$B$1:$B$34

che produce 34 numeri; tanti zero più i numeri corrispondendi ad H2.

Se CONFRONTA trova i primi numeri -prodotti da SE($A$1:$A$34=H1;B1:B34)- fra questo secondo gruppo di numeri, restituisce la posizione: un numero, non mi interessa quale.

Se non li trova restituisce messaggio d'errore.

VAL.NUMERO che contiene tutta la formula restituisce VERO, se CONFRONTA ha restituito la posizione (è un numero, quindi VERO)
FALSO se non trova nulla (il messaggio d'errore n/d produce FALSO).

Con il -- davanti VERO diventa 1, FALSO divento zero.


SOMMA addiziona questi 1.

[papiriof]

Ciao,

il segmento

Codice: Seleziona tutto

=SE($A$1:$A$34=H1;B1:B34)

seleziona i numeri corrispondenti ad H1

CONFRONTA cerca questi numeri nella matrice di numeri prodotta nel secondo argomento

Codice: Seleziona tutto

($A$1:$A$34=H2)*$B$1:$B$34

che produce 34 numeri; tanti zero più i numeri corrispondendi ad H2.

Se CONFRONTA trova i primi numeri -prodotti da SE($A$1:$A$34=H1;B1:B34)- fra questo secondo gruppo di numeri, restituisce la posizione: un numero, non mi interessa quale.

Se non li trova restituisce messaggio d'errore.

VAL.NUMERO che contiene tutta la formula restituisce VERO, se CONFRONTA ha restituito la posizione (è un numero, quindi VERO)
FALSO se non trova nulla (il messaggio d'errore n/d produce FALSO).

Con il -- davanti VERO diventa 1, FALSO divento zero.


SOMMA addiziona questi 1.

Ottima Spiegazione!! GRAZIE CANAPONE
Ringrazio per l'interessamento anche Zer0Kelvin

[CANAPONE]

Ciao,

grazie a te del generoso riscontro: non mi sembrava molto chiaro il tentativo di spiegare la formula.

Metti alla prova la formula: credo che se ci sono dei numeri uguali in corrispondenza di h1 la formula non restituisca il risultato atteso: ed è molto probabile che il Vba sia di maggiore aiuto.

Se questa casistica non esiste nel caso che stai gestendo ( per esempio due o più 8690 in corrispondenza di due o più 1BA01), la formula potrebbe andare.

Saluti

[CANAPONE]

Ciao,

così eviti i doppi conteggi

Codice: Seleziona tutto

=SOMMA(--(VAL.NUMERO(CONFRONTA(RIF.RIGA(1:100000);CONFRONTA(SE($A$1:$A$34=H1;B1:B34);($A$1:$A$34=H2)*$B$1:$B$34;0);0))))

Sempre control+maiusc+invio.

In atteso di soluzioni migliori.

Saluti

[papiriof]

Ciao,


Se questa casistica non esiste nel caso che stai gestendo ( per esempio due o più 8690 in corrispondenza di due o più 1BA01), la formula potrebbe andare.

Saluti

No, non possono esserci doppioni,quindi la formula va più che bene! piuttosto ... se posso approfittare ho la necessità di fare diversi confronti fra due codici diversi perchè il fine è quello di trovare due codici che hanno più numeri in comune.
Quindi devo cambiare più velocemente possibile i codici a questo scopo nelle caselle dove ci sono i due codici ho messo un convalida ma è lo stesso abbastanza lento, ci vorebbe che a far muovere i codici una casella di controllo ma queste agiscono su dei numeri non su codici alfanumerici....qualche idea?

[CANAPONE]

Ciao, porterei le serie di due codici da confrontare (H1 E H2) uno accanto all'altro (H1 ed I1): in H1:I50 -esempio scrivi tutte le coppie di codici e la stessa formula

=SOMMA(--(VAL.NUMERO(CONFRONTA(SE($A$1:$A$34=H1;B1:B34);($A$1:$A$34=I1)*$B$1:$B$34;0))))

Poi usi un semplice MAX , o un INDICE(..CONFRONTA(MAX... sui risultati che ottieni dalle formule matrice.

Forse si può fare tutto con una formula: no saprei dirti come, sicuramente perderesti parecchia sensibilità sui numeri da controllare.

Sempre che abbia capito

[papiriof]

Ciao, porterei le serie di due codici da confrontare (H1 E H2) uno accanto all'altro (H1 ed I1): in H1:I50 -esempio scrivi tutte le coppie di codici e la stessa formula

=SOMMA(--(VAL.NUMERO(CONFRONTA(SE($A$1:$A$34=H1;B1:B34);($A$1:$A$34=I1)*$B$1:$B$34;0))))

Poi usi un semplice MAX , o un INDICE(..CONFRONTA(MAX... sui risultati che ottieni dalle formule matrice.

Forse si può fare tutto con una formula: no saprei dirti come, sicuramente perderesti parecchia sensibilità sui numeri da controllare.

Sempre che abbia capito

Buongiorno , chiedo scusa aCANAPONE per il riscontro alla presente ma causa influenza ho fatto delle prove in modo saltuario; Sostanzialmente la soluzione proposta funziona ma in pratica è inapplicabile perchè i confronti li ho fatti con un codice fisso contro tutti gli altri solo che " tutti gli altri " sono proprio tanti..... (fatto una prova con 6800 codici contro uno fisso ed è subbentrato "calcola" eper finire ha impiegato più di mezz'ora!!!!) ma, una curiosità si può ovviare a calcola??

[Anthony47]

Non sono in grado di fare un collaudo spinto, per carenza di file esemplificativo, ma direi che questa macro potrebbe produrre i confronti richiesti (in sostituzione delle formule):

Codice: Seleziona tutto

Sub pappr()
Dim VArr1, I As Long, LastA As Long, Dest As String, V As Long, H As Long, rDim As Long
Dim RArr(), cPos As Variant, cRig As Long, myComm As Long
'
Dest = "K2"     '<<< L' area ove sara' creata la tabella esiti
'
LastA = Cells(Rows.Count, 1).End(xlUp).Row
VArr1 = Range("A2:B" & LastA).Value
'
Range(Dest).Resize(100, 100).ClearContents     ' AZZERA Area di creazione risultati
Range("A1:A" & LastA).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        Dest), Unique:=True
rDim = Range(Dest, Range(Dest).End(xlDown)).Count - 1
Range(Dest).Offset(1, 0).Resize(rDim, 1).Copy
Range(Dest).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
Application.CutCopyMode = False
r3d1 = Application.WorksheetFunction.Min(Range("B2:B" & LastA).Value)
r3d2 = Application.WorksheetFunction.Max(Range("B2:B" & LastA).Value)
ReDim RArr(1 To rDim, r3d1 To r3d2)
'riposiziona:
For I = LBound(VArr1, 1) To UBound(VArr1, 1)
    cPos = Application.Match(VArr1(I, 1), Range(Dest).Offset(0, 1).Resize(1, rDim), 0)
    RArr(cPos, VArr1(I, 2)) = 1
Next I
'Calcola:
For V = 1 To rDim - 1
    For H = V + 1 To rDim
        myComm = 0
        For I = LBound(RArr, 2) To UBound(RArr, 2)
            If RArr(V, I) <> "" Then
                If RArr(H, I) = RArr(V, I) Then
                    myComm = myComm + 1
                End If
            End If
        Next I
        Range(Dest).Offset(V, H).Value = myComm
    Next H
Next V
End Sub


Inseriscila in un modulo standard (es Modulo1), personalizza l' istruzione marcata <<< poi mandala in esecuzione.
La macro assume che il codice sia in colonna A e il numero in colonna B; verra' creata una tabella all' indirizzo specificato (K2, nel mio codice), con in verticale e in orizzontale i vari Codici, e all' incrocio il "numero di numeri" in comune.
Attenzione: non sapendo quanto sara' grande la tabella di destinazione la macro AZZERA un' area di 100 righe * 100 Colonne a partire dall' indirizzo specificato (K2 nel mio codice); tienilo in conto quando decidi dove creare la tabella degli esiti.

Ciao, fai sapere.

[papiriof]

Non sono in grado di fare un collaudo spinto, per carenza di file esemplificativo, ma direi che questa macro potrebbe produrre i confronti richiesti (in sostituzione delle formule):

Codice: Seleziona tutto

Sub pappr()
Dim VArr1, I As Long, LastA As Long, Dest As String, V As Long, H As Long, rDim As Long
Dim RArr(), cPos As Variant, cRig As Long, myComm As Long
'
Dest = "K2"     '<<< L' area ove sara' creata la tabella esiti
'
LastA = Cells(Rows.Count, 1).End(xlUp).Row
VArr1 = Range("A2:B" & LastA).Value
'
Range(Dest).Resize(100, 100).ClearContents     ' AZZERA Area di creazione risultati
Range("A1:A" & LastA).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        Dest), Unique:=True
rDim = Range(Dest, Range(Dest).End(xlDown)).Count - 1
Range(Dest).Offset(1, 0).Resize(rDim, 1).Copy
Range(Dest).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
Application.CutCopyMode = False
r3d1 = Application.WorksheetFunction.Min(Range("B2:B" & LastA).Value)
r3d2 = Application.WorksheetFunction.Max(Range("B2:B" & LastA).Value)
ReDim RArr(1 To rDim, r3d1 To r3d2)
'riposiziona:
For I = LBound(VArr1, 1) To UBound(VArr1, 1)
    cPos = Application.Match(VArr1(I, 1), Range(Dest).Offset(0, 1).Resize(1, rDim), 0)
    RArr(cPos, VArr1(I, 2)) = 1
Next I
'Calcola:
For V = 1 To rDim - 1
    For H = V + 1 To rDim
        myComm = 0
        For I = LBound(RArr, 2) To UBound(RArr, 2)
            If RArr(V, I) <> "" Then
                If RArr(H, I) = RArr(V, I) Then
                    myComm = myComm + 1
                End If
            End If
        Next I
        Range(Dest).Offset(V, H).Value = myComm
    Next H
Next V
End Sub


Inseriscila in un modulo standard (es Modulo1), personalizza l' istruzione marcata <<< poi mandala in esecuzione.
La macro assume che il codice sia in colonna A e il numero in colonna B; verra' creata una tabella all' indirizzo specificato (K2, nel mio codice), con in verticale e in orizzontale i vari Codici, e all' incrocio il "numero di numeri" in comune.
Attenzione: non sapendo quanto sara' grande la tabella di destinazione la macro AZZERA un' area di 100 righe * 100 Colonne a partire dall' indirizzo specificato (K2 nel mio codice); tienilo in conto quando decidi dove creare la tabella degli esiti.

Ciao, fai sapere.

Grazie per l'interessamento Anthony , ho fatto come da te detto ossia :nella colonna A (da A1) ci sono 6800 codici alcuni di essi in diversamente ripetuti a fianco dei quali(quindi a cominciare da B1 ) i 6800 numeri anch'essi diversamente ripetuti, ho messo un bottone per laciare la macro (che non ho cambiato) ed il risultato è stato che mi si è popolata la col K a partire da K2 fino a K1351
ovvero tutti i codici SENZA I DOPPIONI come giustamente dovrebbe essere ma curiosamente ripetuto il primo codice "1BA01".
Il codice si blocca a questo punto :
Range(Dest).Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ovviamente c'è solo questo vettore ma non la tabella , ho sbagliato qualcosa?

[Anthony47]

Quindi hai 1500 codici diversi...
La macro cerca di creare una tabella di 1500 righe per 1500 colonne; se non ci riesce e' perche' stai lavorando su XL2007 in compatibility mode, che offre solo 256 colonne.
Ti conviene passare a modalita' 2007...

Ciao

[papiriof]

Grazie Anthony ho seguito il consiglio sono passato a 2007 e adesso funziona perfettamente , ci mette un pochino ma niente a paragone con il precedente!!!!

[Anthony47]

La macro calcola 1.125.000 correlazioni, direi che qualche decina di decina di secondi gli servono tutti...
Alla prossima.