arrays and others..

  • Thread starter Thread starter Locachica
  • Start date Start date
L

Locachica

hello!

I have a couple of questions here..

I have 14 files with information bout companies, each file should loo
like this:

.......................company 1............................... compan
2.........etc
...........2002 2001 2000 1999 1998..........2001 2000 1999
ratio1
ratio2
ratio3

So it means that a company can have information about 5 years or abou
4 or 2 or, depends what is available.

Each file has between 1 and 23 (I think) companies, so it can be a fil
with 2, another with 8 etc.

Each file is a group of companies grouped according to certain ratings
A, B, C, D...

So far I have made for each file/rating the average, quartil 25%
quartil 75% and median sorted per years. So the macro looks first i
the line with the year, if it is 2002, then sums up the values an
divides for the nr of values, and so on for each year. For the media
quartils it puts the info in an array to then sort it from small to bi
etc.

NOW the problem gets complicated. I have to make an average, median an
quartils of ALL the information all together, forgetting about th
years, I cannot put all the companies in a sheet cuz it's not bi
enough...

So what I thought is to first put all the information in ONE file, on
file/rating per sheet. Then for each ratio (ratio1, ratio2..) / lin
look for ALL the columns that have information in ALL the sheets, s
first puts in an array the information in the sheet1, then in th
sheet2 etc and in the end sorts this array + calculates the median
quartils.. but I am not sure how to write this. Anybody has any idea?

then, will the array be too long (14 sheets, each sheet bout 1
companies average, each 4 years average, this would be bout 14*15*4,5
.. bout 950-1000 values)? do you think there's another way of doing it
Like making an array of each sheet and then putting the array
together, but then I am not sure how, would this work?
arraysheet1 (i.e = 212, 32, 43, 98, 1212, 328)
arraysheet2 (ie = 98, 74, 56, 122, 45)
...
arraytotal = arraysheet1+arraysheet2.... (so 212, 32, 43, 98, 1212
328, 98, 74, 56, 122, 45.....)

I haven't tried yet, been busy with the other stuff so far, thought o
asking for help and ideas here first.

Thanks a lot!!!
 
You should be able to get it all in one array with no problem.

A single worksheet can hold 65536 rows, so not sure that your statement that
they won't all fit on one sheet is correct, but your know your data and I
don't.

--
Regards,
Tom Ogilvy

Locachica said:
hello!

I have a couple of questions here..

I have 14 files with information bout companies, each file should look
like this:

......................company 1............................... company
2.........etc
..........2002 2001 2000 1999 1998..........2001 2000 1999
ratio1
ratio2
ratio3

So it means that a company can have information about 5 years or about
4 or 2 or, depends what is available.

Each file has between 1 and 23 (I think) companies, so it can be a file
with 2, another with 8 etc.

Each file is a group of companies grouped according to certain ratings,
A, B, C, D...

So far I have made for each file/rating the average, quartil 25%,
quartil 75% and median sorted per years. So the macro looks first in
the line with the year, if it is 2002, then sums up the values and
divides for the nr of values, and so on for each year. For the media +
quartils it puts the info in an array to then sort it from small to big
etc.

NOW the problem gets complicated. I have to make an average, median and
quartils of ALL the information all together, forgetting about the
years, I cannot put all the companies in a sheet cuz it's not big
enough...

So what I thought is to first put all the information in ONE file, one
file/rating per sheet. Then for each ratio (ratio1, ratio2..) / line
look for ALL the columns that have information in ALL the sheets, so
first puts in an array the information in the sheet1, then in the
sheet2 etc and in the end sorts this array + calculates the median +
quartils.. but I am not sure how to write this. Anybody has any idea?

then, will the array be too long (14 sheets, each sheet bout 15
companies average, each 4 years average, this would be bout 14*15*4,5
.. bout 950-1000 values)? do you think there's another way of doing it?
Like making an array of each sheet and then putting the arrays
together, but then I am not sure how, would this work?
arraysheet1 (i.e = 212, 32, 43, 98, 1212, 328)
arraysheet2 (ie = 98, 74, 56, 122, 45)
..
arraytotal = arraysheet1+arraysheet2.... (so 212, 32, 43, 98, 1212,
328, 98, 74, 56, 122, 45.....)

I haven't tried yet, been busy with the other stuff so far, thought of
asking for help and ideas here first.

Thanks a lot!!!!


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
thanks for your reply :)

yes there is enough rows but not columns.. if I wanted to put all the
companies in one sheet it would be 5 (columns) x about 215 companies..
256 :(
Click to expand...

Now the question is.. how to write the macro so it looks in all the
sheets? :S
 
for each sh in ThisWorkbook.Worksheets


Next sh

--
Regards,
Tom Ogilvy

Locachica said:
thanks for your reply :)

yes there is enough rows but not columns.. if I wanted to put all the
companies in one sheet it would be 5 (columns) x about 215 companies..

Now the question is.. how to write the macro so it looks in all the
sheets? :S


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top