Grouping multiple columns into one field for Pivot Table

[Vikash200418]

Hi ,

I have a spreadsheeet in work that I have inherited. On this worksheet we have a list of people with their various types of surveys categories in each column and then every category has a completion date in different column headers

Now the tricky part is we have a several column headers for the areas they work in. To simplify what I am trying to explain. Let us say they are names of people. So for example a people have undertaken surveys names AAA, BBB, CCC, DDD and so on in different columns and we have a corresponding column for completion dates.

So if I want to see how many people have undertaken survey AAA with their completion dates. For this I am looking to combine all the survey headings in one report filter and the corresponding dates in another report filter so that there is a drop down box and I can select from it.


Please let me know if someone can help me.

Thanks,
Vikash

This works fine, however if I now want to run a privot report to see how many states one of these customers work in then it becomes tricky because each state has its own column heading and therefore a field.

How can I group a range of column headers and call them say 'States' and then from that run a pivot table report to find out what states my customers work in and have a cout of this also?

I am using Excel 2007.

 

[Laurent C]

If you are using Excel 2007, this is not a PowerPivot question. Hence, this is not the correct forum.

However, as you already hinted, you will need to reshape your data in a more traditional pivotable format; one column for completion date, one column for survey, one column for all states, ...

You can do this with a first pivot table, where you will pull each column in values (using for example the min function) and set the values to display on rows.

This can, of course, be very tedious if you have many columns. A trick shared by Bill - MrExcel - Jelen was to use the old Pivot and Pivot Chart Wizard to do the work for you. Unfortunately, I can no longer find the link to his article.

 

[Vikash200418]

can any one help me