Spreadsheet: 57890d1258105830-splitting-rows-deleting-rows-based-on-certain-criteria-sample
Sheet: Sheet1

How the data is held at the moment
ID | Name | Industry | Country | Grade 1 | Date 1 | [NAME] | Date 2
 | Allstate Insurance Group | 4 | USA | NR-1 | 2008-10-23 00:00:00 | aa- | 2008-10-23 00:00:00
 | Chubb Group of Insurance Companies | 4 | USA | [NAME] | 2008-12-23 00:00:00 | [NAME] | 2008-12-23 00:00:00
 | Farmers Insurance Group | 4 | USA | NR-2 | 2009-04-17 00:00:00 | NR-2 | 2009-04-17 00:00:00
 | Firemans Fund Insurance Companies | 4 | USA | NR-3 | 2008-12-18 00:00:00 | nr | 2008-12-18 00:00:00
 | Liberty Mutual Insurance Companies | 4 | USA | A | 2009-09-04 00:00:00 | a | 2009-09-04 00:00:00
 | Safeco Insurance Companies | 4 | USA | nr | 2009-09-04 00:00:00 | NR-4 | 2009-09-04 00:00:00
 | [NAME] | 4 | USA | [NAME] | 2005-07-13 00:00:00
 | Sentry Insurance Group | 4 | USA | A+ | 2009-01-30 00:00:00 | aa- | 2009-01-30 00:00:00
 | State Farm Group | 4 | USA | [NAME] | 2009-03-03 00:00:00 | [NAME] | 2009-03-03 00:00:00
 | TIG Insurance Group | 4 | USA | [NAME] | 2009-06-22 00:00:00 | bbb- | 2009-06-22 00:00:00
Step 1: I need to remove any row where Grade 1 AND [NAME] has a NR value (SEE expected output below)
NR Values:
nr | NR-4
NR-1 | [NAME]
NR-2 | A blank cell
NR-3
ID | Name | Industry | Country | Grade 1 | Date 1 | [NAME] | Date 2
 | Allstate Insurance Group | 4 | USA | NR-1 | 2008-10-23 00:00:00 | aa- | 2008-10-23 00:00:00
 | Chubb Group of Insurance Companies | 4 | USA | [NAME] | 2008-12-23 00:00:00 | [NAME] | 2008-12-23 00:00:00
 | Liberty Mutual Insurance Companies | 4 | USA | A | 2009-09-04 00:00:00 | a | 2009-09-04 00:00:00
 | Sentry Insurance Group | 4 | USA | A+ | 2009-01-30 00:00:00 | aa- | 2009-01-30 00:00:00
 | State Farm Group | 4 | USA | [NAME] | 2009-03-03 00:00:00 | [NAME] | 2009-03-03 00:00:00
 | TIG Insurance Group | 4 | USA | NR-2 | 2009-06-22 00:00:00 | bbb- | 2009-06-22 00:00:00
Step 2: I need to split up the remaining Grade 1 and Grade 2 rows and modify the [NAME] field (See expected output below)
ID | Name | Industry | Country | Grade | Date
 | Allstate Insurance Group (Grade 1) | 4 | USA | NR-1 | 2008-10-23 00:00:00
 | Allstate Insurance Group (Grade 2) | 4 | USA | aa- | 2008-10-23 00:00:00
 | Chubb Group of Insurance Companies (Grade 1) | 4 | USA | [NAME] | 2008-12-23 00:00:00
 | Chubb Group of Insurance Companies (Grade 2) | 4 | USA | [NAME] | 2008-12-23 00:00:00
 | Liberty Mutual Insurance Companies (Grade 1) | 4 | USA | A | 2009-09-04 00:00:00
 | Liberty Mutual Insurance Companies (Grade 2) | 4 | USA | a | 2009-09-04 00:00:00
 | Sentry Insurance Group (Grade 1) | 4 | USA | A+ | 2009-01-30 00:00:00
 | Sentry Insurance Group (Grade 2) | 4 | USA | aa- | 2009-01-30 00:00:00
 | State Farm Group (Grade 1) | 4 | USA | [NAME] | 2009-03-03 00:00:00
 | State Farm Group (Grade 2) | 4 | USA | [NAME] | 2009-03-03 00:00:00
 | TIG Insurance Group (Grade 1) | 4 | USA | NR-2 | 2009-06-22 00:00:00
 | TIG Insurance Group (Grade 2) | 4 | USA | bbb- | 2009-06-22 00:00:00
Step 3: I need to remove any NR Value again
ID | Name | Industry | Country | Grade | Date
 | Allstate Insurance Group (Grade 2) | 4 | USA | aa- | 2008-10-23 00:00:00
 | Chubb Group of Insurance Companies (Grade 1) | 4 | USA | [NAME] | 2008-12-23 00:00:00
 | Chubb Group of Insurance Companies (Grade 2) | 4 | USA | [NAME] | 2008-12-23 00:00:00
 | Liberty Mutual Insurance Companies (Grade 1) | 4 | USA | A | 2009-09-04 00:00:00
 | Liberty Mutual Insurance Companies (Grade 2) | 4 | USA | a | 2009-09-04 00:00:00
 | Sentry Insurance Group (Grade 1) | 4 | USA | A+ | 2009-01-30 00:00:00
 | Sentry Insurance Group (Grade 2) | 4 | USA | aa- | 2009-01-30 00:00:00
 | State Farm Group (Grade 1) | 4 | USA | [NAME] | 2009-03-03 00:00:00
 | State Farm Group (Grade 2) | 4 | USA | [NAME] | 2009-03-03 00:00:00
 | TIG Insurance Group (Grade 2) | 4 | USA | bbb- | 2009-06-22 00:00:00
The colours are only to help you visualise what I am trying to do, they are not required!