0

I need to create a pivot table in excel to show which xml pages contain which variables.

The pages look something like:

|Datagroup|Variable|Value|XmlPageName|
|ABC      |    VarX|   22|     pageA1|
|ABC      |    VarY|   33|     pageA1|
|ABC      |    VarY|   44|     pageA2|
|ABC      |    VarX|   80|     pageA3|
|ABC      |    VarY|   12|     pageA3|
|ABC      |    VarY|   80|     pageA4|

First, I want to filter the data on Varx = 22 . Furthermore, I want apply another filter on Vary =33 to show me all XmlPageName entries that contain simultaneously (VarX= 22 and VarY=33).
In this case, it will be pageA1.
I am not able to perform this, as I can't apply 2 filters simultaneously.
I couldn't do this with regular filtering and nor with pivot tables.

I want to mention that the excel file is huge, so "eyeball" filtering takes too much time.

Any ideas on how to achieve this?

Many thanks,

Dola

dola
  • 201
  • 4
  • 9

1 Answers1

0

What you are trying to do seems pretty straightforward:

You can use a data filter: DataFilter1

DataFilter2

DataFilter3

Although I'm not sure what you mean by "eyeball" filtering. Good Luck.

Stepan1010
  • 3,136
  • 1
  • 16
  • 21
  • Thank you Stepan1010, but this is not what is intended. I already tried this one. The issue is that both variables place their values under "Value" item. So if for example VarX has 300 values, and VarY has 500 values, How would you know that "80" came from VarX or from VarY? – dola Oct 29 '12 at 17:05
  • Your 2nd printscreen is the key. It contains the Union of the VarX and VarY sets. I need to select first a VarX=80, and then I want my fields to be limited to see only VarY values, so I can figure out which is the correct XmlPageName. If I filter on just "80", I will see the entries with pageA3 and PageA4. But actually I wanted to see only: 1) the pages that contain VarX = 80 -> pageA3 , and not pageA4! 2) what values do VarY has on those pages.In this case, is only pageA3 which has VarY=12 – dola Oct 30 '12 at 07:30