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

[webcam723]

I have this excel vba problem. 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 4 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
A5: London, B5: Bank of China, C5: 1Y, D4: Placement, E5: SGD, F5: 118000000, G5: 0, H5: 118000000

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

 

[Joe4]

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

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.