CODEWITHSUNDEEP
arraysexceluniquemax
user1554940
user1554940

Reputation: 27

Unique values in Excel

I need to get a value which has the maximum number of occurrences in the unique data set from a big column based on a condition. I just need one value and not the array.

See below the example data that I am working with. I have done it in MATLAB but want to know it in Excel.

Data Set in Excel

So in the above data for example, I want to get the unique values for lanes based on the value of @safea. So if @safea=102 then unique values of lanes=(2,3,1). But I want the value from these set of data which has maximum number of occurrences. In this case it is 2 because 2 has come up 5 times whereas 3 has come up once and 1 has come up as only 1 time.

Another example:

If I select @safea as 162, then the number of unique values in lanes (5 and 4) but 5 has come up 4 times and 4 has come up as only 1 time so the final answer that I want is '5'.

Upvotes: 1

Views: 490

Answers (2)

andy holaday
andy holaday

Reputation: 2302

You could also use this worksheet function to get a conditional MODE:

=MODE(IF(**your @safea value here**=$A$2:$A$22,$B$2:$B$22))

This is an array formula. Confirm entry by pressing Ctrl+Shift+Enter (not just Enter).

Upvotes: 1

jrad
jrad

Reputation: 3190

If you don't mind using VBA, I've devised a Function you can use for what you want. Given the @safea values are in column A and the lane values are in column B, you can use this:

Function MODEIF(criteria As Integer) As Integer
    Dim count As Integer
    count = Application.WorksheetFunction.CountA(Range("A:A"))
    Dim list() As Integer
    Dim size As Integer
    size = 0
    Do While count > 0
        If (Range("A" & count) = criteria) Then
            ReDim Preserve list(size)
            list(size) = Range("B" & count)
            size = size + 1
        End If
        count = count - 1
    Loop
    MODEIF = Application.WorksheetFunction.Mode(list)
End Function

Just put this Function in a Module, go to the spreadsheet, and type =MODEIF(102) or whatever @safea value you want the mode for and it will give you the answer.

Upvotes: 1

Related Questions