[Excel Formula Matriciale] Conta dei valori duplicati

[devil22]

Buonasera,
Sono nuovo su questo forum e inizio con il farvi i complimenti perchè dopo aver letto diversi vostri topic ho risolto molti dubbi che avevo sulle formule excel.
Ho fatto riferimento al titolo di un vostro vecchio topic del 2012 [Excel Formula Matriciale] Conta univoca dei duplicati
perchè grazie a questo sono quasi riuscito ad ottenere ciò che cerco ma ancora ho bisogno di aiuto e vengo al dunque:
avendo nella colonna G una sequenza di numeri voglio conoscere se ogni 14 celle c'è una ripetizione e calcolare quando questa avviene!
Faccio un esempio per cercare di essere più chiaro:
considerando di avere
colonnaG
1
2
3
4
5
6
7
8
9
10
11
1
3
4
4
5
6
ecc... voglio avere nella colonna j un valore che mi dica inazittutto se c'è stata una ripetizione e in più dopo quante caselle è avvenuta!
Grazie alla formula sul vostro sito nel topic citato
=SE(SOMMA(SE(CONTA.SE(G2:G15;G2:G15)>1;1;""))>1;1;"")
confermata con crtl-maiu-invio
sono riuscito ad ottenere la verifica di una eventuale ripetizione nelle 14 caselle seguenti ma ora mi servirebbe sapere dopo quante caselle si è ripetuto.
Quindi tornando all'esempio sopra considerando la colonna g mi serve in J che nei primi 14 valori c'è un valore ripetuto(che è 1 in questo caso) e che si è ripetuto dopo 11 caselle quindi in J dovrà restituire il valore 11

Vi ringrazio in anticipo a chi vorrà aiutarmi in questo dilemma che non riesco a risolvere nemmeno in rete.
In attesa di un vostro cordiale aiuto vi saluto.
Mario

[Anthony47]

Ciao devil22, benvenuto nel forum.
Per non incartarci piu' di tanto io direi di risolverlo con una UserFunction.
Da Excel, premi Alt-F11 per aprire l'editor delle macrO, Menu /Inserisci /Modulo; copia questo codice e incollalo nel frame di dx:

Codice: Seleziona tutto

Function Distance(ByRef myBlock As Range, ByVal myF As Boolean) As Variant
'myF=False, distanza del primo Duplicato; myF=True, distanza piu' Bassa di duplicati
Dim myOut(1 To 2)

For i = 1 To myBlock.Count
    If Application.WorksheetFunction.CountIf(myBlock, myBlock.Cells(i, 1)) > 1 Then
        mymatch = Application.Match(myBlock.Cells(i, 1), myBlock.Offset(i + 0, 0), 0)
        If Not IsError(mymatch) Then
            If myF = False Then
                myOut(1) = mymatch
                myOut(2) = myBlock.Cells(i, 1)
                Distance = myOut
                Exit Function
            Else
                If mymatch < myOut(1) Or myOut(1) = "" Then
                myOut(1) = mymatch
                myOut(2) = myBlock.Cells(i, 1)
               
                End If
            End If
        End If
    End If
Next i
Distance = myOut
End Function


Poi torna su excel e in una cella scrivi la formula

Codice: Seleziona tutto

=Distance(G2:G16;0)

Questa ti restituira' la distanza tra il primo numero doppio dell'intervallo sottoposto alla funzione (G2:G16, nell' esempio).
Se usi invece

Codice: Seleziona tutto

=Distance(G2:G16;1)

otterrai invece la distanza piu' corta tra i duplicati presenti nell'intervallo.
Se non ci sono duplicati il risultato sara' 0

Volendo ottenere sia la distanza che il numero a cui si riferisce:
-selezionare due celle adiacenti sulla stessa riga
-scrivere la formula nella barra della formula
-confermare con Contr-Maiusc-Enter; la prima cella e' la distanza, la seconda il numero duplicato

Ciao