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

or

Conas cealla a chónascadh má tá an luach céanna ann i gcolún eile in Excel?

le chéile más rud é

Mar a thaispeántar an pictiúr ar chlé, ní mór duit cealla an dara colún a chomhtháthú má tá luachanna an chéad cholúin mar an gcéanna. Conas is féidir leat é a bhaint amach?

San Airteagal seo, tabharfaimid isteach trí mhodh chun é a bhaint amach.

Cealla concatenate má tá an luach céanna le foirmlí agus scagaire

Cealla concatenate má tá an luach céanna le cód VBA
Cealla concatenate go héasca má tá an luach céanna le Kutools le haghaidh Excel


Cealla concatenate má tá an luach céanna le foirmlí agus scagaire

Leis na foirmlí thíos, is féidir leat ábhar cille comhfhreagrach a thabhairt le chéile má tá an luach céanna in Excel i gcolún eile.

1. Roghnaigh cill bhán seachas an dara colún (anseo roghnaímid cill C2), iontráil an fhoirmle = IF (A2 <> A1, B2, C1 & "," & B2) isteach sa bharra foirmle, agus ansin brúigh an Iontráil eochair.

2. Ansin roghnaigh cill C2, agus tarraing an Láimhseáil Líon isteach go dtí na cealla a theastaíonn uait a chomhchuibhiú.

3. Iontráil foirmle = IF (A2 <> A3, CONCATENATE (A2, "," "", C2, "" ""), "") isteach i gcill D2, agus tarraing Líon Líon Láimhseáil síos go dtí na cealla scíthe.

4. Roghnaigh cill D1, agus cliceáil Dáta > scagairí. Féach an pictiúr:

5. Cliceáil ar an saighead anuas i gcill D1, dícheangail an (Blanks) bosca, agus ansin cliceáil ar an OK cnaipe.

Feiceann tú go bhfuil na cealla comhtháthaithe má tá luachanna an chéad cholúin mar an gcéanna.

nótaí: Chun na foirmlí thuas a úsáid go rathúil, caithfidh na luachanna céanna i gcolún A a bheith leanúnach.


Cealla a chomhcheangal go héasca i gcolún má tá an luach céanna ann i gcolún eile:

Le Kutools le haghaidh Excel's Sraitheanna Comhcheangail Casta fóntais, is féidir leat cealla a chur le chéile go héasca i gcolún má imíonn an luach céanna i gcolúin eile in Excel leis an taispeántas thíos a thaispeántar. Íoslódáil Kutools le haghaidh Excel anois! (Conair saor in aisce 30 lá)

contatinate de chéanna


Cealla concatenate má tá an luach céanna le cód VBA

Chomh maith le foirmle, sa chuid seo, taispeánfaimid duit conas cód VBA a úsáid chun cealla a chomhtháthú más luach céanna iad.

1. Brúigh Eile + F11 eochracha a oscailt Feidhmchláir Bhunúsacha Amharc Microsoft fhuinneog.

2. Sa Feidhmchláir Bhunúsacha Amharc Microsoft fuinneog, cliceáil Ionsáigh > Modúil. Ansin cóipeáil agus greamaigh thíos an cód isteach sa Modúil fhuinneog.

Cód VBA: cealla comhthráthacha má tá na luachanna céanna acu

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

nótaí:

1. D1 ar aon dul Socraigh xRg = Raon ("D1") ciallaíonn sé go gcuirfear an toradh i gcill D1.

2. Níl agus Dath Comhcheangailte ar aon dul xRes (1, 1) = "Níl" agus xRes (1, 2) = "Dath Comhcheangailte" is iad ceanntásca na gcolún comhtháthaithe. Is féidir leat iad a athrú de réir mar is gá duit.

3. Brúigh an F5 eochair chun an cód a rith, ansin gheobhaidh tú na torthaí comhtháthaithe i raon sonraithe.


Cealla a chomhchuibhiú go héasca má tá an luach céanna acu le Kutools for Excel (roinnt cad a tharlaíonn)

Má tá an dá mhodh thuas casta duit, taispeánfaimid anseo an Sraitheanna Comhcheangail Casta fóntais de Kutools le haghaidh Excel Is féidir leat cealla a chónascadh go héasca le camóg, leathchlón agus mar sin de má tá na luachanna céanna ann i gcolún eile.

Roimh iarratas a dhéanamh Kutools le haghaidh Excel, Le do thoil é a íoslódáil agus a shuiteáil ar dtús.

1. Roghnaigh an raon le cealla a theastaíonn uait a thabhairt le chéile, agus ansin cliceáil Kutools > Cumaisc & Scoilt > Sraitheanna Comhcheangail Casta. Féach an pictiúr:

2. Sa Sraitheanna Comhcheangail Casta bosca dialóige, ní mór duit a dhéanamh mar a leanas.

  • 2.1) Roghnaigh an colún leis an luach céanna is mian leat cealla a chomhchuibhiú bunaithe ar, agus ansin cliceáil ar an Eochair Bhunscoile cnaipe.
  • 2.2) Roghnaigh an colún is mian leat chun na cealla a chomhtháthú, cliceáil Chomhcheangail cnaipe, agus ansin deighilteoir a shonrú ón roghchlár comhthéacs (sonraím anseo Comma).
  • 2.3) Cliceáil ar an OK cnaipe.

Anois tá na cealla comhtháthaithe de réir an cholúin eochair phríomha.

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.


Cealla concatenate go héasca má tá an luach céanna le Kutools le haghaidh Excel


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.
    Kristin · 1 months ago
    Hello, how would the VBA code be adjusted if I want to combine cells in column M based on the duplicates in column A?
    • To post as a guest, your comment is unpublished.
      crystal · 26 days ago
      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.
      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
  • To post as a guest, your comment is unpublished.
    EZPD · 1 years ago
    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
  • To post as a guest, your comment is unpublished.
    Jadee · 2 years ago
    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
    • To post as a guest, your comment is unpublished.
      mcaldw · 1 years ago
      "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?
  • To post as a guest, your comment is unpublished.
    HarryVyvey · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    James · 3 years ago
    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.
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi James,
      I tested the code as you mentioned, but it still works well in my case even I set the rows to 1000+.
  • To post as a guest, your comment is unpublished.
    Travelermatt · 3 years ago
    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!
    • To post as a guest, your comment is unpublished.
      Travelermatt · 3 years ago
      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!
  • To post as a guest, your comment is unpublished.
    Ram · 3 years ago
    This is exactly what I needed...thank you
  • To post as a guest, your comment is unpublished.
    RS · 3 years ago
    I am so thankful to you for these formulas. Fantastic work!
  • To post as a guest, your comment is unpublished.
    Marissa W · 4 years ago
    The concatenate feature does not work. Combines all the rows into one cell despite different keys.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Marissa,
      Sorry I am not sure I got your question. There are three methods in the article, which concatenate feature do you mean?
  • To post as a guest, your comment is unpublished.
    Geoffrey saya · 4 years ago
    How can i merge collums with the same names eg Column one reads Kenya throughout and collumn 2 reads western, Nyanza( appearing more than ten times) and column 3 three names of districts. I want to get how mang districts do we have in every province but keep the columns.
    • To post as a guest, your comment is unpublished.
      crystal · 4 years ago
      Dear Geoffrey,

      Please try the Advanced Combine Rows utility we mentioned in above method two. If you want to keep the columns, please make a copy of the original range and do everything in the copied range.

      Specify the province column as Primary Key, set the Column 1 as Keep 1st, and then set the district column as Calculate > Count.

      After that, you will get how many district you have in every province.
  • To post as a guest, your comment is unpublished.
    cathy postmus · 5 years ago
    Can't seem to concatenate with more than 2 cells using formulas and filters