Type of pivot table but without values in the middle

[gh256]

Hi,

First time posting here, glad there are places on the web to ask questions with excel and macros because I am totally lost at the moment.

I have a spreadsheet that contains raw data (attached). The raw data is on sheet 1 and on sheet 2 is an example of what I would like to achieve.

Basically what I would like to do is have the objectives go along the top (removing duplicated names) and have the risks go down the side (also removing duplicated names).

In the centre is the sort of "Pivot table" where if the risk is in the objective from the raw data then that square is coloured.

Objective	Risk
Objec 1	Risk 1
Objec 1	Risk 2
Objec 2	Risk 3
Objec 3	Risk 4
Objec 4	Risk 1

Would give

	Objec 1	Objec 2	Objec 3	Objec 4
Risk 1	Coloured			Coloured
Risk 2		Coloured
Risk 3		Coloured
Risk 4			Coloured

I hope that makes some sort of sense, I really have no idea where to start with this. I looked at the pivot table function in Excel but cant see it doing what I need.
Another note is the amount of Objectives and Risks can change.

Hope someone can help.

Thanks in advance

Glenn

[MarvinP]

Hi Glenn,

How about putting a third column on sheet 1 with a value. Then in your pivot table do conditional formatting. See the attached on Sheet3.
I really don't see any criteria you have for coloring the cells on your sheet 2. Look at my example. I hope this helps.

[gh256]

Hi Marvin,

Thanks for that. I had previously tried it out however it ends up ordering the objectives SO1, SO10, SO11, S02, S03.
Also you end up with the 1 appearing in the boxes in the middle which looks odd and I would have to use this in some reports.

Glenn

[JosephP]

you can sort the columns by dragging them or creating a custom list. you can also format the data cells with a custom format of
;;;
so that they don't show any data.

[MarvinP]

You can also make the font color of the 1 white on white so the number won't show in the pivot table. The conditional formatting will still work but the 1 won't show.

In your problem there is not a way to determine which cells should get background color without a value or more information in the problem.
Example - Two trains traveling towards each other are traverling at 40 and 60 mph, when do they meet? Not enough info to solve the prob.
Why do you color some of the pivot cells and not others?

[JosephP]

if you color it white the numbers will show when the cells get highlighted ;-)
also I reckon you don't need an additional column as you can count one of the text columns.

[gh256]

I could probably get away with the pivot table as per Joseph's example, however the dragging to sort the item does not work, this step needs to be automated. What is the custom list?

Using the pivot table method, could I create it while recording a macro so it does it on its own?

This is the middle part of 3 stages, an initial program loads the data into excel, excel makes the graph and then triggers a word macro which adds it to a report. The rest is ok at the moment, just the full automation of the table part.

Marvin - the cell is coloured if in the raw data a risk appears in an objective, as the small example in the first post.

Joeseph - Thanks, I can use ;;; which works well, if I remove the additional column it doesn't seem to give me an option in the pivot table to put in "Values".

Thanks,

Glenn

[JosephP]

you can add the risk column to the values area as well as the row or column area.

the custom lists are in the advanced section of the Options near the bottom. any chance you could get the report altered to use 2 digits for all the numbers in the codes? then it would sort automatically

[gh256]

Hi Joseph, thanks for that I am looking for guides on sorting with custom lists now.

I did not reliase that you could use a field more than once in the pivot table design.

Unfortunately the data cannot be altered to make it easier so it does it on its own any way, this is the one major pain that is getting in the way now.

[JosephP]

are the codes always the 'word' before the first space and the same format? if so you could use a formula column (or code) to split out and reformat the code and use that in the pivot table?

[gh256]

It is always SO1 - followed by the name

the number can become 11 or higher SO11 SO23

I doubt it would become higher.

There will always be SO1 - (SO followed by the number) but the part after the hyphen (-) can change so I don't think the custom list for sorting will work?
How do you do the other thing you suggested?

[JosephP]

is it ok to reformat the codes in the pivot table or do they need to stay as they actually are?

[gh256]

The raw data probably has to stay how it is, I might be able to strip out the SO10 bit and leave it off or it might need to be placed above, ideally we need to see the "SO10" - part.
Any changes would need to be made in excel from the raw data.

It is a shame the pivot table sorts at all, that actual raw data is in order but I cant find a way to stop it from sorting the objectives, even if under sort I select manual (drag to sort) it has already placed it in the wrong place.

[JosephP]

is the raw data always in the right order?

[MarvinP]

If the number shows through the background color then make the font color the same as the background and it won't show!

[gh256]

Hi.

Yes the objectives (SO1 etc) in the raw data should always be in the correct order.

Glenn

[JosephP]

ok might be simpler without the pivot then. I have things to do this morning but will try and do something this evening.

@MarvinP
sure you can do that but then you have two font colors to bother with. ain't it easier to hide the data? :-)

[gh256]

Thanks Joseph I really appreciate it

[JosephP]

okay please test this

Please Login or Register  to view this content.

it's pretty quick on your sample data-not sure how much data you really have though.

[gh256]

Wow that is amazing, thank you so much for doing that. I will have to try and study that to see how on earth it is working, I see a lot of googleing ahead of me

If you have a bit of time and it is not to much work, would it be possible to make it look something like this (see attachment) Pivot look.png

For the ticks, the area where boxes are shaded a capital P is placed and the font is "Wingdings 2"

Thanks again for the code above.

[JosephP]

yeah, no problem

Please Login or Register  to view this content.

[gh256]

Thanks very much for that, its a massive help.

I have been trying to get my head round how it works, is there any sites you suggest I should look at for any particular important functions you used?

One thing I have been trying and failing to do is move the pivot table output down 6 lines (so there are 6 blank lines at the top), what in the code do I need to change to do this?

Thanks,
Glenn

[JosephP]

the scripting guys on msdn are quite entertaining and useful for things like the Dictionary object. you can also find some good stuff on the 4guysfromrolla site (only really one guy though and he doesn't do it anymore!)
you'd need to change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

[gh256]

Ah I was close, I changed the first .range bit but missed out the second change further down.

Thanks for that and the advice you have been so helpful

[JosephP]

Happy to help - this one was fun.