Indicate Duplicate based on a Column

[Miroy72]

Hi Everyone,

So I have a spreadsheet with many columns and rows but I am only concerned with two columns here. One is the Employee ID (Column C) and the other is Device Type (Column P). The device type has seven different options (Aircard, PDA Voice/Data, Telemetry, Cell Phone, Satellite, PDA Data, Pager). I am trying to do create two new columns:

1. New column that will indicate ("Yes") the rows of employee ids that have "PDA Voice/Data" and "Cell phone"

2. New column that will indicate ("Yes") the rows of employee IDS who have more than two device types.


Thanks in advance!!

 

[Chameleon64]

Hi Miroy72

The first formula is fairly straightforward but I haven't been able to find a slick solution to the second; as there are only 7 options it might not matter so much. Anyway here are my solutions (enter in row 2):

1. =IF(AND(COUNTIFS($C:$C,C2,$P:$P,"PDA Voice/Data")>0,COUNTIFS($C:$C,C2,$P:$P,"Cell Phone")>0),"Yes","")

2. =IF(IF(COUNTIFS($C:$C,C2,$P:$P,"Aircard")>0,1,0)+IF(COUNTIFS($C:$C,C2,$P:$P,"PDA Voice/Data")>0,1,0)+IF(COUNTIFS($C:$C,C2,$P:$P,"Telemetry")>0,1,0)+IF(COUNTIFS($C:$C,C2,$P:$P,"Cell Phone")>0,1,0)+IF(COUNTIFS($C:$C,C2,$P:$P,"Satellite")>0,1,0)+IF(COUNTIFS($C:$C,C2,$P:$P,"PDA Data")>0,1,0)+IF(COUNTIFS($C:$C,C2,$P:$P,"Pager")>0,1,0)>2,"Yes","")