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.


Excel 2007
ABCD
1Maint #Componenttask typeformula
21212Compressor #1Weekly1
31212Compressor #2Weekly1
41212Compressor #3Weekly1
51212Compressor #4Annual2
61212Compressor #5Weekly1
71212Compressor #6Weekly1
81212Compressor #7Monthly3
91212Compressor #8Weekly1
103412Engine #1Monthly1
113412Engine #3Monthly1
123412Engine #4Monthly1
133412Engine #2Weekly2
143412Engine #5Monthly1
153412Engine #6Monthly1
167812Generator #1Annual1
177812Generator #2Annual1
187812Generator #3Annual1
197812Generator #4Annual1
Sheet3



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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,209
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,432
Messages
5,837,205
Members
430,484
Latest member
himaruasuka

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