Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
arrays and others..
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!!! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
arrays and others..
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" wrote in message ... 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!!!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
arrays and others..
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 :( Now the question is.. how to write the macro so it looks in all the sheets? :S ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
arrays and others..
for each sh in ThisWorkbook.Worksheets
Next sh -- Regards, Tom Ogilvy "Locachica" wrote in message ... 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 :( Now the question is.. how to write the macro so it looks in all the sheets? :S ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
arrays and others..
thanks :) Will try it tomorrow, let's see how it goes and let you know :P ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA arrays | Excel Discussion (Misc queries) | |||
Avg Arrays | Excel Worksheet Functions | |||
Use of arrays | Excel Worksheet Functions | |||
Use of arrays | Excel Worksheet Functions | |||
Arrays | Excel Programming |