Aggregating Data

Please find attached working file.

I am extracting data which exports in the following format with particular attention on columns C (*Category / Geo / Sector Name) & col E (Security ISIN). Based on the attached extract I want to create a summary table aggregating every line item into their respective bucket (column K). (This is not my question however as this will be a simple vlook up and sumif).

My question is this – how do I group all entries within column C into their respective bucket if there are no identifiers from column E (exception ISIN for bonds). Note the entries will consistently follow the same format and naming convention. For entries that have “—“ within column E all I am working on is the description in column C which is my issue. I would like to bucket these items

1)If we tackle “Cash” initially then the following entries I want to allocate to the “Cash” bucket ( from column C4:C8). Note going forward there will be additional currencies which will need to be added so need to manage this.

2) Bonds – all bonds are given an ISIN so perhaps a rule/formula that IF column E has an ISIN then allocate to the “Bonds” bucket

3) CDS – for these entries there are no identifiers so again I am only working on the assumption these entries with follow the naming convention of “CDSW……………………” – so IF column C begins with these x4 characters then allocate to CDS bucket (make sense?) (the bucket should sum to -2.32)

Similar pattern for the remaining buckets;

FFX – will begin with EUR FORWARD CURRENCY (the currency can change i.e. EUR may change to USD OR GBP or any other currency) but not the name FORWARD CURRENCY CONTRACT after will not

Futures – will consistently begin with FUT MMM YY

IRS - IRSW

Based on the above, the expected results for each bucket are provided in column L. Can you advise what is best to achieve this (i.e. mapping table?)


Note also the following;
 The range within column C may be greater than or less than what is given
 If going forward I need to add additional buckets – allow accordingly
 From source I am unable to generate the identifier which wont change for the foreseeable future