Can this be done - changing format of data?

[EliseFreedman]

Hi there

I am using our company software Costar to create a report giving details of the number of employees of each trade with a disease (Hand Arm Vibration Syndrome). I have got the report and now want to create a graph from it. This is where I am having difficulty as the software allows very limited options for changing the data into the layout you require

Currently my data is in the following layout

Trade Stage Number of Employees
Ancillaries 1V 4
Ancillaries 2V 4
Ancillaries 3V 3
Commercial 3V 1
Electrician 1V 4
Electrician 2V 2
Electrician 3V 3
Engineer 1V 21
Engineer 2V 24
Engineer 3V 6
Human Resources 2V 1
Joiner 2V 2
Management 1V 1
Management 2V 1

I have exported the data to excel and manually changed it into the following format

stage 1 stage 2 stage 3
Ancillaries 4 4 3
Commercial 0 0 1
Electrician 4 2 3
Engineer 21 24 6

I have then been able to create the required graph

Is there any way in Excel that i can change the data from Format 1 to Format 2 automatically

Elise

 

[JerryKlmns]

A Pivot table maybe?
(This question sounds like a Microsoft:Office forum68)

 

[xlbo]

Yeh - definitely a pivot table. Would do this in under 30 seconds

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[EliseFreedman]

hmmmm, thanks for that - I had a feeling the answer was going to be Pivot tables.

Can either of you give me an idea on how to do it - Im new to Pivot Tables

 

[HarleyQuinn]

The Pivot table wizard is pretty good.

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[EliseFreedman]

Ive tried using the wizard but not really getting anywhere

 

[HarleyQuinn]

OK, if you're using Excel 2003 try this.

Data-->PivotTable and PivotChart Report...

Select Microsoft Office Excel list or database form the top set of three option buttons and then PivotTable from the bottom two.

Then select all of your data (including column headers) and click next.

Select New Worksheet and click Finish.

Then from the Field List that appears drag Trade into the 'Drop Row Fields Here' section, Stage into the 'Drop Column Fields Here' section and then drag 'Number of Employees' into the 'Drop Data Items Here' section and you're all done

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[JerryKlmns]

EliseFreedman

Just to open your appetiate, you could use excel to query the database and update your perfectly formated and ready to print graph!

Haven't done that yet but I am sure you could search here for QueryTable. I sense that there is poster in this tread that loves to do that!

 

[xlbo]

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question