Finding an Error

[Gene Smolko]

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
	A	B	C	D
1	Maint #	Component	task type	formula
2	1212	Compressor #1	Weekly	1
3	1212	Compressor #2	Weekly	1
4	1212	Compressor #3	Weekly	1
5	1212	Compressor #4	Annual	2
6	1212	Compressor #5	Weekly	1
7	1212	Compressor #6	Weekly	1
8	1212	Compressor #7	Monthly	3
9	1212	Compressor #8	Weekly	1
10	3412	Engine #1	Monthly	1
11	3412	Engine #3	Monthly	1
12	3412	Engine #4	Monthly	1
13	3412	Engine #2	Weekly	2
14	3412	Engine #5	Monthly	1
15	3412	Engine #6	Monthly	1
16	7812	Generator #1	Annual	1
17	7812	Generator #2	Annual	1
18	7812	Generator #3	Annual	1
19	7812	Generator #4	Annual	1
Sheet3

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

 

[mikerickson]

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]

Thanks Mike,

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