Hi!
I am using pivot tables for work and everything I've used them for so far has been working perfectly and I love the feature! However, I have run into a slight problem that I couldn't google myself out of, so I'm giving this forum a shot
It might be kind of hard to explain in words what I want, even though what I want to do should be rather easy (I hope!). I will enclose a few files that I will refer to while I explain:
raw_data.jpg shows my sheet called "Raw data" which is where I put in... the raw data of course
PT_1.jpg and PT_2.jpg show my sheet called "SIG" in two different situations: One where I have only chosen one row label (only S1), and one where I've chosen all row labels I want to use (S1 through S10).
test_for_excel-help.xlsx is the Excel file itself for you guys to play with
My situation is this. When I have only one row label (the "S1" for example - see PT_1.jpg), everything works perfectly. The statistics work fine and it's very pleasant, nice and tidy to look at etc. However, I need Excel and the pivot table to "treat" all ten rows/fields ("S1"-"S10") as one variable in the pivot chart, or the ten fields in the row label section as one field if you get what I mean. As soon as I put in all ten fields in the "row label" section, it turns into a mess in the pivot table (see PT_2.jpg), and it starts branching out. I need it to stay in one row in the pivot table and not branching out underneath each other.
I wonder if there's a way to somehow "merge" "S1"-"S10" into one variable for the chart. What I want Excel (and pivot) to do, is to count the amount of times "ABC", "DEF", GHI", etc. appears in all ten columns ("S1"-"S10" in the Raw data sheet) combined, and not as separate. It's hard to explain why I need it that way, but all you guys need to know is that I need it that way to keep things organized
In some days there might only be needed to punch in the "words" in the "S1" column, some days only in the "S1" and the "S2" columns, other days all ten columns, etc.
The way it is now at work, I punch in these things manually and I just want to set this up in a pivot table to save me some time If it's not possible, I'll just have to accept that and just continue punching in these things manually.
Also, I want the "(tom)" (ie. Norwegian for "(empty)") to disappear. And the way it is now with ten different fields in the row label, I can only affect the "S1" when I click to remove the empty fields from showing up in the chart. "S2" through "S10" is not affected by my changes.
I don't know if any of this makes any sense. And maybe what I want isn't possible to do with Excel and Pivot. But I was hoping it was possible. Please at least look into this and give me some hints and tips to maybe setting it up differently, so that I can get the result I want. Ie. counting the amount of times a given word, like "ABC" appears within the ten columns "S1 through "S10" (and the dates they are written is also important)
I've been reading a bit about power pivot. Maybe I need that? Btw, I have Excel 2010 and I don't have PowerPivot (not yet at least - I've asked my workplace to get it).
Thank you for your attention!
Bookmarks