Mapping strings in cell to value, and then keeping certain rows based on certain criterias

[webcam723]

I have this excel file. The tenors in row C goes from O/N (overnight), 1 month, 3 months, 6 months and 1 year. I wish to give a value to the tenors such that it goes in ascending order based on its duration, but remain the abbreviations in the Column C.

After doing that, I want to delete rows by keeping only the highest Tenor, while remaining the relevant Entity, Bank Name and Pillar (AB_CD or Placement).

With reference to the screenshot I've attached above, after running the vba code, my first 3 rows should look like this:
A2: China, B2: Bank of America, C2: 1Y, D2: AB_CD, E2: SGD, F2: 118000000, G2: 0, H2: 118000000
A3: China, B3: Bank of America, C3: 1Y, D3: Placement, E3: SGD, F3: 118000000, G3: 0, H3: 118000000
A4: London, B4: Bank of China, C4: 1Y, D4: AB_CD, E4: SGD, F4: 118000000, G4: 0, H4: 118000000

I don't really know how to start the code, I would really appreciate some help on this!

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Mapping strings in cell to value, and then keeping certain rows based on certain criterias
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.