Skip to main content
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
  8.2K 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. 
2 years 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.