I have the following dataset in a Google Spreadsheet
+-------------------------------------------+------------------+
|Software Name |Operating System | Multiple OS? |
+--------------------------------------------------------------+
|Office |Windows |Yes |
|Office |Mac |Yes |
|VMWare Fusion |Mac |No |
|VMWare Fusion |Mac |No
+---------------+---------------------------+------------------+
I am been trying to find a way to automatically populate the Multiple OS field with YES/NO. This should only occur when there are duplicate software entries with different operating systems.
I initially expected this to be a fairly easy problem to solve and started messing with FILTER()
and countif()
but shortly after have become quite perplexed as to how to approach this.
=ArrayFormula(IF(LEN(A2:A),IF(ISNUMBER(MATCH(A2:A,FILTER(A2:A,B2:B<>VLOOKUP(A2:A,A2:B,2,0)),0)),"Yes","No"),))
The FILTER(A2:A,B2:B<>VLOOKUP(A2:A,A2:B,2,0))
produces an array of values from the A column that have multiple values in the B column. ISNUMBER(MATCH())
will produce TRUE for each value in the A column that exists in that array.