Note: The other languages of the website are Google-translated. Back to English
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Wednesday, 19 January 2022
  1 Replies
  3.4K Visits
0
Votes
Undo
Dear all

If I have nth row (Very large number). I want to count the duplicates each 10 cell. For example

For example I have the following set
2
1
1
1
1
1
1
3
2

I need to count the duplicates (taking into account the first duplicate), each 3 cells i.e the output should be
2
1
3


Any suggestions
Thanks in Advance. 
8 months ago
·
#2441
0
Votes
Undo
Hi mtornado,

To remove deplicate values every 10 cells, please do as follows:

  1. In your worksheet, press Alt + F11, then click Insert > Module;
  2. Copy the code below, and paste it to the module box;
  3. Press F5 to run the code, then you will see a Kutools for Excel dialog as shown below:
  4.   dialog.png
  5. Go back to your worksheet, and select the range. Then click the OK buttom on the dialog.


Sub RemoveDuplicatesValue_10()

Dim xSltRg, xCells, xRg As Range
Dim xStartRg, xEndRg As Range
Dim xSInt, xCount, xRntInt, xNumInt, xF As Integer
On Error Resume Next

xSInt = 10
Set xSltRg = Application.InputBox("Select range:", "Kutools for Excel", , , , , , 8)
If xSltRg Is Nothing Then Exit Sub
Set xSltRg = Application.Intersect(ActiveSheet.UsedRange, xSltRg)
Set xSltRg = Application.Union(xSltRg, xSltRg.Item(1))
xCount = xSltRg.Count
xNumInt = Int(xCount / xSInt)
xRntInt = 0
xRntInt = xCount Mod xSInt
For xF = 1 To xNumInt
Set xStartRg = xSltRg.Item(((xF - 1) * xSInt + 1))
Set xEndRg = xSltRg.Item(xF * xSInt)
Set xCells = xSltRg.Worksheet.Range(xStartRg.AddressLocal & ":" & xEndRg.AddressLocal)
For xInt = xCells.Count To 1 Step -1
Set xRg = xCells.Item(xInt)
If WorksheetFunction.CountIf(xCells, xRg.Value) > 1 Then
xRg.Value = ""
End If
Next
Next
If xRntInt > 0 Then
Set xStartRg = xSltRg.Item(xNumInt * xSInt + 1)
Set xEndRg = xSltRg.Item(xCount)
Set xCells = xSltRg.Worksheet.Range(xStartRg.AddressLocal & ":" & xEndRg.AddressLocal)
For xInt = xCells.Count To 1 Step -1
Set xRg = xCells.Item(xInt)
If WorksheetFunction.CountIf(xCells, xRg.Value) > 1 Then
xRg.Value = ""
End If
Next
End If

End Sub


Note that to remove duplicate values every N cells, change the 10 in the code to N.

Amanda
Attachments (1)
  • Page :
  • 1
There are no replies made for this post yet.