THOUGHTS:
1.) Is there a better way of doing the entire project?
2.) Would it work better if the data were split into 253 separate sheets each with one column and the match done against sheet names?
3.) Would it work better if the data were split into 253 separate text files each with one column, the match done against names and then that text file imported?
4.) Could it all be done using something like (with column count rows)?
STRUCTURE:
The workbook structure consists of:
SHEETS Master - where the output is presented.
SHEETS Country - just holds the list of 253 entries for the drop list in SHEETS master.
SHEETS Data - holds all the data in a table with 254 columns varying in size from 1 cell to 44090 cells
SHEETS OffsetData - this is only being used to output the offset data whilst I am trying to debug the coding.
SHEETS CleanUpData - this is only being used to output the cleaned up data whilst I am trying to debug the coding.
Full workbook size without populating SHEETS Master is 7,752 KB (7.7 MB)
Full workbook size with SHEETS Master populated with 44090 cells in column B is 25,742 KB (25.7 MB)
FURTHER REFERENCE:
http://www.excelforum.com/excel-gene...r-columns.html
ISSUES:
If I use the VBA code as shown Ref: Code A then the output to SHEETS Master takes up to 2 seconds,
however it outputs a lot of 0(zero) values in SHEETS Master Column B which in turn causes unnecessary data in
SHEETS Master Columns D,E,F,G as seen in Ref: Fig 1
If I use the VBA code as shown Ref: Code B then the output to SHEET Master takes up to 6 minutes
however it outputs no 0(zero) values in SHEETS Master Column B which in turn causes virtually no unnecessary data in
SHEETS Master Columns D,E,F,G as seen in Ref: Fig 2
The code difference is given in Ref: Code C
Ref: Code A
Ref: Fig 1
Fig 1.gif
Ref: Code B
Ref: Fig 2
Fig 2.gif
Ref: Code C
RESOLVE:
???
Bookmarks