Sorry for the confusion. Ok, at the moment, I've got a bunch of CSV files. They're mostly in the following format:Originally Posted by SamT
Regno Number Field 1 Etc. 200001 -- -- 200002 -- --
I'm looking to merge them into one big table (using the regno number as the constant/primary key which links all the data together):
Regno Number Field 1 Field 2 Field 3 (From Spreadsheet 2) Etc. 200001 -- -- -- -- 200002 -- -- -- --
Then, I want to filter the data. The extract_class file has the list of regno numbers alongside something called class ref. I want to only keep have data for regno numbers which have a class ref of 110:
Regno Number Class Ref Field 2 Field 3 (From Spreadsheet 2) Etc. 200001 110 -- -- -- 200005 110 -- -- --
As you can see, regno number 200002 has been deleted since it doesn't have a class ref of 110.
This doesn't seem that difficult to achieve. But, there are a few problems. Firstly, some of the CSV files have multiple entries under each regno number:
Regno Number Field 1 200001 X 200001 Y 200001 Z 200002 --
So the final table will actually look like this:
Regno Number Class Ref Field 2 Field 3 (From Spreadsheet 2) Etc. 200001
110
X
Y
-- [Merged cell with above]
[Merged cell with above] [Merged cell with above] Z
[Merged cell with above]
[Merged cell with above] [Merged cell with above] P
200005 110 N Q -- [Merged cell with above]
[Merged cell with above] [Merged cell with above] T 200006 110 E R --
As you can see with the 200001 entry, there are some merged cells. Field 3 has three rows of data but all that is under the same cell in the Regno Number column.
Also, another problem is that Excel doesn't load each CSV file fully (since they're too big) so I'm not sure how I can use VBA to do all this. That's why I thought I might be able to use Access to do it and export the result to Excel. What do you think?
PS: I'm not sure if that clears it up. If not, I'm more than happy to create a example using the actual data. Thanks again!
Reply With Quote