transposing and pivoting

I'm sure you'll be able to write some code to achieve this but I gotta say there must be an easier way.
These input spreadsheets your getting, is it possible to access the data that's generating them?
My concern with this approach is that if some input files are missing (for whatever reason) then the resulting output files will be incorrect.  Being able to get the data right from the source would remove this.
Understand if you can't and we'll work through it.

The spreadsheets have been filled in manually.

OK, but where is the data coming from?

Forecasts from various banks.  I would not be able to reformat at source, so I think we're stuck with what we have.

no problem.  

Are you just going to have one code in a spreadsheet or will it be like the example you gave above having multiple codes ?

My suggestion is parsing each spreadsheet (via code) into a database that will make your reporting a lot more flexible.  A lot like your intermediate step except that data is put into a table.  It would mean you could process the same file twice by accident and not have duplicate data.  You could also see if there is any data missing.
Your final output reports could be more easily manipulated and the pivoted data

I had a similar situtation in that I had multiple input files constantly being updated but needed a reliable source of data from which to draw accurate reports.  I wrote a simple app that polled a directory that these input files were saved to.  When it finds one it parses the data, inserts into a database and moves the input file to a backup location before shutting down.
I could then run any kind of report from the compiled database.  Refreshing these reports would bring in any new data.  I could also change the final reports and pivot any field against another if required without relying on the input files.
What I'm getting at is write your C# program along the same lines.  Keep it simple and parse the data into a db.
Then use Excel or your favourite BI tool to create the reports you want.  It means you don't have to keep going back to your C# program to change the reports.  Also means your users can create their own reports easily enough.

Thanks for this.  There will be multiple codes in each file.
Could you suggest how to parse these files into a db.  I need the data in cell A1 (the file date).

Believe it or not, getting that date is the easiest part.  
I would approach it using OleDb and do multiple queries on each spreadsheet to:

Each code has 11 rows right?  That won't change will it?

The number of rows is variable.  What we know is that when we move onto a new code we have the actual code in row 3.  Related to this we may be able to use forecast period as some kind of record delimiter.

Not an issue as we can specify a range large enough to encompass all the rows say 10000.  When running the query we just exclude lines (records) with null / empty values.

What would be the schema of this db table?
I would need to see some SQL to get the data into this table.

The db table's schema is exactly as you have your intermediate step.  I"ll get something together to show you soon.

It won't be straightforward to create a db table as I don't have administrator rights.  
I've written the attached code to get to the intermediate stage.
Can you let me know if there are any bugs?
Also, if you can help me get the file from the intermediate to the final stage that would be great.
Code.txt

Perhaps a pivot on the filedate column would be a good start.

I'm sorry as I can't test your code at the moment but does it do what you want?

Looking through your code I can see that you work out what is on each row... looks ok but you're in the best position to tell me if it works or not.  Impossible to tell just looking at the code.

It seems to work so far.  How could I code the pivot?

What I was proposing was not to necessarily create a table in your Oracle database but to set up your own database with a single table at this point, just for the data.  Given that Excel does have a limited number of rows I would suggest even running a small MySQL or MSSQL express db that you can use for now.
So instead of writing out the intermediate table to another spreadsheet, I would insert this into a table using SQL commands with the same format.

Here's an example and walkthrough http://www.ianatkinson.net/computing/mysqlcsharp.htm

and for c# with MS SQL / SQL Server Express

http://www.codeproject.com/Articles/4416/Beginners-guide-to-accessing-SQL-Server-through-C

You can easily set up SQL Server Express on most windows machines easily enough and can download it here.

http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx

Given the following what would I do once I'd pivoted the table.  How could I make SQL produce multiple files like this?  This is a one off exercise.

One input file will have many output files.
There will be date for different dates which will go into different columns in the sample below.
The spreadsheet below does not display the vendor.  The name of the sheet will have this information

This is why I thought of using C# to do the pivoting and subsequent splitting of the files.
Even creating a test database requires approval from the dba.

I understand where you are coming from with this and I'm just trying to save you time. Once you write this you don't want to be editing the code to create another report or pivot another field etc
However, Given your situation where you're unable to install a database etc then you don't have much choice. Look I won't be able to look into the code for at least a day or two so why don't you see your dba about a test database?
I've heard this before about it being a one off exercise...from my experience it never is but if that's three way we have to go we will :-)

Thanks for your efforts so far.

No problem. I'm not one to just answer questions here, I like to be able to share my experience and save you time if I can.

Thanks.