Finding an Error

Gene Smolko

New Member
Joined
Apr 11, 2015
Messages
36
Hello Everyone,


I have a sheet with a lot of data and I need to check it for a certain error. Basically I have a database that lists maintenance procedures with a line entry for each component that maintenance procedure is performed on. There are many columns in this database but there is one that I need to check for errors called task type. The task type for a maintenance procedure should be the same every time the maintenance procedure is listed. If it's different, that's an error that needs corrected. I was thinking that a formula that could number task types for a maintenance procedure would work. For example, for a given maintenance procedure all task types should be the same, a formula that numbers them should all be "1," any twos, threes etc would be an error. If anyone has a different idea for a solution that would work, that's fine too. Below is a image that illustrates what I'm talking about.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Maint  #</td><td style=";">Component</td><td style=";">task type</td><td style=";">formula</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1212</td><td style=";">Compressor #1</td><td style=";">Weekly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1212</td><td style=";">Compressor #2</td><td style=";">Weekly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1212</td><td style=";">Compressor #3</td><td style=";">Weekly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1212</td><td style=";">Compressor #4</td><td style=";">Annual</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1212</td><td style=";">Compressor #5</td><td style=";">Weekly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1212</td><td style=";">Compressor #6</td><td style=";">Weekly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">1212</td><td style=";">Compressor #7</td><td style=";">Monthly</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">1212</td><td style=";">Compressor #8</td><td style=";">Weekly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">3412</td><td style=";">Engine #1</td><td style=";">Monthly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">3412</td><td style=";">Engine #3</td><td style=";">Monthly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">3412</td><td style=";">Engine #4</td><td style=";">Monthly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">3412</td><td style=";">Engine #2</td><td style=";">Weekly</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">3412</td><td style=";">Engine #5</td><td style=";">Monthly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">3412</td><td style=";">Engine #6</td><td style=";">Monthly</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">7812</td><td style=";">Generator #1</td><td style=";">Annual</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">7812</td><td style=";">Generator #2</td><td style=";">Annual</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">7812</td><td style=";">Generator #3</td><td style=";">Annual</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">7812</td><td style=";">Generator #4</td><td style=";">Annual</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br />


In this example, Maint # 1212 has two errors, an Annual and a Monthly, Maint # 3412 has one error, Weekly.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
You could use =IF(COUNTIFS(B:B, B2 , C:C, C2)=COUNTIF(B:B, B2)), "all match", "some error") to show which rows have data that is not entirely correct.
 

Gene Smolko

New Member
Joined
Apr 11, 2015
Messages
36
Thanks Mike,

I removed the spaces and gave the formula a try, unfortunately I got an error.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,045
Messages
5,639,751
Members
417,108
Latest member
Thein Than

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
Top