Forum Discussion
formula to count the number of cells including the one which repeats itself
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...
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.
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
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...
can i paste excel worksheet which i have done
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
