Calculate mode for a variable range set over until loop

Question

I want to calculate mode for a range. Range is a variable based on a condition.

Value 1     Value 2     Output
A           10          10
A           12          10
A           10          10
B           5           3
B           3           3
B           2           3
B           3           3

Like in the above case:

I need to calculate the mode(column C), with the range of value 2(column B), with a condition that Value 1(column A)is same in the range.

Sub mode()

Dim count
Dim rng As Range

x = 2
Do While Range("A" & x).Value = Range("A" & x + 1).Value
     x = x + 1
Loop

Set rng = Range(Cells(x, 2), Cells(x + 1, 2))
md = WorksheetFunction.mode(rng)
Range("C" & x).Value = md

End Sub

Do You have any clue for that?

can you help with the formula ? – Sweta Garg Oct 25 '19 at 09:06 — Sweta Garg, Oct 25 '19 at 09:06

score 3 · Accepted Answer · answered Oct 25 '19 at 09:07

If your data are in A1:B7, then put this in C1 and copy down.

It's an array formula so needs to be confirmed with Ctrl, Shift and Enter, and curly brackets will appear round the formula.

=MODE(IF($A$1:$A$7=A1,$B$1:$B$7))

Of course, you could add the formula using VBA.

Pᴇʜ · Answer 2 · 2019-10-25T09:15:17.350

2

Enter the following formula as array formula (Ctrl+Shift+Enter) in cell C1 and pull it down

=MODE(IF(A:A=A1,B:B))

^{Note: In newer Excel versions you might need to use the MODE.SNGL function instead.}

^{Image 1: Column C uses the array formula with an IF condition.}

For further information see Conditional mode with criteria.

edited Oct 25 '19 at 09:15

answered Oct 25 '19 at 09:09

Pᴇʜ

56,719
10
49
73

Plutian · Answer 3 · 2019-10-25T10:30:47.040

For reference rather than the best answer, below is the VBA I wrote which completes the same task as the array formula from the other answers:

Sub mode2()
Dim lastrow As Long, x As Long, b As Long
Dim cel As Range, cel2 As Range
Dim rng() As Variant
b = 2
lastrow = Range("A" & Rows.count).End(xlUp).Row
For Each cel In Range("A2:A" & lastrow)
    If cel.Value = cel.Offset(1, 0).Value Then
            If (Not Not rng) = 0 Then
                ReDim rng(0 To 0)
                rng(0) = cel.Offset(, 1).Value
            Else
                ReDim Preserve rng(0 To (cel.Row - b))
                rng(cel.Row - b) = cel.Offset(, 1).Value
        End If
    Else
        ReDim Preserve rng(0 To (cel.Row - b))
        rng(cel.Row - b) = cel.Offset(, 1).Value
        If (Not Not rng) <> 0 Then
            Range("C" & cel.Row).Value = Application.WorksheetFunction.mode(rng)
            b = cel.Row + 1
            Erase rng()
        End If
    End If
Next cel
End Sub

This is probably not the cleanest or best macro, but it works and maybe it will help someone when a formula isn't an option. (at least it'll be useful for me if I ever go code bowling)

Shouldn't `lastrow`, `x` and `b` be `Long` otherwise you have a lot of implicit conversions to `Long`? And just for my interest can you explain `Not Not rng`? — Pᴇʜ, Oct 25 '19 at 10:08
@Pᴇʜ You are correct, I'm not that experienced in VBA (yet) and without formal training I still keep mixing up my variable types. (believe me it has given me copious amounts of debugging grief, but I'll get there eventually). As for the Not Not rng, [I found this on another answer](https://stackoverflow.com/a/45882516/11936678) and after some testing, found it works reliably in this case. I'll let them do the explaining as I probably won't do a good job. — Plutian, Oct 25 '19 at 10:26
Thanks for the link, interesting method (never seen it before). — Pᴇʜ, Oct 25 '19 at 11:02

Calculate mode for a variable range set over until loop

3 Answers3