Calcolo valori non ripetuti ... ed altro

[scanacc]

Buongiorno, mi rivolgo di nuovo a voi in quanto è un paio d'ore che ci sbatto la testa e non riesco a venirne fuori.
Nella colonna A ho una serie di nomi degli agenti:
Mario Rossi
Giuseppe Verdi
Franco Nero
Nella colonna B ho una colonna delle città abbinate agli agenti:
Mario Rossi - Roma
Mario Rossi - Roma
Mario Rossi - Livorno
Mario Rossi - Livorno
Mario Rossi - Palermo
Mario Rossi - Firenze

Giuseppe Verdi - Lucca
Giuseppe Verdi - Lucca
Giuseppe Verdi - Trieste
Giuseppe Verdi - Bergamo
Giuseppe Verdi - Lucca

Franco Nero - Torino
Franco Nero - Torino
Franco Nero - Cuneo

Vorrei avere una formula che riesce a dirmi per ogni agente:
Il totale delle città (al netto delle ripetute) e il totale delle sole città non ripetute

Visto l'esempio di cui sopra vorrei in pratica che la formula mi fornisse il seguente risultato:

Mario Rossi
Totale delle città (al netto delle ripetute): 4 (che sarebbero Roma Livorno Palermo e Firenze)
Totale delle sole città non ripetute: 2 (che sarebbero Palermo e Firenze)

Giuseppe Verdi
Totale delle città (al netto delle ripetute): 3 (che sarebbero Lucca Trieste e Bergamo)
Totale delle sole città non ripetute: 2 (che sarebbero Trieste e Bergamo)

Franco Nero
Totale delle città (al netto delle ripetute): 2 (che sarebbero Torino e Cuneo)
Totale delle sole città non ripetute: 1 (che sarebbe Cuneo)

Sperando sia una cosa semplice vi saluto e grazie per il sicuro aiuto che vorrete offrirmi

[Anthony47]

E quale versione Office usi?

Se hai 365:
-in F1 calcoli l'elenco degli agenti con

Codice: Seleziona tutto

=UNICI(A1:A30)


In G1 l'elenco delle localita' associate al primo agente

Codice: Seleziona tutto

=SE(LUNGHEZZA(F1)>2;CONTA.SE($A$1:$A$30;F1);"")

Copia verso il basso per quanti Agenti pensi di avere

In H1 l'elenco delle localita' non ripetute:

Codice: Seleziona tutto

=SE(LUNGHEZZA(F1)>2;SOMMA(--(INDICE(UNICI($A$1:$B$30);0;1)=F1));"")

Copia verso il basso come la formula precedente

Ciao

[scanacc]

Grazie per l'aiuto Anthony.
e se volessi avere SOLO il numero delle CITTA' ripetute?
Ad esempio
MARIO ROSSI: 2 (Roma e Livorno)
GIUSEPPE VERDI: 1 (Lucca)
FRANCO NERO: 1 (Torino)
Grazie di nuovo Anthony

[Anthony47]

Deduco che usi Office 365 oppure Office 2021, ma anche con le sue prestazioni per cercare di ottenere una formula che non avesse bisogno di colonne di appoggio ho surriscaldato il neurone sinistro. Ho optato quindi per una “funzione personalizzata” corrispondente al seguente codice vba:

Codice: Seleziona tutto

 Function GimmeCities(ByRef myRan As Range) As Variant
Dim oArr() As Variant, agArr(), ctyArr(), ctyRng As Range
Dim myMatch, agI As Long, ctyI As Long, Dupl As String, acRows As Long, acCols As Long
'
'Debug.Print Application.Caller.Address
acRows = Application.Caller.Rows.Count
acCols = Application.Caller.Columns.Count

ReDim agArr(1 To myRan.Rows.Count)
ReDim ctyArr(1 To myRan.Rows.Count)
'Set ctyRng = Application.WorksheetFunction.Index(myRan, 0, 2)
'
For I = 1 To myRan.Rows.Count
    If Len(myRan.Cells(I, 1)) > 1 Then
        myMatch = Application.Match(myRan.Cells(I, 1).Value, agArr, False)
        If IsError(myMatch) Then
        agI = agI + 1
            agArr(agI) = myRan.Cells(I, 1).Value
            myMatch = agI
        End If
        ctyArr(myMatch) = ctyArr(myMatch) & "," & myRan.Cells(I, 2)
    End If
Next I
If agI > acRows Then
    ReDim oArr(1 To agI, 1 To 2)
Else
    ReDim oArr(1 To acRows, 1 To 2)
End If
For I = 1 To agI
    oArr(I, 1) = agArr(I)
    mySplit = Split(ctyArr(I) & ", ", ",", , vbTextCompare)
    Dupl = ""
    For j = 0 To UBound(mySplit)
        myMatch = Application.Match(mySplit(j), mySplit, False)
        If myMatch < (j + 1) Then
            If InStr(1, Dupl & " ", "-" & mySplit(j), vbTextCompare) = 0 Then
                oArr(I, 2) = oArr(I, 2) + 1
            End If
            Dupl = Dupl & "-" & mySplit(j)
        End If
    Next j
Next I
If acRows < agI And acCols = 2 Then oArr(acRows, 1) = ". . ."
For I = I To acRows
    oArr(I, 1) = ""
    oArr(I, 2) = ""
Next I
GimmeCities = oArr
End Function

Inserisci il codice in un modulo standard del tuo vba; poi torna su Excel e scrivi la formula

Codice: Seleziona tutto

=GimmeCities(A1:B20)

Invece di A1:B20 inserisci il tuo intervallo di Agente /Città

La formula restituisce una matrice a 2 colonne: i nomi sulla prima, il numero delle città duplicate sulla seconda
Se lavori con Office 365 la formula scrivera’ automaticamente i risultati sull’area necessaria, se questa area e’ libera (altrimenti ti sara’ visualizzato l’errore #ESPANSIONE!)
Se lavori con una versione che non supporta gli intervalli dinamici allora la formula va inserita come formula a matrice su un’area di X righe * 2 colonne:
-selezioni l’area di X righe * 2 colonne
-scrivi la formula nella barra della formula
-confermi con la combinazione Contr-Maiusc-Enter, non il solo Enter
Se le righe non sono sufficienti per contenere tutto l’elenco, l’ultima riga conterra’ “. . .”; puoi invece inserire la formula su un numero di righe superiore al bisogno, e in questo caso le righe inutili sarnno lasciate vuote

Prova e fai sapere...

[scanacc]

Tutto perfetto ... come sempre