Find distinct values within certain range / column with specific condition

[koenie]

Hello all,
I have not found any clue to solve this single problem today with Google or another source. Therefore, I post a new thread myself.
I would like a VBA script to detect a Material, in which at least one Equipment has another Next Maintenance Date compared to the other Equipments. In other words:
for Material '123nca', is is easily seen for humans that Equipment '10002' has a distinctive Next Maintenance Date compared to Equipment '10001' and '10003'. Therefore, a script needs to detect that Material '123nca' is not consistent since one Equipment has a different Next Maintenance Date. Hopefully, my problem is clear enough for you all! Many thanks in advance.

Material Equipment Next Maintenance Date Next Calibration Date 123nca 10001 1-2-2014 1-6-2016 123nca 10002 1-3-2014 1-6-2016 123nca 10003 1-2-2014 1-6-2016 223adq 20110 1-7-2015 1-9-2016 223adq 20112 1-7-2015 27-9-2016 467qwe 30001 1-2-2015 1-9-2017 467qwe 30002 1-2-2015 1-9-2017 467qwe 30003 1-2-2015 1-9-2017 467qwe 30004 1-2-2015 1-9-2017 467qwe 30005 1-2-2015 1-9-2017 <tbody> </tbody><colgroup><col><col><col><col></colgroup>

<tbody>
</tbody><colgroup><col></colgroup>

 

[oldbrewer]

Material	Equipment	Next Maintenance Date	Next Calibration Date
123nca	10001	01/02/2014	01/06/2016
123nca	10002	01/03/2014	01/06/2016		ALERT
123nca	10003	01/02/2014	01/06/2016		ALERT
223adq	20110	01/07/2015	01/09/2016
223adq	20112	01/07/2015	27/09/2016
467qwe	30001	01/02/2015	01/09/2017
467qwe	30002	01/02/2015	01/09/2017
467qwe	30003	01/02/2015	01/09/2017
467qwe	30004	01/02/2015	01/09/2017
467qwe	30005	01/02/2015	01/09/2017
99dd99	12	01/03/2015	02/09/2017
99dd99	13	01/04/2015	03/09/2017		ALERT
you could sort table by next maintenace ascenging to get rid of duplicate alert
formula in F3
	=IF(A3=A2,IF(C3<>C2,"ALERT",""),"")

<colgroup><col span="2"><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[koenie]

Material	Equipment	Next Maintenance Date	Next Calibration Date
123nca	10001	01/02/2014	01/06/2016
123nca	10002	01/03/2014	01/06/2016		ALERT
123nca	10003	01/02/2014	01/06/2016		ALERT
223adq	20110	01/07/2015	01/09/2016
223adq	20112	01/07/2015	27/09/2016
467qwe	30001	01/02/2015	01/09/2017
467qwe	30002	01/02/2015	01/09/2017
467qwe	30003	01/02/2015	01/09/2017
467qwe	30004	01/02/2015	01/09/2017
467qwe	30005	01/02/2015	01/09/2017
99dd99	12	01/03/2015	02/09/2017
99dd99	13	01/04/2015	03/09/2017		ALERT
you could sort table by next maintenace ascenging to get rid of duplicate alert
formula in F3
	=IF(A3=A2,IF(C3<>C2,"ALERT",""),"")

<tbody>
</tbody>

thanks oldbrewer! This works fine. I have inserted the function into VBA code as follows and this works:
Below a part of the code......for future usage!
Do Until firstRow > lastrow

If Cells(firstRow, 48).Value = Cells(secondRow, 48).Value Then

If Cells(firstRow, 22).Value <> Cells(secondRow, 22).Value Then

Cells(secondRow, 51).Value = "Yes"

Else: Cells(secondRow, 51).Value = "No"

End If

Else: Cells(secondRow, 51).Value = "No"

End If

secondRow = secondRow + 1
firstRow = secondRow - 1

Loop