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

Testy.xlsm

photo_2023-05-31 21.07.38.jpeg

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!