How can I create different sets of sequential values based on another column in a table?
You could use partition by on the note type, which allows a calculation to be reset by groups.  For example:

create table OldNotes (
       OldNoteRef           varchar (20) not null
      ,OldNoteType          varchar (20)
      -- Other data goes here
)
go

insert OldNotes (OldNoteRef, OldNoteType) values ('CABC123', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('CABC456', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('CABC789', 'C')
insert OldNotes (OldNoteRef, OldNoteType) values ('DXYZ001', 'D')
insert OldNotes (OldNoteRef, OldNoteType) values ('DXYZ034', 'D')
insert OldNotes (OldNoteRef, OldNoteType) values ('XZYZ100', 'D')
go


select OldNoteRef
      ,OldNoteType
      ,row_number() over (
          partition by OldNoteType
          order by OldNoteRef
      ) as NewNoteID
  from OldNotes
go


Would produce the following results, which will give you a sequence within the note type.

╔════════════╦═════════════╦═══════════╗
║ OldNoteRef ║ OldNoteType ║ NewNoteID ║
╠════════════╬═════════════╬═══════════╣
║ CABC123    ║ C           ║         1 ║
║ CABC456    ║ C           ║         2 ║
║ CABC789    ║ C           ║         3 ║
║ DXYZ001    ║ D           ║         1 ║
║ DXYZ034    ║ D           ║         2 ║
║ XZYZ100    ║ D           ║         3 ║
╚════════════╩═════════════╩═══════════╝


BTW, table produced by http://www.sensefulsolutions.com/2010/10/format-text-as-table.html