Skip to main content

Conas sraitheanna cóngaracha a chumasc go tapa leis na sonraí céanna in Excel?

Má cheaptar go bhfuil bileog oibre agat leis na sonraí céanna sna sraitheanna cóngaracha, agus anois ba mhaith leat na cealla céanna a chumasc in aon chill amháin, ionas go mbeidh cuma néata agus álainn ar na sonraí. Conas a dhéanann tú sraitheanna cóngaracha a chumasc leis na sonraí céanna go tapa agus go caothúil? Inniu, tabharfaidh mé bealach tapa duit chun an fhadhb seo a réiteach.

cumasc na cealla céanna 1

Cumaisc sraitheanna cóngaracha de na sonraí céanna le cód VBA

Cumaisc sraitheanna cóngaracha de na sonraí céanna le Kutools le haghaidh Excel

Unmerge cealla agus líon le luachanna dúblacha in Excel

Cumaisc sraitheanna cóngaracha de na sonraí céanna le cód VBA

Ar ndóigh, is féidir leat na sonraí céanna a chumasc le Cumaisc & Ionad ordú, ach má tá na céadta cealla is gá a chumasc, tógfaidh an modh seo am. Mar sin is féidir leis an gcód VBA seo a leanas cabhrú leat na sonraí céanna a chumasc go héasca.

1. Coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an macra seo a leanas sa Modúilfhuinneog.

Sub MergeSameCell()
'Updateby Extendoffice
Dim Rng As Range, xCell As Range
Dim xRows As Integer
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
xRows = WorkRng.Rows.Count
For Each Rng In WorkRng.Columns
    For i = 1 To xRows - 1
        For j = i + 1 To xRows
            If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
                Exit For
            End If
        Next
        WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
        i = j - 1
    Next
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

3. Ansin brúigh an F5 eochair chun an cód seo a rith, taispeántar dialóg ar an scáileán chun raon a roghnú le bheith ag obair leis. Féach an pictiúr:

cumasc na cealla céanna 2

4. Ansin cliceáil OK, déanfar na sonraí céanna i gcolún A a chumasc le chéile. Féach an pictiúr:

cumasc na cealla céanna 1

Cumaisc sraitheanna cóngaracha de na sonraí céanna le Kutools le haghaidh Excel

Leis an Cumaisc cealla céanna fóntais de Kutools le haghaidh Excel, is féidir leat na luachanna céanna a chumasc go tapa i gcolúin iolracha le cliceáil amháin.

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á. 
Téigh go dtí Íoslódáil
Triail In Aisce 30 láCeannach
PayPal / MyCommerce

Tar éis a shuiteáil Kutools le haghaidh Excel, is féidir leat a dhéanamh mar a leanas:

1. Roghnaigh na colúin is mian leat na sraitheanna cóngaracha a chumasc leis na sonraí céanna.

2. Cliceáil Kutools > Cumaisc & Scoilt > Cumaisc Cealla Mar an gcéanna, féach ar an scáileán:

3. Agus ansin rinneadh na sonraí céanna sna colúin roghnaithe a chumasc i gcill amháin. Féach an pictiúr:

cumasc na cealla céanna 4

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

Chun níos mó eolas a fháil air seo, tabhair cuairt air seo Cumaisc Cealla Mar an gcéanna gné.

Taispeántas: Déan na cealla céanna a chumasc i gcill amháin nó i dtrácht chun luachanna dúblacha a líonadh:

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!

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

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á!
Comments (44)
No ratings yet. Be the first to rate!
Abhishek
Abhishek
This comment was minimized by the moderator on the site
This helped me a lot. Searched a lot of sites, even Chat GPT too. But this code right here is the one. I had like thousands of data which i wanted to merge according to the data in one single column. This code helped me out. Kudos to you my good Sir!
Reply
0
0
Murat Kaya
Murat Kaya
This comment was minimized by the moderator on the site
thanks alot
Reply
0
0
MURAT
MURAT
This comment was minimized by the moderator on the site
How can I exit the running macro when I want to cancel the cell selection when I run the macro?
Reply
0
0
skyyang
skyyang     MURAT
This comment was minimized by the moderator on the site
Hello, Murat,
The vba code in this article will pop out an error dialog box if you click the Cancel button, to fix this problem, please apply the below code:
Sub MergeSameCell()
'Updateby Extendoffice
On Error Resume Next
Dim Rng As Range, xCell As Range
Dim xRows As Integer
xTitleId = "KutoolsforExcel"
Set workrng = Application.Selection
Set workrng = Application.InputBox("Range", xTitleId, workrng.Address, Type:=8)
If workrng Is Nothing Then
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
xRows = workrng.Rows.Count
For Each Rng In workrng.Columns
    For i = 1 To xRows - 1
        For j = i + 1 To xRows
            If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
                Exit For
            End If
        Next
        workrng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
        i = j - 1
    Next
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Please have a try, hope it can help you!
Reply
Report
0
0
fahimmulla
fahimmulla
This comment was minimized by the moderator on the site
Hi Guys!
First of all thank you for all your support. This has been amazing and worked in past. But for some reason it is not working anymore...

My range at the moment is "$A$2:$A$126551" I am not sure if this was so large before as per user the range was larger in past as well( I am trying to help him out here). Any assistance would be great.

I get the error:
"Run-time error '6':

Overflow"

on "xRows = WorkRng.Rows.Count"

Sub MergeSameCell()
'Updateby Extendoffice
Dim Rng As Range, xCell As Range
Dim xRows As Integer
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
xRows = WorkRng.Rows.Count
For Each Rng In WorkRng.Columns
For i = 1 To xRows - 1
For j = i + 1 To xRows
If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
Exit For
End If
Next
WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
i = j - 1
Next
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Additionally: When I select single date range upto row count 12547 it works but thats only for single date. I am looking to do it for all the dates in the column
Reply
Report
0
0
Fahim
Fahim
This comment was minimized by the moderator on the site
Hi,
this has been amazing and worked in past. But for some reason it is not working anymore...

My range at the moment is "$A$2:$A$126551" I am not sure if this was so large before as per user the range was larger in past as well( I am trying to help him out here). Any assistance would be great.

I get the error:
"Run-time error '6':
Overflow"

on "xRows = WorkRng.Rows.Count"<sup></sup><strike></strike>
Sub MergeSameCell()
'Updateby Extendoffice
Dim Rng As Range, xCell As Range
Dim xRows As Integer
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
xRows = WorkRng.Rows.Count
For Each Rng In WorkRng.Columns
For i = 1 To xRows - 1
For j = i + 1 To xRows
If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
Exit For
End If
Next
WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
i = j - 1
Next
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Reply
0
0
Max
Max
This comment was minimized by the moderator on the site
Thanks a lot for this macro, you saved my day, really!
Reply
0
0
Ricardo
Ricardo
This comment was minimized by the moderator on the site
A formula funciona perfeitamente para valores em colunas, mas se fossem valores para mesclar em linhas? Como seria a formula? Obrigado!!
Reply
0
0
Clinton
Clinton
This comment was minimized by the moderator on the site
Thanks a lot for the help. I have a followup question on this. Suppose i have the following situation:

Apple 2
Apple 2
Orange 2
Orange 2
Banana 1
Pear 1
Kiwi 1

Running the macro will cause all the '1's and the '2's to be grouped together and my total count will be 3 instead of 7. Is there a way I can merge the cells in the second column based on those in the first? Thanks in advance (:
Reply
0
0
Roberto
Roberto     Clinton
This comment was minimized by the moderator on the site
I have the same problem, I want merge the cells in a column based on the value of another column.. Is there a solution?
Reply
0
0
Kimberly S
Kimberly S
This comment was minimized by the moderator on the site
This is amazing. Thank you so much for the code. Is there any addition that would make it so the segments do not merge over a page break when printing?
Reply
0
0
skyyang
skyyang     Kimberly S
This comment was minimized by the moderator on the site
Hello, Kimberly,
I can't get your detailed problem, but, the below VBA code can help you to merge the same cells before and after the page break separately, please try.
If it helps you, please let me know.

Sub MergeSameCell_PageBreak()
Dim Rng As Range, xCell As Range
Dim xRows As Integer
Dim xHPB As HPageBreaks
Dim xChpb As Long
Dim xBol As Boolean
Dim xRg As Range
Set xHPB = ActiveSheet.HPageBreaks
xChpb = xHPB.Count
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
xRows = WorkRng.Rows.Count
For Each Rng In WorkRng.Columns
For I = 1 To xRows - 1
For J = I + 1 To xRows
xBol = False
Set xRg = Rng.Cells(J, 1)
For xC = 1 To xChpb
If xRg.Row = xHPB.Item(xC).Location.Row Then
xBol = True
Exit For
End If
Next
If xBol Then Exit For
If Rng.Cells(I, 1).Value <> Rng.Cells(J, 1).Value Then
Exit For
End If
Next
WorkRng.Parent.Range(Rng.Cells(I, 1), Rng.Cells(J - 1, 1)).Merge
I = J - 1
Next
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Reply
Report
0
0
kaushikbbr
kaushikbbr
This comment was minimized by the moderator on the site
In the above VBA code line number 19 "i=j-1 "
how is it going to affect our logic anyway? I did remove that and could still able to get the same result!
Any specific purpose why it is present?
Reply
Report
0
0
kaushikbbr
kaushikbbr     kaushikbbr
This comment was minimized by the moderator on the site
It is to limit the value i to last row.
Please disregard this post!
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations