CODEWITHSUNDEEP
excelvbamatch
user2550641
user2550641

Reputation: 347

Excel: check value occurence, compare content with range and set value

I try to combine several function in Excel and for that special problem, I can't find a solution.

I would like to check if a value is contained in a row more than one time . If so, I have to get all values of that matches. I do not want to give out all the matched content! But I would like to compare those values with values from another range and then determin whether matches are all the same type or not

Example:

I have a table like:

Animal     |   Number
___________|___________
Dog        |   2
           |
Cat        |   1
           |
Rabbit     |   2
           |
Cat        |   1
           |
Dog        |   3
           |
Dog        |   2

Now I want create a table which indicates, if the numbers occur only once, or if they occur more times for the same animal or mixed more times:

Number   | one time  |   more times single dog | more times single cat | more times mixed
_________|___________|_________________________|_______________________|____________________
  1      |    O      |             O           |           X           |        O
         |           |                         |                       |
  2      |    O      |             O           |           O           |        X
         |           |                         |                       |
  3      |    X      |             O           |           O           |        O

To check whether the number has more than one occurence I can use:

    =IF(COUNTIF('table1'!B:B;A1)>1;"X";"")

Then I read how to print out all occurences of a value in a range here.

but I can neither transfer that solution to my problem nor figure out any alternative.

Thank you in advance for your help!

EDIT:

So my main problem is how to get the content of ALL occurrences, e.g. for number "2" I would like to have the result {"Dog","Rabbit"} in a form to work on with those values (in order to compare them with another values)

Upvotes: 1

Views: 84

Answers (1)

omegastripes
omegastripes

Reputation: 12602

Regarding of how to get all occurrences, that you mentioned in your edit. Here is an example:

Sub Test()

    Dim objResult As Object
    Dim arrAllNumbers()
    Dim arrAllAnimals()
    Dim lngNumber As Long
    Dim arrAnimals()
    Dim strAnimals As String

    ' processing the table
    Set objResult = ExtractOccurrences(Range("A2:B7"))
    ' example how to get array of the numbers
    arrAllNumbers = objResult.Keys
    ' example how to get array of the dictionaries containing corresponding animals
    arrAllAnimals = objResult.Items
    ' example how to get all animals for certain number
    lngNumber = 2
    arrAnimals = objResult(lngNumber).Keys
    ' convert to the string representation
    strAnimals = "{""" & Join(arrAnimals, """,""") & """}"

End Sub

Function ExtractOccurrences(rngTable As Range) As Object

    Dim arrTable() As Variant
    Dim objList As Object

    arrTable = rngTable
    Set objList = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arrTable, 1)
        If IsEmpty(objList(arrTable(i, 2))) Then Set objList(arrTable(i, 2)) = CreateObject("Scripting.Dictionary")
        objList(arrTable(i, 2))(arrTable(i, 1)) = ""
    Next
    Set ExtractOccurrences = objList

End Function

Sub Test() scope has the variables shown in locals window as follows:

locals

So for number 2 the result is {"Dog","Rabbit"}.

Upvotes: 1

Related Questions