Search code examples
google-sheetsformulas

Pre-populating a column in a row based on related rows


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.


Solution

  • =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.