CODEWITHSUNDEEP
excelvba
user759740
user759740

Reputation: 127

Excel vb project-best practice

I'm not a vb developer neither so familiar with excel. Anyway i have a project to be done using MS Excel (cannot use access). System is to provide a ratio analysis(ans some other analysis) of companies where data from an annual report need to entered to the system. Then based on several reports data I can derive graphs and all other information.

My question Now I can store data in a single sheet like using is as as a database. it'll be like

CompanyName     Year     Data1     Data2    Data3...

Here the CompanyName can be duplicated as many Years data can be entered. If I use this method Each time I derive company data, I have to search for the relevant rows in the worksheet and keep lots of data in an array as I read through those rows and produce the final result.

Or I can use separate worksheet for each company. Then I only have to search for the relevant sheet name and perform operations in that worksheet it self easily.

So what is the best way to do this? Thanks

Upvotes: 1

Views: 297

Answers (2)

Charles Williams
Charles Williams

Reputation: 23540

It might be simpler to do some of this just using formulas and Excel functions. The basic approach would be to keep the data on one sheet and sort it by year within company so that all the years for a company are grouped together. Then use Filter to create a list of unique companies.
These steps get repeated each time you add new data.
Then create 2 formulas for each company: the first uses MATCH to find the first row containing the company name and the second uses COUNTIF to find how many rows there are for the company.
Then you can use OFFSET(firstrow,ColumnIndex,NumberOfRows,1) (or similar) to get the required range of data for Charts and ratio analysis etc.

Upvotes: 4

xQbert
xQbert

Reputation: 35333

Whatever way works. IMO you could create a defined range (or many) and issue SQL against it just like it was Access table(s). I'm for keeping all like data on the same worksheet even for different companies; but that's just my 2 cents. You can create a pivot to separate out the information and slice/dice it however needed

Since someone liked the comment as an answer:...

Upvotes: 5

Related Questions