• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I organize this?

K

ksmic

New Member
We are entering the second of a five-year project that a lot of different people have worked on, but no one has any Excel expertise and it has not been organized efficiently.

Basically, we are surveying colleges and universities to see if they offer degrees or courses in a particular subject matter. We want to know as much about this as possible, so we have different columns for various questions. We need to be able to give our client information about a particular university or column.

However, is there a way to have sub-spreadsheets for universities that offer multiple programs/courses, and who have multiple contacts? I don’t like having so much information squeezed into each cell, but I also don’t want to add another 10 columns that will only apply to a few universities (most of them won’t be as complicated as the one listed, but there are enough that are that I need to figure it out).

I also don’t like having 3 entries for “University X”, but there are some things that are specific to the contact person and some that are specific to the University or course. Any thoughts on how to distinguish/organize those? We need the information on the programs, but we are also trying to build a database of subject matter experts in this field, so it’s equally important to keep track of everyone we contact.

I know there has to be a way to improve this. Any suggestions are greatly appreciated.
 

Attachments

depending on what/how you plan to present this data to your client, having multiple entries in 1 cell (undergrad courses) may make it very hard for you to pull the data for your client. i may be best, although you said you dont like the idea, to have a separate row for each entry. yes, this may cause some columns to be empty, but this will allow you to reorganize the data (like with a pivot table) with a lot of flexibility. this way you could easy see what undergrad particular colleges offer... or you could select a particular course and see which university offer said course.... much more flexible.

saying that... if you simply need to select a particular university and see what they offer, having everything for that university in one row... could...work (it may not be pretty though).

typically, you collect your data and then 'dress it up' before you present the requested information. think about charts... the data is the information that is captured. if you walked in and handed that to a coworker, they may not be able to make any sense from it... but organizing it in a table or with some charts make it very easy for them to pull whatever information may be of interest.
 
Ok, so should we have multiple entries based on contacts (as it is now), or on courses/programs? For some universities, we will have multiple contacts and 1 course; others we will have 1 contact and multiple courses; some we will have both (like the one I attached). Should we make a separate spreadsheet for contacts and the columns specific to them?
 
That was my other question (fear). Are we even in the right program? What does Access let you do that is better for this situation?
 
Hi ,

This is somewhat on the lines of a database , and the first improvement is not to think in terms of what you want , but in terms of what you have. What you have is data. Streamline the organization of the data , and you will automatically streamline the report generation or presentation.

1. There are basically 3 sections of data viz. universities , faculty and courses. Try to segregate information related solely to each section in separate worksheets. What I mean is , have all information which is related solely to universities in a separate sheet ; similarly , all information which is solely related to faculty , let it be in a separate sheet. The same goes for courses.

2. Once this is done , we can start specifying the relationships viz. how do we relate the universities to the faculty , how do we relate the faculty to the courses ,...

3. Last of all we can think of what you wish to present ; thereafter comes the actual step of thinking of how to present it.

Narayan
 
Hi ksmic,

Yes, Access is probably a better long term solution but can provide a very steep learning curve if you are not familiar with it.

Your main issues relate to the way your data is structured, not the program you are using to access it (no pun intended).

Excel can provide very good basic data storage functionality and will allow very good reporting on the back of it (with the help of some VBA).

I would echo Narayan's comment:

1. There are basically 3 sections of data viz. universities , faculty and courses. Try to segregate information related solely to each section in separate worksheets. What I mean is , have all information which is related solely to universities in a separate sheet ; similarly , all information which is solely related to faculty , let it be in a separate sheet. The same goes for courses.

2. Once this is done , we can start specifying the relationships viz. how do we relate the universities to the faculty , how do we relate the faculty to the courses ,...

3. Last of all we can think of what you wish to present ; thereafter comes the actual step of thinking of how to present it.
Click to expand...

Leave the data in Excel and restructure it as suggested (it is much easier to restructure data in Excel than Access) then feed back in to this forum at each stage and we will help you through the process.

Regards,

Peter
 
You must log in or register to reply here.
Back
Top