Project with bar chart - where do i start?

[emmatm]

Please can I have some advice on how to start my project??

So I will take a report from a system which will have a whole range of business lines (this is included on the attached report), I manage only a selection of these business lines so I want to dump this report into an excel worksheet tab. If I have a key form my business lines that I manage I would like to automatically extract the data from the report and create a table with only my business lines that I can then use to create bar & pie charts.

Once I have the table of data for my business lines, I want to have the option of pulling only 1 business line if this is independent to create the charts or as a group. I suppose I should build the grouping into the key

How do you suggest this is created?

The information I want created in a table will be from the following columns:

A | B | C | I | H | D | K

Only for my business lines.

Thanks

 

[Luke M]

With your setup, looks like the easiest way to extract the data would be via AdvancedFilter. You could do this manually each time from the Data - Filter - AdvancedFilter button, or you can use a macro. In the attached, I setup the macro and workbook so you can just hit the button and it will automatically extract all your data. Once you have it extracted, you'll need to decide what charts you want to create. I'd suggest a PivotTable, as that would let you build a PivotChart very easily and let you have the option of choosing which business line to look at.

 

[emmatm]

Thank you Luke that is great, can you tell me how I can update the macro to include column H the applicable key from the 'key' tab in columns D - F (some have more than one type for example row 18-19 have key 2 & 1)?

 

[emmatm]

After the macro is updated (any advice on how to do this please as above??)

I want to create a bar chart from the data that is in the result tab.
I want my bar chart per business line with the following bars by building & floor:
1. Anchor desks allocated (this is column D on the results tab)
2. Anchor desks occupied (this is column E on the results tab)
3. Agile desks allocated (this is the count of column F if alpha not numeric on the results tab)
4. Agile desks occupied (this is column G on the results tab)
5. Single occupancy office (this will be the data from column D on the offices tab)
6. Double occupancy office (this will be the data from column E on the offices tab)

Is it possible to create a tab per business line (perhaps with a macro) where the graphs per business line are on each tab?

So for example business line DS&I (see tab 'Key') I would want a tab called 'DS&I' on this tab I would want a bar chart to show the desk formations as the points above for rows 18 & 19 (as these are group 1), I would also want another bar chart on another tab 'DS&I Group' to show the desk formations for rows 18 - 26 (as these are group 2), I would then want rows 20-26 with an individual chart on separate tabs named after the business unit.

This seems very complex to me so I am not sure how to get this information.

Thanks

 

[Luke M]

Thank you Luke that is great, can you tell me how I can update the macro to include column H the applicable key from the 'key' tab in columns D - F (some have more than one type for example row 18-19 have key 2 & 1)?

I don't understand your file layout I'm afraid. There's no columns in the original data that seem to match up to "Group1" or "Group2", so I'm not sure how to answer your question.

Similarly, there is no column call "Anchor Desk" in your source data, so I'm not sure what you're referring to. Perhaps you could take a crack at mocking up a few of the charts you're wanting, and then we'll be able to see which data you want where?