Note: The other languages of the website are Google-translated. Back to English
Belépek  \/ 
x
or
x
Regisztráció  \/ 
x

or

Hogyan lehet összefűzni a szöveget az Excel kritériumai alapján?

Ha feltételezem, hogy van egy azonosítószámú oszlopom, amely tartalmaz néhány duplikátumot és egy oszlop nevet, most szeretném összefűzni a neveket az egyedi azonosítószámok alapján, a bal oldali képernyőképen, hogy gyorsan összefoghassuk a szöveget kritériumok alapján, hogyan lehetne csinálni az Excel-ben?

A doc egyesíti a szöveget az 1. kritérium alapján

Összevonja a szöveget kritériumok alapján a felhasználó által definiált funkcióval

Összevonja a szöveget kritériumok alapján a Kutools for Excel programmal


A szöveg és az egyedi azonosító számok kombinálásához előbb kivonhatja az egyedi értékeket, majd létrehozhat egy felhasználó által definiált függvényt, hogy az egyedi azonosító alapján egyesítse a neveket.

1. Vegyük például a következő adatokat: előbb ki kell vonni az egyedi azonosító számokat, kérjük, alkalmazza ezt a tömbképletet: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Írja be ezt a képletet egy üres cellába, például a D2-be, majd nyomja meg a gombot Ctrl + Shift + Enter gombok együtt, lásd a képernyőképet:

A doc egyesíti a szöveget az 2. kritérium alapján

típus: A fenti képletben A2: A15 az a lista adattartomány, amelyből egyedi értékeket szeretne kinyerni, D1 az oszlop első cellája, ahová ki szeretné tenni a kivonási eredményt.

2. Ezután húzza lefelé a kitöltő fogantyút az összes egyedi érték kibontásához, amíg üresek nem jelennek meg, lásd a képernyőképet:

A doc egyesíti a szöveget az 3. kritérium alapján

3. Ebben a lépésben létre kell hoznia a Felhasználó által definiált funkció a nevek egyedi azonosító számok alapján történő kombinálásához tartsa lenyomva a ALT + F11 gombokat, és ez megnyitja a Microsoft Visual Basic for Applications ablak.

4. Kattints betétlap > Modulok, és illessze be a következő kódot a Modulok Ablak.

VBA kód: összefűzi a szöveget kritériumok alapján

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. Ezután mentse el és zárja be ezt a kódot, menjen vissza a munkalapra, és írja be ezt a képletet az E2 cellába, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , lásd a képernyőképet:

A doc egyesíti a szöveget az 4. kritérium alapján

6. Ezután húzza le a kitöltő fogantyút azokra a cellákra, amelyeken alkalmazni kívánja ezt a képletet, és az összes megfelelő nevet egyesítették az azonosító számok alapján, lásd a képernyőképet:

A doc egyesíti a szöveget az 5. kritérium alapján

Tipp:

1. A fenti képletben A2: A15 az az eredeti adat, amely alapján össze akarsz kapcsolni, D2 a kivont egyedi érték, és B2: B15 a név oszlop, amelyet össze akarsz kapcsolni.

2. Amint láthatja, vesszővel elválasztott értékeket egyesítettem, bármilyen más karaktert használhat a képlet vesszőjének szükség szerinti megváltoztatásával.


Ha van Kutools for Excel, Annak Haladó kombinált sorok segédprogram segítségével gyorsan és kényelmesen összefűzheti a szövegalapot a kritériumok alapján.

Kutools for Excel : több mint 300 praktikus Excel-bővítménnyel, ingyenesen, korlátozás nélkül, 30 nap alatt kipróbálható.

Telepítése után Kutools for Excel, tegye a következőket:

1. Válassza ki az egyesíteni kívánt adattartományt egy oszlop alapján.

2. Kattints Kutools > Egyesítés és felosztás > Haladó kombinált sorok, lásd a képernyőképet:

3. Az Kombinálja a sorokat az oszlop alapján párbeszédpanelen kattintson az ID oszlopra, majd a gombra Elsődleges kulcs hogy ez az oszlop legyen az a kulcsoszlop, amelyre az összevont adatok alapulnak, lásd a képernyőképet:

A doc egyesíti a szöveget az 7. kritérium alapján

4. Kattintson a gombra név oszlopot, amelybe az értékeket egyesíteni szeretné, majd kattintson Kombájn opciót, és válasszon egy elválasztót az egyesített adatokhoz, lásd a képernyőképet:

A doc egyesíti a szöveget az 8. kritérium alapján

5. Miután elvégezte ezeket a beállításokat, kattintson a gombra OK a párbeszédablakból való kilépéshez, és a B oszlopban lévő adatokat az A kulcsoszlop alapján egyesítettük. Lásd a képernyőképet:

A doc egyesíti a szöveget az 9. kritérium alapján

Ezzel a szolgáltatással a következő probléma a lehető leghamarabb megoldódik:

Hogyan kombinálhat több sort egybe, és összegezheti a duplikátumokat az Excelben?

Töltse le és ingyenes próbaverziót Kutools for Excel Now!


Kutools for Excel: több mint 300 praktikus Excel-bővítménnyel, ingyenesen, korlátozás nélkül, 30 nap alatt kipróbálható. Töltse le és ingyenes próbaverziót most!

A legjobb irodai termelékenységi eszközök

A Kutools for Excel megoldja a legtöbb problémát, és 80% -kal növeli a termelékenységet

  • újrafelhasználás: Gyorsan helyezze be összetett képletek, diagramok és bármi, amit korábban használt; Cellák titkosítása jelszóval; Levelezőlista létrehozása és e-maileket küldeni ...
  • Szuper Formula Bár (könnyedén szerkeszthet több szöveget és képletet); Olvasás elrendezés (könnyen olvasható és szerkeszthető nagyszámú cella); Beillesztés a Szűrt tartományba...
  • Cellák / sorok / oszlopok egyesítése az adatok elvesztése nélkül; Osztott cellák tartalma; Kombinálja a duplikált sorokat / oszlopokat... megakadályozza az ismétlődő cellákat; Hasonlítsa össze a tartományokat...
  • Válassza a Másolat vagy az Egyedi lehetőséget Sorok; Válassza az Üres sorok lehetőséget (az összes cella üres); Super Find és Fuzzy Find sok munkafüzetben; Véletlenszerű kiválasztás ...
  • Pontos másolás Több cella a képletreferencia megváltoztatása nélkül; Automatikus referenciák létrehozása több lapra; Helyezze be a golyókat, Jelölőnégyzetek és még sok más ...
  • Kivonat szöveg, Szöveg hozzáadása, Eltávolítás pozíció szerint, Hely eltávolítása; Hozz létre és nyomtasson személyhívó részösszegeket; Konvertálás a cellatartalom és a megjegyzések között...
  • Szuper szűrő (mentse el és alkalmazza a szűrősémákat más lapokra); Haladó rendezés hónap / hét / nap, gyakoriság és egyebek szerint; Speciális szűrő félkövér, dőlt betűvel ...
  • Kombinálja a munkafüzeteket és a munkalapokat; Táblázatok egyesítése kulcsoszlopok alapján; Az adatok felosztása több lapra; Kötegelt konvertálás xls, xlsx és PDF...
  • Több mint 300 hatékony funkció. Támogatja az Office / Excel 2007-2019 és 365. Támogatja az összes nyelvet. Könnyen telepíthető a vállalkozásba vagy szervezetbe. 30 napos ingyenes próbaverzió. 60 napos pénzvisszafizetési garancia.
kte tab 201905

Az Office fül a füles felületet hozza az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és minden nap több száz kattintással csökkenti az egér kattintását!
officetab alja
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Md. Zaker Hossain · 1 months ago
    @skyyang It worked like a charm sir. Thank you so much.
  • To post as a guest, your comment is unpublished.
    skyyang · 1 months ago
    @Md. Zaker Hossain 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!
  • To post as a guest, your comment is unpublished.
    Md. Zaker Hossain · 1 months ago
    Is there any way to add "and" instead of "," before the last data? (For example: D2355, D2273, D2397, D2600 and D2386)
  • To post as a guest, your comment is unpublished.
    AA · 4 months ago
    Great function, exactly what I needed! Works like a charm
  • To post as a guest, your comment is unpublished.
    AS · 1 years ago
    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. :)
  • To post as a guest, your comment is unpublished.
    Pete · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Merijn · 2 years ago
    BTW i used the VBA solution
  • To post as a guest, your comment is unpublished.
    Merijn · 2 years ago
    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?
  • To post as a guest, your comment is unpublished.
    skyyang · 2 years ago
    @cadrose97 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!
  • To post as a guest, your comment is unpublished.
    cadrose97 · 2 years ago
    How can I ignore blank cells? mine currently displays this:

    ";2503201111@msg.telus.com;;2503202222@msg.telus.com;2508193333@msg.telus.com;2503714444@msg.telus.com;;;;"

    I'd like for the 1st, 3rd and last 3 semi colons not to there/show. TIA
  • To post as a guest, your comment is unpublished.
    victor · 2 years ago
    thank you very much! This was so simple and helped a lot!!
  • To post as a guest, your comment is unpublished.
    skyyang · 2 years ago
    @David 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.
  • To post as a guest, your comment is unpublished.
    David · 2 years ago
    Is it possible to replace the comma splitter with a line break, i.e. char(10)? Many thanks.
  • To post as a guest, your comment is unpublished.
    Ahmed · 2 years ago
    So Easy, thank you :)
  • To post as a guest, your comment is unpublished.
    minhtien1900@gmail.com · 3 years ago
    Hi guys , I got an error #NAME? when I apply formulas CONCATENATEIF in excel file after set VBA code for this, could anyone help me to solve it , thanks som uch
  • To post as a guest, your comment is unpublished.
    al.boulley@gmail.com · 3 years ago
    @krawlis Yes, what you want to do is add the function to a module. Go into the VBA editor, right-click on "VBAProject" in the Project Explorer, mouse over the "Insert" menu item, and in that submenu choose "Module". Any functions you put in there will be useable on any sheet in your workbook.
  • To post as a guest, your comment is unpublished.
    krawlis · 3 years ago
    Is there a way to apply this CONCATENATEIF function in a separate sheet? It works when I put it in the same sheet as input data, but i need both tables in different sheets and it doesn't work.
  • To post as a guest, your comment is unpublished.
    MIchele · 3 years ago
    Is there a way to do this on Mac????
    It's exactly what I need - please let me know (or if any mac software would do it that you know of). Thx
  • To post as a guest, your comment is unpublished.
    Yash · 3 years ago
    @DJDave Wow!! Genius! Worked like a charm! There ARE come spaces that show as a different character. Thanks a lot Dave! Wonder how you came up with the idea! Also, wonder how it works for some other peeps..Anyway, thanks again!
  • To post as a guest, your comment is unpublished.
    DJDave · 3 years ago
    @Yash The code uses some non-breaking spaces for indentation, these trip up Excel2016. Hard to spot an invisible error..
  • To post as a guest, your comment is unpublished.
    DJDave · 3 years ago
    I had a problem after pasting this code into Excel 2016 - it contains non-regular spaces (perhaps non-breaking spaces?) which throw up syntax errors which are not evident no matter how closely you look because they are invisible! It is the indentation spaces that are the problem. Paste the code into Word and turn on hidden characters to see them.
  • To post as a guest, your comment is unpublished.
    Chris · 3 years ago
    @Ram Bahadur Ale Works great just slow. I am doing it with 27k lines of text in excel just set it off go for a brew and leave it to run
  • To post as a guest, your comment is unpublished.
    Yash · 4 years ago
    Hi!

    concactenateif is Exactly what I was looking for. But unfortunately can´t get it to work Always get a compile error:syntax error. Any ideas?

    In the past, with some imported VBA modules, I have noticed that I had to replace the "," by ";" as in my PC, maybe owing to my regional settings, that's the only way it works. Avidly use the built in sumifs etc. But can´t understand where am going wrong on this one.

    One more possibility that comes to mind is the fact that in office 365, "concat" replaces "concactenate". Can you help out please?

    Thanks in advance,

    Yash
  • To post as a guest, your comment is unpublished.
    Ram Bahadur Ale · 4 years ago
    It does not work for the big data range. I found that its working datarange is up to A2:A362. We would be grateful if you share the solution to cover the wider data range like A2:A200000 .....
    Thank you
  • To post as a guest, your comment is unpublished.
    Ram Bahadur Ale · 4 years ago
    It does not work for the big data range. I found it's working range is only up to A2:A362. We would be grateful if you share the solution for the big data range like A2:A200000 ....

    Thank you
  • To post as a guest, your comment is unpublished.
    ConfusedNBusy · 4 years ago
    @Enrique Thanks for posting this is exactly what I am looking for. I seem not to be saving the vba code correctly. I am getting an error message about ambiguous name found.

    Any suggestions or step by step on the VBA step of this project?

    Thanks
  • To post as a guest, your comment is unpublished.
    nickado · 4 years ago
    Great!!! Thank you so much!
  • To post as a guest, your comment is unpublished.
    Matt · 4 years ago
    Awesome, thank you! I used the VBA solution and it worked great.
  • To post as a guest, your comment is unpublished.
    Samrat Govekar · 4 years ago
    Extremely helpful and nicely explained
  • To post as a guest, your comment is unpublished.
    Samrat Govekar · 4 years ago
    Explained in detailed and easy to understand, really helped when i was stuck at exact same situation.
  • To post as a guest, your comment is unpublished.
    latha · 5 years ago
    Taking more time for updating the same concatenateif() formula. i have 5000 rows. and its more than 2 hrs now its still updating :(

    Any resolution to make it work fast?
  • To post as a guest, your comment is unpublished.
    Renee · 5 years ago
    I am looking for a way to use a variation of this code to create a variant list based on master variant. Using your example data, I would need to combine columns A and B into unique identifiers and then concatenate those identifiers to each row based on the value in column A, excluding the value from from the combined for that row, and the rest in alpha sort order:

    Master id name id variant list
    CN20150012 Lucy CN20150012-Lucy CN20150012-Andy CN20150012-Monica CN20150012-Phiby
    US20150011 Tommas US20150011-Tommas US20150011-Rose
    CN20150012 Monica CN20150012-Monica CN20150012-Andy CN20150012-Lucy CN20150012-Phiby
    CN20150012 Phiby CN20150012-Phiby CN20150012-Andy CN20150012-Lucy CN20150012-Monica
    US20150011 Rose US20150011-Rose US20150011-Tommas
    UK20150014 Peter UK20150014-Peter UK20150014-Anith UK20150014-Kristi UK20150014-Libin
    JP20150010 Ramon JP20150010-Ramon JP20150010-Brenda JP20150010-James
    UK20150014 Libin UK20150014-Libin UK20150014-Anith UK20150014-Kristi UK20150014-Peter
    UK20150014 Anith UK20150014-Anith UK20150014-Kristi UK20150014-Libin UK20150014-Peter
    JP20150010 James JP20150010-James JP20150010-Brenda JP20150010-James JP20150010-Matus
    CN20150012 Andy CN20150012-Andy CN20150012-Lucy CN20150012-Monica CN20150012-Phiby
    UK20150014 Matus UK20150014-Matus JP20150010-Brenda JP20150010-James
    UK20150014 Kristi UK20150014-Kristi UK20150014-Anith UK20150014-Libin UK20150014-Peter
    JP20150010 Brenda JP20150010-Brenda JP20150010-James JP20150010-Ramon

    I have a sheet with over 1000 lines, each item comes with up to 4 variants. Trying to do this manually is impossible but I cannot find a solution that fits my needs.
  • To post as a guest, your comment is unpublished.
    Tim Blosser · 5 years ago
    This VBA code saved the day for me. Thank you!
  • To post as a guest, your comment is unpublished.
    Manoj · 5 years ago
    Will this tool be able to handle case sensitive combinations such as

    jABC 123
    abc 345
    ABc 678
    ABC 912
  • To post as a guest, your comment is unpublished.
    Enrique · 5 years ago
    Thanks for this code. It was EXACTLY what I needed. You saved me a lot of effort, thank you so much.
  • To post as a guest, your comment is unpublished.
    Kaladhar · 5 years ago
    This is an excellent solution (VBA code) and it addressed my requirements in minutes. I will refer your site to others and I will visit for everything that I need going forward.