I think I will send the file through and that way it will all make sense. How do I attach the Excel sheet?
The output will make sense once you see the sheet
OK column B I have sorted so you will see duplicates apperaing on consecutive rows like 10-11 & 28-29. If we take 10-11 this duplicate instance would be a CAN-CPENFF whereas 28-29 NIA-CAN. In some ways the best method is probably remove all FNN's (column B) that are not duplicates and then reduce each duplicate to a single row and have the additional column F added with the dupicate clear code category or if there were 3 entries column G would be utilised. I guess Conditional Sum or something could be used. What I need in the end is a breakdown of the amount of CAN-CAN CAN-CPENFF etc See how you go I will attempt something but I think what you come up with will be better and less manual than my approach.
It was a two part problem with much work manipulating the data to arrive at the point of where CAN-CAN was possible. In many ways all the work is in getting the data to that point afterwhich it is easy
Not quite as your column F is only reporting if there is an exact match CAN-CAN, NIA-NIA. I was looking at picking up whatever the clearcode categories where for the subsequent faults. So if there was a duplicate it would pull the clearcode category for the second job regardless of what that category was and likewise if it was a triple it would pull the 2nd and 3rd categories regardless of what they are.
I think this should make things clearer
Cool no hurry at all I meet my deadline but some smart formulas for next time will be good
I am really sorry about this and appreciate your help but is this ccidentally the same spreadsheet with the old formula's