Vai al contenuto principale

Come concatenare le celle se lo stesso valore esiste in un'altra colonna in Excel?

Autore: Silvia Ultima modifica: 2023-04-23

Come mostrato nello screenshot qui sotto, se vuoi concatenare le celle nella seconda colonna in base agli stessi valori nella prima colonna, ci sono diversi metodi che puoi usare. In questo articolo, introdurremo tre modi per eseguire questa operazione.

combina se stesso


Celle concatenate se lo stesso valore con formule e filtro

Le seguenti formule aiutano a concatenare il contenuto delle celle corrispondenti in una colonna in base allo stesso valore in un'altra colonna.

1. Seleziona una cella vuota oltre alla seconda colonna (qui selezioniamo la cella C2), inserisci la formula = SE (A2 <> A1, B2, C1 & "," e B2) nella barra della formula, quindi premere il pulsante entrare chiave.

2. Quindi selezionare la cella C2 e trascinare la maniglia di riempimento verso il basso sulle celle che è necessario concatenare.

3. Immettere la formula = SE (A2 <> A3, CONCATENATE (A2, "," "", C2, "" ""), "") nella cella D2 e ​​trascina la maniglia di riempimento verso il basso sulle celle rimanenti.

4. Selezionare la cella D1 e fare clic Dati > Filtro. Vedi screenshot:

5. Fare clic sulla freccia a discesa nella cella D1, deselezionare il (Spazi vuoti) casella, quindi fare clic su OK pulsante.

Puoi vedere che le celle sono concatenate se i valori della prima colonna sono gli stessi.

Note:: Per utilizzare correttamente le formule di cui sopra, gli stessi valori nella colonna A devono essere continui.


Concatena facilmente le celle se lo stesso valore con Kutools per Excel (diversi clic)

Il metodo sopra descritto richiede la creazione di due colonne helper e comporta più passaggi, che potrebbero risultare scomodi. Se stai cercando un modo più semplice, considera l'utilizzo di Combina righe avanzate utensile dal Kutools for Excel. Con pochi clic, questa utility ti consente di concatenare le celle utilizzando un delimitatore specifico, rendendo il processo rapido e senza problemi.

Consiglio: Prima di applicare questo strumento, installare Kutools for Excel in primo luogo. Vai al download gratuito ora.

1. Clic Kutools > Unisci e dividi > Combina righe avanzate per attivare questa funzionalità.
2. Nel Combina righe avanzate finestra di dialogo, devi solo:
  • Seleziona l'intervallo che desideri concatenare;
  • Imposta la colonna con gli stessi valori di Chiave primaria colonna.
  • Specificare un separatore per unire le celle.
  • Clicchi OK.

Risultato

Note::

Celle concatenate se lo stesso valore con codice VBA

È inoltre possibile utilizzare il codice VBA per concatenare le celle in una colonna se lo stesso valore esiste in un'altra colonna.

1. Stampa altro + F11 chiavi per aprire il Applicazioni Microsoft Visual Basic finestra.

2. Nel Applicazioni Microsoft Visual Basic finestra, fare clic inserire > Moduli. Quindi copia e incolla il codice sottostante nel file Moduli finestra.

Codice VBA: concatena le celle se gli stessi valori

Sub ConcatenateCellsIfSameValues()
	Dim xCol As New Collection
	Dim xSrc As Variant
	Dim xRes() As Variant
	Dim I As Long
	Dim J As Long
	Dim xRg As Range
	xSrc    = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(, 2)
	Set xRg = Range("D1")
	On Error Resume Next
	For I = 2 To UBound(xSrc)
		xCol.Add xSrc(I, 1), TypeName(xSrc(I, 1)) & CStr(xSrc(I, 1))
	Next I
	On Error GoTo 0
	ReDim xRes(1 To xCol.Count + 1, 1 To 2)
	xRes(1, 1) = "No"
	xRes(1, 2) = "Combined Color"
	For I = 1 To xCol.Count
		xRes(I + 1, 1) = xCol(I)
		For J = 2 To UBound(xSrc)
			If xSrc(J, 1) = xRes(I + 1, 1) Then
				xRes(I + 1, 2) = xRes(I + 1, 2) & ", " & xSrc(J, 2)
			End If
		Next J
		xRes(I + 1, 2) = Mid(xRes(I + 1, 2), 2)
	Next I
	Set xRg = xRg.Resize(UBound(xRes, 1), UBound(xRes, 2))
	xRg.NumberFormat = "@"
	xRg = xRes
	xRg.EntireColumn.AutoFit
End Sub

Note:

1. D1 nella linea Imposta xRg = Range ("D1") significa che il risultato verrà posizionato a partire dalla cella D1.
2. 'Non" e "Colore combinato" nella linea xRes (1, 1) = "No" e xRes (1, 2) = "Combined Color" sono le intestazioni delle colonne selezionate. 

3. premi il F5 chiave per eseguire il codice, quindi otterrai i risultati concatenati nell'intervallo specificato.


Concatena facilmente le celle se lo stesso valore con Kutools per Excel

I migliori strumenti per la produttività in ufficio

🤖 Assistente AI di Kutools: Rivoluziona l'analisi dei dati basandosi su: Esecuzione intelligente   |  Genera codice  |  Crea formule personalizzate  |  Analizzare i dati e generare grafici  |  Richiama le funzioni di Kutools...
Funzioni popolari: Trova, evidenzia o identifica i duplicati   |  Elimina righe vuote   |  Combina colonne o celle senza perdere dati   |   Round senza formula ...
Super ricerca: VLookup a criteri multipli    VLookup a valori multipli  |   VLookup su più fogli   |   Ricerca fuzzy ....
Elenco a discesa avanzato: Crea rapidamente un elenco a discesa   |  Elenco a discesa dipendente   |  Elenco a discesa a selezione multipla ....
Gestore di colonna: Aggiungi un numero specifico di colonne  |  Sposta colonne  |  Attiva/disattiva lo stato di visibilità delle colonne nascoste  |  Confronta intervalli e colonne ...
Funzionalità in primo piano: Messa a fuoco della griglia   |  Vista di progettazione   |   Grande barra delle formule    Gestore di cartelle di lavoro e fogli   |  Resource Library (Testo automatico)   |  Date picker   |  Combina fogli di lavoro   |  Crittografa/decrittografa le celle    Invia e-mail per elenco   |  Super filtro   |   Filtro speciale (filtro grassetto/corsivo/barrato...) ...
I 15 migliori set di strumenti12 Testo Strumenti (aggiungi testo, Rimuovi personaggi, ...)   |   50+ Grafico Tipi (Diagramma di Gantt, ...)   |   40+ Pratico Formule (Calcola l'età in base al compleanno, ...)   |   19 Inserimento Strumenti (Inserisci il codice QR, Inserisci immagine dal percorso, ...)   |   12 Conversione Strumenti (Numeri in parole, Conversione di valuta, ...)   |   7 Unisci e dividi Strumenti (Combina righe avanzate, Celle divise, ...)   |   ... e altro ancora

Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo.  Fai clic qui per ottenere la funzionalità di cui hai più bisogno...

Descrizione


Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
Comments (23)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thank you very much, the VBA code works very well but I have a question that I can't solve.

NO Color
1 red
1 blue
1 red
1 yellow
2 red
2 yellow


in this case, in column B we have some duplicate colors related to the same number. How can I have an output that only return the values once?

I would like an output like this

NO Color
1 red, blue, yellow
2 red, yellow
This comment was minimized by the moderator on the site
Hi

The second method described in the post can help you solve this problem. After following the steps to specify the settings, checking the Delect Duplicate Values option will delete all duplicates in the combined results.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/concatenate.png
This comment was minimized by the moderator on the site
Thank you so much for this formula! It saved hours of investigation! Fantastic work :)
This comment was minimized by the moderator on the site
Hi Crystal,

Thanks for your reply. I'll have to put my thinking hat on again...
Thank you
This comment was minimized by the moderator on the site
Hello, the macro above works great! Thank you. Is it anyway possible (I tried, but not successful) to amend it to concatenate column B (description) based on duplicates in column A (HS code) but also taking column C (origin) in the combination?
So description texts concatenate per HS code per origin.
Also column D(quantity) and column E(value) must be summed per HS code and origin.

A B C D E
HS CODE DESCRIPTION ORIGIN QUANTITY VALUE
5407420000 TWEED CN 10 € 150,00
5407420000 COTTON CN 15 € 250,00
5407420000 POLYESTER TW 5 € 130,00
5407420000 ORGANIC COTTON US 18 € 450,00
5407420000 COTTON US 23 € 780,00
5407420000 VELVET DELUXE CN 20 € 380,00
5407420000 COTTON CN 10 € 120,00
5806310000 TWEED JP 15 € 35,00
5806310000 POLYESTER AU 20 € 78,00
5806310000 TWEED AU 25 € 254,00
5806310000 POLYESTER SG 130 € 888,00
5806310000 COTTON EU 120 € 945,00
5806310000 COTTON FABRIC EU 10 € 80,00


Result shopuld be:

5407420000 TWEED, COTTON, VELVET DULUXE, COTTON, CN 55 € 900,00
5407420000 POLYESTER TW 5 € 130,00
5407420000 ORGANIC COTTON, COTTON US 41 € 1230,00
5806310000 TWEED JP 15 € 35,00
5806310000 POLYESTER, TWEED AU 45 € 332,00
5806310000 POLYESTER SG 130 € 888,00
5806310000 COTTON, COTTON FABRIC EU 130 € 1025,00

I hope this possible, thank you!
This comment was minimized by the moderator on the site
Hi Ivar,

Thank you for your comment. I am not able to solve this problem with VBA code yet. Sorry for that.
You can try if the last method can help.
This comment was minimized by the moderator on the site
Hello, how would the VBA code be adjusted if I want to combine cells in column M based on the duplicates in column A?
This comment was minimized by the moderator on the site
Hi Kristin,To combine cells in column M based on the duplicates in column A, try the VBA below.In the code,  O1 is the first cell to output the results; M is the column you will combine based on the duplicates in column A; A1 and A represent the first cell and the column where the duplicates locate; No and Combine color is the header of the columns after concatenating. You can change these variables as needed.<div data-tag="code">Sub ConcatenateCellsIfSameValues()
'Updated by Extendoffice 20211105
Dim xCol As New Collection
Dim xSrc As Variant
Dim xSrcValue As Variant
Dim xRes() As Variant
Dim I As Long
Dim J As Long
Dim xRg As Range
Dim xResultAddress As String
Dim xMergeAddress As String
Dim xUp As Integer

xResultAddress = "O1" 'The cell to output the results
xMergeAddress = "M" 'The column you will combine based on the duplicates in column A

xSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(, 1)
xUp = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Rows.Count
xSrcValue = Range(xMergeAddress & "1:" & xMergeAddress & xUp)

Set xRg = Range(xResultAddress)
On Error Resume Next
For I = 2 To UBound(xSrc)
xCol.Add xSrc(I, 1), TypeName(xSrc(I, 1)) & CStr(xSrc(I, 1))
Next I

On Error GoTo 0
ReDim xRes(1 To xCol.Count + 1, 1 To 2)
xRes(1, 1) = "No"
xRes(1, 2) = "Combined Color"
For I = 1 To xCol.Count
xRes(I + 1, 1) = xCol(I)
For J = 2 To UBound(xSrc)
If xSrc(J, 1) = xRes(I + 1, 1) Then
xRes(I + 1, 2) = xRes(I + 1, 2) & ", " & xSrcValue(J, 1)
End If
Next J
xRes(I + 1, 2) = Mid(xRes(I + 1, 2), 2)
Next I
Set xRg = xRg.Resize(UBound(xRes, 1), UBound(xRes, 2))
xRg.NumberFormat = "@"
xRg = xRes
xRg.EntireColumn.AutoFit
End Sub
This comment was minimized by the moderator on the site
I just wanted to thank you. It wasn't exactly what I wanted but man did it help me figure out what to do.

I have a table where the person's name was in column A, dates in column B and the names of tools they use in the headers of columns C:G . In each column there is a "Y" if they used that tool on that date and blank if they did not. (FYI: the same person can be listed more than once and may have used the same tool more than once) On a separate (summary) page I wanted to list all tools each person used within a date period, only listing each tool they used once, in the same cell. On this page, the person's name was in column A, Types of tools used in column B and the helper columns were in column G:K. Here's what I got:
The first helper column (G2):
=IF(COUNTIFS(Table7[Person's Name],A2,Table7[Screw Driver],"Y",Table7[Date],">="&1/1/20,Table7[Date],"<="&3/31/20),"Screw Driver","")
In the last helper column (K2):
=IF(COUNTIFS(Table7[Person's Name],A2,Table7[Hammer],"Y",Table7[Date],">="&1/1/20,Table7[Date],"<="&3/31/20),IF(J2="","Hammer",J2&"/"&"Hammer"),J2)

In B2 I just entered =K2

Thanks again and I hope this helps someone. EZPD
This comment was minimized by the moderator on the site
Hi, first of all thanks for creating this resource. I have been trying to figure this out for a couple of hours and I'm stuck. I'm using your 'concatenate cells if same value' but my script is looking at column "D" instead of "A. I can't figure out how to get it to use a different column for the data besides the one right next to it. In my cases I want it to look at column "D" to see if the value is the same and if so, it will grab the data from column "H" and put that data from column "H' into a cell in column "J". How do I switch this to use column "H" for the data? Thx


Sub ConcatenateCellsIfSameValues()
Dim xCol As New Collection
Dim xSrc As Variant
Dim xRes() As Variant
Dim I As Long
Dim J As Long
Dim xRg As Range
xSrc = Range("D1", Cells(Rows.Count, "D").End(xlUp)).Resize(, 2)
Set xRg = Range("J1")
On Error Resume Next
For I = 2 To UBound(xSrc)
xCol.Add xSrc(I, 1), TypeName(xSrc(I, 1)) & CStr(xSrc(I, 1))
Next I
On Error GoTo 0
ReDim xRes(1 To xCol.Count + 1, 1 To 2)
xRes(1, 1) = "No"
xRes(1, 2) = "Products"
For I = 1 To xCol.Count
xRes(I + 1, 1) = xCol(I)
For J = 2 To UBound(xSrc)
If xSrc(J, 1) = xRes(I + 1, 1) Then
xRes(I + 1, 2) = xRes(I + 1, 2) & vbCrLf & xSrc(J, 2)
End If
Next J
xRes(I + 1, 2) = Mid(xRes(I + 1, 2), 2)
Next I
Set xRg = xRg.Resize(UBound(xRes, 1), UBound(xRes, 2))
xRg.NumberFormat = "@"
xRg = xRes
xRg.EntireColumn.AutoFit
End Sub
This comment was minimized by the moderator on the site
"I can't figure out how to get it to use a different column for the data besides the one right next to it. In my cases I want it to look at column 'D' to see if the value is the same and if so, it will grab the data from column 'H' and put that data from column 'H' into a cell in column 'J'."

Did you ever figure this out?
This comment was minimized by the moderator on the site
Hi,

looks like 2 of your formulas are wrong :

=IF(A2<>A3,CONCATENATE(A2,",""",C2,""""),""). You need to change "A2" to "D1". As you'll want to add the string to the previous cell.

same goes for this formula :

=IF(A2<>A1,B2,C1 & "," & B2) : Change C1 to D1.


kind regards


Harry
This comment was minimized by the moderator on the site
I use this VBA for lots of my spreadsheets and its great. But the spreadsheets have become very large 50k+ rows and it doesnt seem to be working any more. If I use it on 1000 rows it works fine but large sets of data it cant seem to cope with. No errors just no results. Any help would be appreciated.
This comment was minimized by the moderator on the site
Hi James,
I tested the code as you mentioned, but it still works well in my case even I set the rows to 1000+.
This comment was minimized by the moderator on the site
Using the VBA macro and getting great results, I have tried tweaking it slightly for my needs but cant get it to work so I hope you can help.

Which bit do I change to make it concat a specific column, not the one directly to the right of the xSrc = Range?

Thanks for your great work!
This comment was minimized by the moderator on the site
Or as a better option, if you had 3 columns instead of 2 and found duplicates in column A (like your example) can you concat column B into a cell and column C into a seperate cell? So if you had columns of Number, Colour, Age, could you concat colour and age into different columns upon finding duplicates in Number? Hope that makes sense!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations