Hi im new to VBA and have just got stuck.
Ive been told to export documents from a database system we use into excel where I want to turn the data into graphs. This is easy enough to do manually, but my boss wants the process automated.
The data im working with is the number of a certain test that a doctor submits to us (the lab) to carry out.

eg

Doctor: DR James (May)

Test Name             Quantity      
Test A                           5                    
Test B                           7                      
Test C                           4
Test D                           3  

Doctor: DR James (June)

Test Name             Quantity      
Test A                           7                    
Test B                           7                      
Test C                           9                    


what I have at the moment is 2 different sets of data (above) on the same sheet. They are both from different time periods. What I want to do is compare the two using a graph.
I need to line them up to do this.

So I want something like this:


Doctor: DR James

Test Name             Quantity May      Quantity June        
Test A                           5                        7
Test B                           7                        7
Test C                           4                        9
Test D                           3  


The problem is that sometimes a certain "test"  may not show up, as that "test" may not have been carried out that month (as has happened in June with test D) Its a shame that it wont show it and put a zero, but this thing wont. It just leaves out that test altogether!

If I was just to record a macro it would just copy the same cells the whole time, and if one doesnt line up the thing will be useless.

What I really want to do is refer to each cell by its contents.
So I could say for example "Test A (may) goes in this column and Test A (june) goes in this column". I know that this would need alot of code (as I have quite a few tests) but i dont mind doing it as it would save time in the long run.

Could someone suggest what I could do to achieve this!
Any help would be great and save me loads of time!!

Hi,
Have you tried a pivot table in Excel?

e.g.
lay out your data in the following format:
Doctor Month Test Name Quantity
Dr James May TEst A 5
Dr James May TEst B 7
Dr James May TEst C 4

Doctor Month Test Name Quantity
Dr James June TEst A 5
Dr James June TEst B 7
Dr James June TEst C 4


Then use the Pivot Table wizard to convert it into the format you listed below.

Sum of Quantity Month
Test Name May June Grand Total
TEst A 5 5 10
TEst B 7 7 14
TEst C 4 4 8
Grand Total 16 16 32


Hope this helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Yea im just this second looking at them!
Ive never used them before, but from what ive just read from you and on the the net. im gonna give them a go!

Would a pivot table be able to line the tests up though?
EG if test A was not carried out at all during one month. Would it then line it up with test B? Which would be wrong

[Edited by Bloobird on 06-10-2005 at 12:00 PM GMT]

[Edited by Bloobird on 06-10-2005 at 12:01 PM GMT]

Hi,
Pivot Tables are really groooovy in this way.. so long as the text matches, it'll go in the correct column.
Send me a sample spreadsheet and i'll knock up some code for you if you wish.
Regards,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Oh thanks, very nice of you to offer!


Shall I mail it to you?

Can you upload it here?
Suggest not the entire dataset, just a small subset in the format that you are using.
Also,
Are you in charge of the code to extract from the database into Excel? We may be able to work on it to get it straight into a nice format in excel from the db.
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

No, I have very little interaction with the data before its given to me in excel form  

Anyway here is an example of the data I will have!!

Thanks again!



____________________________
Attached:
example.zip 2 KB (Downloads: 4)

Hi Bloo,
Just had a quick look at your spreadsheet... er.. that format is rubbish!    
Anyway.. will try and get some code up for you today.
Regards,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Cheers mate that would be great!!
I really appreciate it!
The format is terrible, but thats the database package we use!

Hi Bloo,
Looking at the spreadsheet again...
This format is really rediculous. Can you confirm the following please?
Each month will ALWAYS be in column C
Each doctor segment will ALWAYS be in column A
Each doctor segment will immediatly follow the doctor row

The code is not going to be straight forward, especially parsing the set/sets columns, and I want to make sure that this is fixed cos its going to be a lot of code.

Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Hey

I am going to see the person who is in charge of the database package on monday. To see if there is a way to get more "friendly" data from it.
I could let you know what he says.
I really appreciate your help on this as its got me puzzled!

Cool
At least get them to get the Set/Sets stuff in a decent layout. Preferably in single columns with the total at the bottom, rather than wrapped at the end somewhere.
Let me know how it goes
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Yea my first though was what a rubbish layout!!
And now you have said it too it means I can go to them and tell them that it needs changing!!!
Will be on here monday to let you know.
Thanks again for your help on this!!

Right I have a much better format for the data!
Seems there is a way to stop the truncation!!

Ive attached two different occurances (one for Jan and one for Feb) These are the files that will need to be compared.
We can ignore the ALL (the totals) at the bottom as that is of no interest to us. I just thought i would include it so you can see exactly what the system outputs.

Also the graphs do not need to include totals of any kind. Just a break down of the individual "tests"

[Edited by Bloobird on 10-10-2005 at 11:09 AM GMT]

[Edited by Bloobird on 10-10-2005 at 11:21 AM GMT]

____________________________
Attached:
egs.zip 4 KB (Downloads: 2)

Hi Bloobird,
I am working on this, see attached spreadsheet.
Open it up, select a worksheet with data on it, then go to tools\macros\run. There will only be 1 macro listed.
This macro converts the rather not-nice format of the data you supplied into good source data for a pivot-table and graph.
Have a look and see if this is heading in the right direction.
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

____________________________
Attached:
test_inpivot.zip 12 KB (Downloads: 3)

Yea that looks cool!!
The type of graph i need is one for each doctor.

So on the x axis there could be the test names and on the y axis the number of tests carried out. but on the same graph i need the data from the other period of time. So they can be compared.
So i need away to get the data from the one spreadsheet and put it into the other so a graph can be produced to compare the two.

Ive dug up an old one and attached it, so you can see the end product im expected to produce

Cheers for all the help so far!

____________________________
Attached:
eg.zip 6 KB (Downloads: 1)

Ok,
Another spreadsheet for you.
Open it, and enter the file information that you want to import.
The SERIES should be the month/date of the data and will form the name of the series on the graph

NOTE:
This does not stop you importing the data more than once, so be careful.

Hope this helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

____________________________
Attached:
doctor_rep.zip 22 KB (Downloads: 1)

Hey thats great!
Thanks for your help!! I really appreciate it!!

  

Hi Bloobird,
Can you mark this topic as solved if you are happy with it please?
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)