With Field Calculator, you can only do a single pass, and you wouldn't get the exact name you are looking for. Each name would be appended with an ID number. That said, you might get something close with this.
Pre-Logic Script Code:
d ={}
def seq_count(f0,f1,f2):
global d
dictValue = "{}{}".format(f0,f1)
if dictValue not in d.keys():
d[dictValue] = 1
return "{}_{}".format(dictValue,d[dictValue])
else:
d[dictValue] += 1
return "{}_{}".format(dictValue,d[dictValue])
Call it with:
seq_count(!rpsuid!, !facilityNumber!, !waterUtilityNodeIDPK!)
Result:
6705Water_1
6705Water_2
6705Sewer_1
6705Sewer_2
6705Sewer_3
6705Comm_1