How many (mode) 10's in this data set, without the use built-in excel functions

[Technology]

What I am trying to achieve is finding the number of repeats of the value 10 out of this row of values situated from left to right, starting at A2 and finishing at EH2 then placing that result in A4.
'Without excel functions' means something that does not resembles '=SMALL($A$2:$A$50,ROW()-1)'.

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

1​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

3

3

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

3​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

4​

5​

5​

5​

5​

5​

5​

5​

5​

5​

6

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

6​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

8

8​

8​

8​

8​

8​

8​

8​

8​

8​

8​

8​

8​

9​

9​

9​

9​

9​

9​

9​

9​

10​

10​

10​

10​

10​

Thank you in advance! Place result in A4.

 

[smozgur]

@Technology - Side note: When you are asking a new question based on another question that you asked and was solved then it might help to mention/refer to that post as well. Just FYI.

Turn rows and columns into one row using VBA

How do you take a set of values (rows and columns) in EXCEL and use VBA to puts all those values into one row?

www.mrexcel.com

 

[Technology]

@Peter_SSs
1. The number of columns will stay the same.
2. 10 will always be the highest. I'm just working with the following data.

@smozgur
The first code worked, but the second code provided an error message stating 'Run-time error '13': Type mismatch.

 

[Peter_SSs]

@Peter_SSs
1. The number of columns will stay the same.
2. 10 will always be the highest. I'm just working with the following data.

Thanks for the clarification. See if this works for you.

Sub Count10s()
  Range("A4").Value = UBound(Filter(Application.Index(Range("A2:EH2").Value, 1, 0), 10)) + 1
End Sub

 

[smozgur]

The first code worked, but the second code provided an error message stating 'Run-time error '13': Type mismatch.

It should work without problems if the Result variable is an array as it is supposed to be. Perhaps you might want to try posting your sample data "before" the macro runs, so we can test it.
You can use XL2BB to send sample mini-sheets easily on the board.

 

[Technology]

@smozgur
Ok, yeah I was wondering if I just continue the tread or start a new one. I'll start doing so. I tried to download XL2BB, but I dont seems to have the option in the add-in's.

@Peter_SSs
Yeah, that works!

With this data starting at A1, how would we go about finding which column has the most occurrences of 0's.

7​	3​	8​	6​	3​	6​
1​	6​	9​	4​	2​	5​
5​	10​	10​	4​	6​	2​
1​	7​	6​	7​	0​	6​
1​	5​	6​	3​	9​	7​
1​	7​	8​	8​	1​	6​
2​	8​	0​	6​	0​	2​
7​	5​	10​	1​	3​	0​
8​	5​	9​	9​	4​	0​
6​	4​	3​	2​	4​	2​
8​	4​	7​	6​	6​	9​
6​	4​	1​	4​	3​	1​
2​	3​	0​	8​	8​	4​
0​	3​	5​	4​	7​	6​
7​	1​	0​	0​	8​	3​
4​	1​	8​	3​	9​	6​
4​	1​	1​	10​	3​	5​
7​	3​	0​	4​	1​	4​
3​	6​	6​	9​	6​	1​
8​	2​	3​	6​	6​	8​
0​	0​	1​	10​	1​	2​
5​	2​	9​	4​	3​	7​
5​	7​	3​	8​	1​	6​

 

[smozgur]

I was wondering if I just continue the tread or start a new one.

Starting is a new thread is the right thing to do if you are asking a new question.

With this data starting at A1, how would we go about finding which column has the most occurrences of 0's.

Now, I think this is a new question. Take this way, the data set is different, the question is different. So you need to ask it as a new question by starting a new thread.

 

[Technology]

@smozgur
Should I delete it?

 

[smozgur]

Should I delete it?

Not necessary.

Also, just as @Peter_SSs mentioned, you should better explain if you need VBA, or Excel function. It will help the helper to know the intended method.

 

[Peter_SSs]

@Peter_SSs
Yeah, that works!

Good news! Thanks for letting us know.