PDA

View Full Version : Treat Several Columns as one in Pivot Report



Sir Babydum GBE
04-29-2009, 08:36 AM
Hello my old friends. I'm happy to say that reports about my demise have been greatly exagerrated. I am very much alive. I've given up sooth-saying and now live in Frinton with a Seagull

Now to my question:

I want to create a list of activities for each day that contains the following column headers:

Date; Activity; Host; Attendee 1; Attendee 2; Attendee 3; Attendee 4 and so on - perhaps up to 30 attendees.

I don't want to create a seperate line for each attendee because I and my co users are far too lazy for that.

Would there be a way for my to have a list like that but have all the attendee columns treated as one so that, for instance, I might want a pivot to report how many times Betty attended activities in March without having it broken down to Attendee 1; Attendee 2 etc. Or I might want to see if Brian attended art class on May 15th, and so on.

I would normally use a pivot for such reporting but can I with a setup like this?

many thanks

Sir BD

Bob Phillips
04-29-2009, 09:34 AM
I would think you would need to decompose the data to get that.

Why not have a simple VBA job that decomposes it and creates the pivot on the fly. That way you only need to run the VBA when the data gets updated.

Sir Babydum GBE
05-02-2009, 09:04 AM
I would think you would need to decompose the data to get that.

Why not have a simple VBA job that decomposes it and creates the pivot on the fly. That way you only need to run the VBA when the data gets updated.That concisely answers the question and offers a good solution. I shall try that, and no doubt will be back with another question when I get stuck on the VBA! Thanks XLD

Paul_Hossler
05-02-2009, 02:39 PM
I get "2 dimensional" data all the time, usually with some time periods as column headers:

REGION GROUP DEPT JAN FEB MAR .........

I wrote a general purpose macro to build a more pivot table friendly list:

REGION GROUP DEPT MONTH

May not be perfect (and I'll take any feedback) but you're welcome to use anything that helps.


Paul