Find distinct values within certain range / column with specific condition

koenie

New Member
Joined
Dec 12, 2012
Messages
9
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.

MaterialEquipmentNext Maintenance DateNext Calibration Date
123nca100011-2-20141-6-2016
123nca100021-3-20141-6-2016
123nca100031-2-20141-6-2016
223adq201101-7-20151-9-2016
223adq201121-7-201527-9-2016
467qwe300011-2-20151-9-2017
467qwe300021-2-20151-9-2017
467qwe300031-2-20151-9-2017
467qwe300041-2-20151-9-2017
467qwe300051-2-20151-9-2017

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

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

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
MaterialEquipmentNext Maintenance DateNext Calibration Date
123nca1000101/02/201401/06/2016
123nca1000201/03/201401/06/2016ALERT
123nca1000301/02/201401/06/2016ALERT
223adq2011001/07/201501/09/2016
223adq2011201/07/201527/09/2016
467qwe3000101/02/201501/09/2017
467qwe3000201/02/201501/09/2017
467qwe3000301/02/201501/09/2017
467qwe3000401/02/201501/09/2017
467qwe3000501/02/201501/09/2017
99dd991201/03/201502/09/2017
99dd991301/04/201503/09/2017ALERT
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

New Member
Joined
Dec 12, 2012
Messages
9
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

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