Note: The other languages of the website are Google-translated. Back to English
Logáil isteach  \/ 
x
or
x
Cláraigh  \/ 
x

or

Conas téacs a chomhtháthú go héasca bunaithe ar chritéir in Excel?

Má cheapaim go bhfuil colún d’uimhreacha aitheantais agam ina bhfuil roinnt dúbailtí agus colún ainmneacha, agus anois, ba mhaith liom na hainmneacha a thabhairt le chéile bunaithe ar na huimhreacha aitheantais uathúla mar a thaispeántar ar chlé, chun an téacs a chomhcheangal go tapa bunaithe ar chritéir, conas a d’fhéadfaimis a dhéanamh in Excel?

doc téacs a chur le chéile bunaithe ar chritéir 1

Téacs concatenate bunaithe ar chritéir le Feidhm Sainithe ag Úsáideoir

Téacs concatenate bunaithe ar chritéir le Kutools for Excel


Chun téacs a chomhcheangal leis na huimhreacha aitheantais uathúla, is féidir leat na luachanna uathúla a bhaint ar dtús agus ansin Feidhm Sainmhínithe ag Úsáideoir a chruthú chun na hainmneacha atá bunaithe ar an ID uathúil a chur le chéile.

1. Tóg na sonraí seo a leanas mar shampla, ní mór duit na huimhreacha aitheantais uathúla a bhaint ar dtús, cuir an fhoirmle eagar seo i bhfeidhm le do thoil: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Cuir an fhoirmle seo isteach i gcill bhán, D2 mar shampla, ansin brúigh Ctrl + Shift + Iontráil eochracha le chéile, féach an scáileán:

doc téacs a chur le chéile bunaithe ar chritéir 2

Leid: San fhoirmle thuas, A2: A15 an bhfuil an raon sonraí liosta ar mhaith leat luachanna uathúla a bhaint as, D1 Is é an chéad chill den cholún is mian leat an toradh eastósctha a chur amach.

2. Agus ansin tarraing an láimhseáil líonta síos chun na luachanna uathúla uile a bhaint go dtí go dtaispeántar bearnaí, féach an scáileán:

doc téacs a chur le chéile bunaithe ar chritéir 3

3. Sa chéim seo, ba cheart duit a Feidhm Sainithe ag an Úsáideoir chun na hainmneacha a chomhcheangal bunaithe ar na huimhreacha aitheantais uathúla, coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

4. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil Fuinneog.

Cód VBA: téacs comhthráthach bunaithe ar chritéir

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. Ansin sábháil agus dún an cód seo, téigh ar ais chuig do bhileog oibre, agus cuir an fhoirmle seo isteach i gcill E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , féach ar an scáileán:

doc téacs a chur le chéile bunaithe ar chritéir 4

6. Ansin tarraing an láimhseáil líonta síos go dtí na cealla a theastaíonn uait an fhoirmle seo a chur i bhfeidhm, agus cuireadh na hainmneacha comhfhreagracha uile le chéile bunaithe ar na huimhreacha aitheantais, féach an scáileán:

doc téacs a chur le chéile bunaithe ar chritéir 5

Leid:

1. San fhoirmle thuas, A2: A15 an bhfuil na sonraí bunaidh ar mhaith leat a chur le chéile bunaithe ar, D2 an luach uathúil atá bainte agat, agus B2: B15 an colún ainm is mian leat a chur le chéile.

2. Mar a fheiceann tú, chomhcheangail mé na luachanna atá scartha le camóg, is féidir leat carachtair ar bith eile a úsáid trí chamóg “,” na foirmle a athrú de réir mar is gá duit.


Má tá tú Kutools le haghaidh Excel, Lena Sraitheanna Comhcheangail Casta áirgiúlacht, is féidir leat bunús an téacs ar chritéir a chur i gcrích go tapa agus go caothúil.

Kutools le haghaidh Excel : le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá.

Tar éis a shuiteáil Kutools le haghaidh Excel, déan mar a leanas céimeanna:

1. Roghnaigh an raon sonraí a theastaíonn uait a chur le chéile bunaithe ar cholún amháin.

2. Cliceáil Kutools > Cumaisc & Scoilt > Sraitheanna Comhcheangail Casta, féach ar an scáileán:

3. Sa an Comhcheangail Sraitheanna Bunaithe ar an gColún bosca dialóige, cliceáil an colún Aitheantais agus ansin cliceáil Eochair Bhunscoile chun an colún seo a dhéanamh mar an príomhcholún ar a bhfuil do chuid sonraí comhcheangailte bunaithe, féach an scáileán:

doc téacs a chur le chéile bunaithe ar chritéir 7

4. Agus ansin cliceáil Ainm colún ar mhaith leat na luachanna a chomhcheangal, ansin cliceáil Chomhcheangail rogha, agus roghnaigh deighilteoir amháin do na sonraí comhcheangailte, féach an scáileán:

doc téacs a chur le chéile bunaithe ar chritéir 8

5. Tar éis na socruithe seo a chríochnú, cliceáil OK chun an dialóg a fhágáil, agus tá na sonraí i gcolún B curtha le chéile bunaithe ar eochaircholún A. Féach an pictiúr:

doc téacs a chur le chéile bunaithe ar chritéir 9

Leis an ngné seo, réiteofar an fhadhb seo a leanas a luaithe is féidir:

Conas sraitheanna iolracha a chomhcheangal i gceann dúblach agus suim in Excel?

Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!


Kutools le haghaidh Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. Íoslódáil agus triail saor in aisce Anois!

Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

  • Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
  • Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
  • Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
  • Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
  • Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
  • Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
  • Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
  • Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
  • Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.
cluaisín kte 201905

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
bun officetab
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.