Programming to automatically send text to specific sheets and specific columns.

[GlobalExcelller]

I am new to Macros and Visual Basics. There's some datasets which I want to combine into one workbook separated into a few different
sheets for different years, e.g. Sheet 1 = Total sitting Leaving Cert 2013, Sheet 2 = Total sitting Leaving Cert 2012 etc.
I have about 200 datasets in text format. One of these datasets looks like this;

ID = 1
Total sitting Leaving Cert 2013: 114. College places as follows:
UCD 5 , DCU 3 , TCD 3 , UCC 1 , NUIG 1 , NUIM 28 , UL 2 , DIT 16 , Blanchardstown 10 , Carlow IT 1 , Tallaght 10 , Waterford 1 , National College of Art & Design 1 , National College of Ireland 2 , Total of main colleges 84.
Total sitting Leaving Cert 2012: 113. College places as follows:
UCD 3 , DCU 5 , TCD 9 , NUIM 28 , DIT 19 , AIT 1 , Blanchardstown 8 , Dun Laoire 1 , Tallaght 5 , National College of Art & Design 1 , National College of Ireland 3 , St Patricks Institute 1 , Total of main colleges 84.
Total sitting Leaving Cert 2011: 115. College places as follows:
UCD 9 , DCU 3 , TCD 7 , NUIM 27 , DIT 11 , Blanchardstown 8 , Carlow IT 3 , Tallaght 7 , Waterford 1 , National College of Ireland 6 , Total of main colleges 82.
Total sitting Leaving Cert 2010: 111. College places as follows:
UCD 9 , TCD 4 , NUIM 16 , DIT 10 , AIT 2 , Blanchardstown 8 , Carlow IT 1 , Dundalk 1 , Tallaght 17 , Waterford 1 , Total of main colleges 69.
Total sitting Leaving Cert 2009: 117. College places as follows:
UCD 6 , TCD 3 , NUIM 23 , DIT 19 , Blanchardstown 6 , Dun Laoire 2 , Dundalk 1 , Tallaght 20 , Total of main colleges 80.
Total sitting Leaving Cert 2008: 112. College places as follows:
UCD 11 , DCU 5 , TCD 14 , NUIG 2 , NUIM 20 , DIT 20 , Blanchardstown 7 , Tallaght 15 ,
Total sitting Leaving Cert 2007: 111. College places as follows:
UCD 9 , TCD 3 , NUIM 28 , DIT 9 , Blanchardstown 2 , Dun Laoire 4 , Dundalk 1 , LIT 1 , Tallaght 6 , Total of main colleges 63.
Total sitting Leaving Cert 2006: 126. College places as follows:
UCD 3 , TCD 5 , NUIM 23 , DIT 18 , Blanchardstown 5 , Dun Laoire 2 , Dundalk 1 , Tallaght 7 , Waterford 1 , Total of main colleges ?.

In a workbook that I have created I have an ID for the 200 datasets as well as columns which should reference the different
categories in the text above, i.e B1 = "UCD" and J1 = "Car". (Car is short for Carlow IT).

What I want to do is to get each of the numbers beside the categories into their corresponding columns. So first I'll need to
find a way to tell excel that "Car" = "Carlow IT" etc.
The next thing I need to do is put the relevant number into the appropriate column whilst also ensuring that it goes into the
correct sheet.

I am working with Microsoft Excel 2010 and I know how to create basic macros.

I'd appreciate any advice or guidelines on this,

Thanks.

[p24leclerc]

could you attach your excel file you want to work with along with 3 or 4 text files so we can play with them and give you a working solution? If not obvious for an outsider, it would be great if you could put in some of the numbers in the right places as examples
Regards

[GlobalExcelller]

Pierre, I attached a zipped folder with the relevant files.

Thank you.

[p24leclerc]

Hi,
look at the attached workbook and to the tab named Categories.
In contains some instructions for the macro to work properly.
Hope it will fill your need.
P.S. as we are dealing with TXT file, it is very important that they are as constant as possible. Any variation may bug the program or give you false data.