Identify in a column when the same number appears more than 10 times in a row

[bing77025]

Hello,

I am not familiar with Excel, so I would really appreciate if you could help me with this.
I have done an online survey and some people have given the same answer to many questions. I would like to be able to quickly identify these people to determine whether I should drop them from the database.

More concretely, I would like to write a formula that will allow me to know when the same number appears more than 10 times in a row. The number can be -77, 0, 1, 2, 3, 4, 5, 6, 7. If one of this number repeats more than ten times in a row, I would like to have a number 1 in the cell of the formula and if none of the numbers repeats more than then times in a row, I would like to have 0 in the cell.

Could you please help me with this?

Best.

[martindwilson]

how's the data laid out?

[bing77025]

Thanks for your answer. Originally, each participants is on a row and then each answer is in a different column. However, I have also transposed the dataset so that each participant is on a different column and each answer on a different row (the plan was to measure the frequency of each answer by participants).

[Nels]

I've made a template attached for your case, hope this can be helpful to you.

In the file, Column K to T are the received answer(you can extend this if necessary).
Column A to I are those counting for each answer(As you mentioned -77, 0, 1, 2, 3, 4, 5, 6, 7. I treat these are all choices in your survey)

Column J is the formula to check Column A-I to see if any one answer is repeated at least 10 times.
(which should be the data you need)

appears more than 10 times.xls

[martindwilson]

when you say in a row do you mean consecutive
ie
211111111111234
or just appearing 11 times or more
ie
2,1,3,1,4,1,5,1,6,1,7,1,8,1,9,1,2,1,4,1,6,1
if its the latter
=--(MAX(FREQUENCY(b1:AA1,{-77;0;1;2;3;4;5;6;7}))>10)
or just
=--(MAX(FREQUENCY(B1:AA1,B1:AA1))>10)

[bing77025]

Hello,

Thank you for your help. Yes, I mean more than ten time consecutively, for example:
5 2 1 1 1 1 1 1 1 1 1 1 1 2 3 4 : 1 (yes, more than 10 times)
1 2 1 1 1 1 3 1 1 1 1 1 1 2 1 4 : 0 (no, not more than 10 times)

Do you think that it is possible?