google spreadsheet - Pre-populating a column in a row based on related rows -
i have following dataset in google spreadsheet
+-------------------------------------------+------------------+ |software name |operating system | multiple os? | +--------------------------------------------------------------+ |office |windows |yes | |office |mac |yes | |vmware fusion |mac |no | |vmware fusion |mac |no +---------------+---------------------------+------------------+
i been trying find way automatically populate multiple os field yes/no. should occur when there duplicate software entries different operating systems.
i expected easy problem solve , started messing filter()
, countif()
shortly after have become quite perplexed how 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 array of values column have multiple values in b column. isnumber(match())
produce true each value in column exists in array.
Comments
Post a Comment