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.

 

[Peter_SSs]

I suggest the you explain exactly what it is that you are trying to achieve and what you mean by "without 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. I hope that helps and appreciate your input, I agree that I could have been more clear. 'Without excel functions' means something that does not resembles '=SMALL($A$2:$A$50,ROW()-1)'. Thank you, again.

 

[smozgur]

@Technology - I updated the original post to include the description as you posted. It is necessary to keep page content meaningful.
Please include the description in your future questions beside partially asking the question in the title as advised.

 

[Technology]

Thank you, I'm new to forums and am trying the best I can. I appreciate the input, and the help.

 

[smozgur]

Thank you, I'm new to forums and am trying the best I can. I appreciate the input, and the help.

No problem.

Regarding your question, "without using Excel functions" means that it can be either done manually typing in cell A4, or using a macro (VBA).
If you could explain the question a bit more, perhaps providing more descriptive sample, then I believe someone will come up with a simple VBA solution that will help.

 

[Technology]

Thank you for your understanding.

I'm looking for something like this:

Sub MultipleRowsToOneRow()
Dim R As Long, C As Long, StartRow As Long, LastRow As Long
Dim Arr As Variant, Result As Variant
Arr = Range("A1").CurrentRegion
ReDim Result(1 To UBound(Arr, 1), 1 To UBound(Arr, 1) * UBound(Arr, 2))
For R = 1 To UBound(Arr, 1)
For C = 1 To UBound(Arr, 2)
Result(1, (R - 1) * UBound(Arr, 2) + C) = Arr(R, C)
Next
Next
Range("A1").Resize(UBound(Result, 1), UBound(Result, 2)) = Result
End Sub


Here's an excerpt of what I have on the spreadsheet:
Row 1 is values
Row 2 is values organized left to right lowest to highest.
Row 3 is the median.
Row 4, Column A needs to show how many times the value 10 is repeated using VBA.

 

[smozgur]

Try the following code (place it right above the End Sub as the last line in your code):

Range("A4").Value = Application.WorksheetFunction.CountIf(Range("A2:EH2"), 10)

 

[Peter_SSs]

how many times the value 10 is repeated using VBA.

Using vba is what you did not mention in your original post.

1. Will the number of columns ever vary?
2. Would 10 always be the last number in the list or could there be higher numbers? If higher, can you say what the highest number might be?

 

[smozgur]

To make it compatible with your existing code (dynamic instead of A2:EH2)

Range("A4").Value = Application.WorksheetFunction.CountIf(Range("A2").Resize(, UBound(Result, 2)), 10)