Pam ha due colonne di dati. Nella colonna A ci sono identificatori semplici, come A, B, C, ecc. Nella colonna B ci sono una serie di valori interi.

Può ordinare i dati in base all’identificatore e, secondariamente, ai valori interi. Ora vuole, nella colonna C, avere una formula che concatenerà tutti i valori interi per un particolare identificatore. Pertanto, se A1: A4 contengono tutti l’identificatore A, nella cella C1 vorrebbe avere tutti i valori in B1: B4 concatenati e divisi da virgole, ad esempio “11, 17, 19, 25”. Poiché il numero di righe per ogni identificatore può essere diverso, Pam non è sicura di come procedere con la concatenazione.

Il modo più semplice per eseguire questa operazione è utilizzare una macro, che può essere creata come funzione definita dall’utente. Ecco un esempio:

Function CatSame(c As Range) As String     Application.Volatile     sTemp = ""

iCurCol = c.Column     If iCurCol = 3 Then         If c.Row = 1 Then             sLast = ""

Else             sLast = c.Offset(-1, -2)

End If         If c.Offset(0, -2) <> sLast Then             J = 0             Do                 sTemp = sTemp & ", " & c.Offset(J, -1)

J = J + 1             Loop While c.Offset(J, -2) = c.Offset(J - 1, -2)

sTemp = Right(sTemp, Len(sTemp) - 2)

End If     End If     CatSame = sTemp End Function

Questa funzione prende fondamentalmente un valore che le viene passato (un riferimento di cella) e verifica che il riferimento di cella sia per la colonna C.Se lo è, inizia a concatenare i valori dalla colonna B in base ai valori nella colonna A. restituisce la stringa di valori concatenati se il valore è la colonna A è diverso dal valore nella riga sopra di essa.

Supponendo che i tuoi identificatori siano nella colonna A e che i tuoi valori da concatenare siano nella colonna B, potresti inserire quanto segue nella colonna C:

=CatSame(C1)

Copia questo quanto necessario nella colonna C e finirai con esattamente ciò che Pam voleva.

Una funzione più versatile sarebbe quella che funzionerebbe in qualche modo come CERCA.VERT, ma riportare un elenco concatenato di valori che corrispondono a qualsiasi cosa tu stia cercando. Considera la seguente funzione:

Function VLookupAll(vValue, rngAll As Range, _   iCol As Integer, Optional sSep As String = ", ")

Dim rCell As Range     Dim rng As Range     On Error GoTo ErrHandler

Application.Volatile     Set rng = Intersect(rngAll, rngAll.Columns(1))

For Each rCell In rng         If rCell.Value = vValue Then _           VLookupAll = VLookupAll & sSep & _           rCell.Offset(0, iCol).Value     Next rCell

If VLookupAll = "" Then         VLookupAll = CVErr(xlErrNA)

Else         VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))

End If ErrHandler:

If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)

End Function

Questa funzione richiede fino a quattro argomenti. Il primo è il valore che vuoi abbinare nella tua ricerca. Nell’istanza di Pam, questo sarebbe l’identificatore che desideri, come A, B o C. Il secondo argomento è l’intervallo di celle in cui cercare le corrispondenze (colonna A in questo caso).

Il terzo argomento è un offset (dall’intervallo nel secondo argomento)

che rappresenta i valori che vuoi concatenare. Puoi usare la funzione in questo modo:

=VLookupAll("B",A1:A99,1)

Se vuoi specificare un diverso delimitatore tra i valori, puoi farlo usando il quarto argomento opzionale. Ad esempio, quanto segue restituisce una stringa in cui un trattino separa ogni valore:

=VLookupAll("B",A1:A99,1,"-")

Le soluzioni finora si sono concentrate sull’utilizzo delle macro. La ragione di ciò è relativamente semplice: non esiste una soluzione basata su formule che possa fare ciò di cui Pam ha bisogno. L’utilizzo di istruzioni IF annidate per valutare cosa c’è nella colonna A non funzionerà bene perché sei limitato nella profondità di annidamento delle istruzioni IF.

Potresti usare una formula e un risultato intermedio se non ti dispiace avere i valori concatenati nell’ultima istanza di un identificatore nella colonna A. Inizia inserendo questa formula nella cella C1:

=B1

Questa formula dovrebbe andare nella cella C2:

=IF(A2=A1,C1 & ", " & B2, B2)

Copia questa formula per tutte le righe necessarie. Ciò che si ottiene è una serie sempre più lunga di valori concatenati nella colonna C, con il più lungo in ciascuna esecuzione che si trova sulla stessa riga dell’ultimo identificatore sequenziale nella colonna A. È quindi possibile inserire quanto segue in tutte le celle della colonna applicabili D:

=IF(LEN(C2)>LEN(C1),"",C1)

Questa formula mostra solo le stringhe più lunghe dalla colonna C, che è ciò con cui Pam aveva bisogno per iniziare.

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (9197) si applica a Microsoft Excel 97, 2000, 2002 e 2003. Puoi trovare una versione di questo suggerimento per l’interfaccia a nastro di Excel (Excel 2007 e versioni successive) qui:

Concatenating Values ​​from a Variable Number of Cells.