Indicate Duplicate based on a Column

Miroy72

New Member
Joined
Aug 7, 2014
Messages
47
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!!
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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","")
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top