formula to count the number of cells including the one which repeats itself

[Terence019740]

Good evening.

I would be very grateful if anyone could help with the below.

With regards the file attached.

you will see that column J has numerous numbers in said column.

is there a formula that will compare the first number in column J, against all the others down the column, one by one, until it finds a number that repeats itself with any of the above, it then tallys the amount of numbers in total including that which has repeated itself, and display this beside the last number that repeated itself in column K, and then carries on down the column doing the same thing over and over again until it gets to the end of the said column.

Just in case i have not explained myself properly. if you look at column K, in the attached file maybe it is self explanatory what i would like to do.

any help is greatly appreciated.

many thanks all

Terence MARTINEZ

[#2]

@Terence019740 

From your description, it seems that you simply want counts of the repeated numbers in column J, and you want each count to be shown next to the last occurrence of its number.

But your sample's expected results (in column K) do not match that, where I have highlighted the column K cells in the attached workbook.

Examples: Two rows (47 and 49) show counts for zero! Neither of them is the row for the last zero. And row 147 has a count of 10 for the sevens, but there are only two occurrences of seven!

So please try again to describe your intended results.

[#3]

@Terence019740 

Sub end_result()

Dim i, j As Long
Range("K:K").Clear

j = 6
For i = 6 To 194

If Application.WorksheetFunction.CountIf(Range(Cells(j, 10), Cells(i, 10)), Cells(i, 10)) = 2 And Len(Cells(i, 10).Value) > 0 Then
Cells(i, 11).Value = Application.WorksheetFunction.Count(Range(Cells(j, 10), Cells(i, 10)))
j = i + 1
Else
End If

Next i

End Sub

In the attached file you can click the button in cell N2 to run this macro which returns the expected result in the sample file. When i copy your data and paste it in my sheet the cells in range J6:J194 without a number aren't empty. Therefore i've added this condition " And Len(Cells(i, 10).Value) > 0 " in the IF THEN ELSE statement.

[#4]

@SnowMan55 

i will try and explain what it is i would like the formula to do.

1) so i have a column of numbers (Column A) as per below, sometimes there could be a blank space in between them, and or sometimes there could be numerous blank spaces. but what i am interested is in the numbers in Column A.

                    column       COLUMN         

                         A                B                  

Row     1                     

            2            1

            3

            4            3

            5

            6

            7

            8             4

            9

           10            6

           11

           12            3              5

           13

           14             0

           15             0              2

           16             1

           17             3

           18             5 

           19             5               4

           20

           21

           22

           23

           24

with regards Column A, I would like to ask if there is a formula where it would start counting from the very beginning (A1), and go down the column respectively going through each number individually, until the formula detects a number that repeats itself with any of the above numbers it has already analysed, and that if it does find a number which repeats itself, which in this case it should find that both (A4) & (A12) are the same, then it should display in Column B12 the number 5, signifying that it has counted 5 numbers including that which has caused it to repeat itself.

I would then like for the formula to start counting afresh from A13, down the column until it finds another number that repeats itself, which happens in Column A14 & A15, whereby a zero repeats itself. in this case it should show the number 2, in column B15, to signify that it has counted 2 numbers including the number that repeated itself.

the formula should then start counting fresh from column A16 etc..... until it reaches the end of column A.

I hope I have sort of explained myself, and I again apologize for my lack of excel knowledge.

thank you for your continued support.

Terence Martinez

[#5]

@Terence019740 

See the attached workbook - you'll have to allow macros.

After clicking the command button, select the first cell of the range with numbers (for example A1 or E6), then click OK. The column to the right will be populated.

Code:

Sub FillIt()
    Dim rng As Range
    Dim r1 As Long
    Dim r2 As Long
    Dim r As Long
    Dim c As Long
    Dim d As Object
    Dim n As Long
    On Error Resume Next
    Set rng = Application.InputBox(Prompt:="Select the first cell", Default:=ActiveCell.Address, Type:=8)
    If rng Is Nothing Then Exit Sub
    On Error GoTo 0
    Application.ScreenUpdating = False
    r1 = rng.Row
    c = rng.Column
    r2 = Cells(Rows.Count, c).End(xlUp).Row
    Range(Cells(r1, c), Cells(r2, c)).Offset(0, 1).ClearContents
    Set d = CreateObject(Class:="Scripting.Dictionary")
    For r = r1 To r2
        If Cells(r, c).Value <> "" Then
            n = n + 1
            If d.exists(Key:=Cells(r, c).Value) Then
                Cells(r, c).Offset(0, 1).Value = n
                d.RemoveAll
                n = 0
            Else
                d(Cells(r, c).Value) = 1
            End If
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

[#6 · Accepted solution]

@Terence019740 If you are using Excel for MS365 or Excel for the web, the SCAN function could be used as follows:

=LET(
    rng, E6:E200,
    arr, TEXTAFTER(SCAN("1|", SEQUENCE(ROWS(rng)), LAMBDA(p,n, LET(
        r, TEXTBEFORE(p, "|"),
        a, INDEX(rng, r),
        b, INDEX(rng, n),
        IF(AND(COUNTIF(a:b, b)=2, b<>""), n+1&"|"&COUNT(a:b), r&"|")))), "|"),
    IF(arr="", arr, --arr)
)

With older versions of Excel, try the following formula in cell F6 and copy down as needed:

=IF(AND(COUNTIF(INDEX($E$5:E6, IFNA(MATCH(1E+100, $F$5:F5, 1), 0)+1):E6, E6)=2, E6<>""), COUNT(INDEX($E$5:E6, IFNA(MATCH(1E+100, $F$5:F5, 1), 0)+1):E6), "")

See attached...

[#7]

@Hans Vogelaar good evenning, and thank you for your support.

many thanks

Terence Martinez

[#8]

@djclements thank you very much for your help.

it worked.

thank you once again for your support.

terence martinez

[#9]

@asssqwe1330 

[#10]

@Terence019740 You're welcome :)

Kudos to @OliverScheurich for deciphering the original question before additional clarification was given. My formulas used the same basic logic as his VBA method.

Cheers!