Consolidate Repetitive Data

Hi All

I have a large set of Data that needs to be consolidated. Basically the data contains a large no of repetitive data and I need to consolidate them to analyze it further. Your assistance is much appreciated.

I have attached the spreadsheet with a sample data and the outcome that I would like to obtain. Hope it all makes sense.

Thanks

Tamil