Rearrange data to make pivot chart

[aqua267]

I am very new to Excel and have so far done a few basic pivot tables/charts. I am trying to rearrange my source data to make a column chart where credits are shown in green and debits in red.

Original.PNG

Here's my original data I get from a database. Please note there's 3 sets of values A, B, C.
Set1 A, B, C refers to Credits Set2 A, B, C refers to debits Set3 A, B, C is the difference between Set1 A - Set2 A and so on..
I need to make a chart like the one below, Set1 of A,B,Cs are the green columns, Set2 of A, B, Cs are the red columns. Set3 A, B, Cs is hte difference displayed on top.

Chart.PNG

How do I do this? This is what I have done so far:
I hand edited and rearranged my original dataset to look like this: It was quite a bit of work to do this since I have a large amount of data.

Rearranged data.PNG

Then I could make a chart out of this. But what I would like to know is if there's a better way to achieve this. Thanks much!

+------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| Year | Set1 A | Set1 B | Set1 C | Set2 A | Set2 B | Set2 C | Set3 A | Set3 B | Set3 C |
+------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 2010 | 58 | 33 | 111 | 0 | 0 | 300 | 58 | 33 | -189 |
| 2011 | 56 | 33 | 112 | 0 | 0 | 300 | 56 | 33 | -188 |
| 2012 | 56 | 33 | 112 | 0 | 0 | 300 | 56 | 33 | -188 |
| 2013 | 56 | 33 | 112 | 0 | 0 | 300 | 56 | 33 | -188 |
| 2014 | 56 | 30 | 102 | 0 | 0 | 300 | 56 | 30 | -198 |
| 2015 | 134 | 0 | 0 | 190 | 0 | 60 | -56 | 0 | -60 |
+------+--------+--------+--------+--------+--------+--------+--------+--------+--------+

[mikeTRON]

I suggest you post a sample data set and people can probably help.

[aqua267]

Thanks for your reply. Sample dataset is in the original post towards the end. I believe it has some formatting issues.

[mikeTRON]

ACTUAL dataset, not a picture.

[aqua267]

Test Dataset.xlsx

Attached dataset in excel. Thanks for looking.

[aqua267]

Added Dataset to the post. Thanks for your help.

[mikeTRON]

So I see the data, but what are you trying to accomplish with it specifically?

Typically people post a before and a mocked up after.

[aqua267]

I am trying to make the chart outlined in Chart.png. I can not do it with the dataset in Test Dataset.xls file so I handedited data to insert the labels A, B, C. I was wondering if there's any other way to do it in Excel with a few clicks or a different technique.
Before is the dataset I have. After is the chart I want to make. Question is how to rearrange this data to make this chart.
Any help is appreciated. Thanks!

[mikeTRON]

So you are trying to automate the process of processing the data, which is definitely possibly.

You can start by playing with a tool called PowerQuery or DataExplorer. It is a free addin for Excel that allows you to "Unpivot" your data. I have used it a few times, but I have found it easier to just create some sort of VBA macro to automate the process. This requires a little bit more ramp up time, but it infinitely more adjustable.

[aqua267]

Thanks much I will try it out.