Gå til hovedindhold

Hvordan sammenkædes let tekst baseret på kriterier i Excel?

Antag, at jeg har en kolonne med ID-numre, der indeholder nogle duplikater og en kolonne med navne, og nu vil jeg sammenkæde navnene baseret på de unikke ID-numre som vist skærmbillede til venstre for hurtigt at kombinere teksten baseret på kriterier, hvordan kunne vi gøre i Excel?

doc kombiner tekst baseret på kriterier 1

Sammenkæd tekst baseret på kriterier med brugerdefineret funktion

Sammenkæd tekst baseret på kriterier med Kutools til Excel


For at kombinere tekst med de unikke ID-numre kan du først udpakke de unikke værdier og derefter oprette en brugerdefineret funktion for at kombinere navnene baseret på det unikke ID.

1. Tag følgende data som eksempel, du skal først udpakke de unikke ID-numre. Anvend venligst denne matrixformel: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Indtast denne formel i en tom celle, f.eks. D2, og tryk derefter på Ctrl + Skift + Enter nøgler sammen, se skærmbillede:

doc kombiner tekst baseret på kriterier 2

Tip: I ovenstående formel A2: A15 er det listedataområde, du vil udtrække unikke værdier fra, D1 er den første celle i den kolonne, du vil lægge udtrækningsresultatet ud.

2. Og træk derefter fyldhåndtaget ned for at udtrække alle unikke værdier, indtil blanke vises, se skærmbillede:

doc kombiner tekst baseret på kriterier 3

3. I dette trin skal du oprette en Brugerdefineret funktion Hvis du vil kombinere navnene baseret på de unikke ID-numre, skal du holde nede ALT + F11 tasterne, og det åbner Microsoft Visual Basic til applikationer vindue.

4. Klik indsatte > Moduler, og indsæt følgende kode i Moduler Vindue.

VBA-kode: sammenkædet tekst baseret på kriterier

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

5. Gem derefter og luk denne kode, gå tilbage til dit regneark, og indtast denne formel i celle E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , se skærmbillede:

doc kombiner tekst baseret på kriterier 4

6. Træk derefter fyldhåndtaget ned til de celler, som du vil anvende denne formel, og alle de tilsvarende navne er blevet kombineret baseret på ID-numrene, se skærmbillede:

doc kombiner tekst baseret på kriterier 5

tips:

1. I ovenstående formel A2: A15 er de originale data, som du vil kombinere baseret på, D2 er den unikke værdi, du har ekstraheret, og B2: B15 er den kolonne, du vil kombinere sammen.

2. Som du kan se, kombinerede jeg de værdier, der er adskilt med komma, du kan bruge andre tegn ved at ændre kommaet "," med formlen efter behov.


Hvis du har Kutools til Excel, Med Avancerede kombinere rækker værktøj, kan du hurtigt og bekvemt sammenkæde tekstbasen efter kriterier.

Kutools til Excel : med mere end 300 praktiske Excel-tilføjelsesprogrammer, gratis at prøve uden begrænsning på 30 dage.

Efter installation Kutools til Excel, gør som følger:

1. Vælg det dataområde, du vil kombinere, baseret på en kolonne.

2. Klik Kutools > Flet og del > Avancerede kombinere rækker, se skærmbillede:

3. I Kombiner rækker baseret på kolonne Klik på ID-kolonnen, og klik derefter på Primærnøgle for at gøre denne kolonne til den nøglekolonne, som dine kombinerede data er baseret på, se skærmbillede:

doc kombiner tekst baseret på kriterier 7

4. Og klik derefter på Navn kolonne, som du vil kombinere værdierne, og klik derefter på Kombiner mulighed, og vælg en separator til de kombinerede data, se skærmbillede:

doc kombiner tekst baseret på kriterier 8

5. Når du er færdig med disse indstillinger, skal du klikke på OK for at afslutte dialogen, og dataene i kolonne B er kombineret baseret på nøglekolonnen A. Se skærmbillede:

doc kombiner tekst baseret på kriterier 9

Med denne funktion løses følgende problem så hurtigt som muligt:

Hvordan kombineres flere rækker til en og sum duplikater i Excel?

Download og gratis prøveversion Kutools til Excel nu!


Kutools til Excel: med mere end 300 praktiske Excel-tilføjelsesprogrammer, gratis at prøve uden begrænsning på 30 dage. Download og gratis prøveversion nu!

Bedste kontorproduktivitetsværktøjer

🤖 Kutools AI Aide: Revolutionér dataanalyse baseret på: Intelligent udførelse   |  Generer kode  |  Opret brugerdefinerede formler  |  Analyser data og generer diagrammer  |  Aktiver Kutools funktioner...
Populære funktioner: Find, fremhæv eller identificer dubletter   |  Slet tomme rækker   |  Kombiner kolonner eller celler uden at miste data   |   Runde uden formel ...
Super opslag: VLookup med flere kriterier    Multiple Value VLookup  |   VOpslag på tværs af flere ark   |   Fuzzy Lookup ....
Avanceret rulleliste: Opret hurtigt rulleliste   |  Afhængig rulleliste   |  Multivælg rulleliste ....
Column Manager: Tilføj et bestemt antal kolonner  |  Flyt kolonner  |  Skift synlighedsstatus for skjulte kolonner  |  Sammenlign områder og kolonner ...
Fremhævede funktioner: Grid fokus   |  Designvisning   |   Stor Formel Bar    Arbejdsbog & Ark Manager   |  Ressourcebibliotek (Autotekst)   |  Datovælger   |  Kombiner regneark   |  Krypter/Dekrypter celler    Send e-mails efter liste   |  Superfilter   |   Specielt filter (filter fed/kursiv/gennemstreget...) ...
Top 15 værktøjssæt12 tekst Værktøjer (tilføje tekst, Fjern tegn, ...)   |   50 + Chart Typer (Gantt kort, ...)   |   40+ Praktisk formler (Beregn alder baseret på fødselsdag, ...)   |   19 Indsættelse Værktøjer (Indsæt QR-kode, Indsæt billede fra sti, ...)   |   12 Konvertering Værktøjer (Tal til ord, Valutaomregning, ...)   |   7 Flet og del Værktøjer (Avancerede kombinere rækker, Opdel celler, ...)   |   ... og mere

Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid.  Klik her for at få den funktion, du har mest brug for...

Beskrivelse


Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
Comments (38)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Great function! Is there a way to maintain the format in the cell it's concatenating data from? i.e. $45.07, $555.34, $0.00, $0.25, -$12.25 I've figured out how to stack them with wrap text and CHAR(10) in place of "," but having trouble keeping the format. I will be using this for a mail merge in Word.
This comment was minimized by the moderator on the site
Hello, Laurie,If you want to keep the cell formatting when concatenating the data, you can apply the Advanced Combine Rows feature of Kutools for Excel, in the dialog box, after finishing the settings, you just need to check Use formatted values option, and all the data formatting will be kept as you need.
You can download Kutools for Excel and free trial 30-day.
This comment was minimized by the moderator on the site
Is there any way to add "and" instead of "," before the last data? (For example: D2355, D2273, D2397, D2600 and D2386)
This comment was minimized by the moderator on the site
Hi, Hossain,May be there is not a direct method for solving your problem, you can add another formula to convert the last comma to the text "and".=SUBSTITUTE(E2,","," and ",LEN(E2)-LEN(SUBSTITUTE(E2,",","")))
Please try, thank you!
This comment was minimized by the moderator on the site
It worked like a charm sir. Thank you so much.
This comment was minimized by the moderator on the site
Great function, exactly what I needed! Works like a charm
This comment was minimized by the moderator on the site
Hi,

Very helpful VBA solution. Thank you kindly! My question is: Is there a way to change the code or function for multiple criteria? Although the code works for me, I need it to show values corresponding to a timestamp-interval (>= timestamp A, <= timestamp B)


Thank you in advance. :)
This comment was minimized by the moderator on the site
Is there a way to assign this to a button? On large data ranges it takes a while, so ideally I only want it to start the concatenate process once I've finished doing everything else in the sheet. I tried adding a trigger myself but it stopped working completely
This comment was minimized by the moderator on the site
BTW i used the VBA solution
This comment was minimized by the moderator on the site
Extremely helpfull! After editing it for my sheet i have #VALUE! for some of the unique values.
I did a countif to see if it could be that there are too many names to concatenate. The two unique values that have the #VALUE! error have 13635 and 19810 results. Is there a way to overcome this?
This comment was minimized by the moderator on the site
How can I ignore blank cells? mine currently displays this:

";;;;;;;;;"

I'd like for the 1st, 3rd and last 3 semi colons not to there/show. TIA
This comment was minimized by the moderator on the site
Hello, Chantelle
When concatenating the cell values ignoring the blank cells, please apply the below User Defined Function:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
thank you very much! This was so simple and helped a lot!!
This comment was minimized by the moderator on the site
Is it possible to replace the comma splitter with a line break, i.e. char(10)? Many thanks.
This comment was minimized by the moderator on the site
Hello, David,

To combine the cells with line break, the following User Defined Function may help you.

Function ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For I = 1 To CriteriaRange.Count
If CriteriaRange.Cells(I).Value = Condition Then
xResult = xResult & vbCrLf & ConcatenateRange.Cells(I).Value
End If
Next I
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf_LineBreak = xResult
Exit Function
End Function

After pasting this code, then apply this formula: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations