Forum Discussion
Solved
formula to count the number of cells including the one which repeats itself
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
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...
11 Replies
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...
