Grouping multiple columns into one field for Pivot Table

Hi All,

I have a spreadsheeet in work that I have inherited. On this worksheet we have a list of customers with their various details in the column headers such as name, telephone number, email and so on.

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 states. So for example a customer could work in NY, CA, CO, MT and so on. Now this is usually yes or a no. For yes we simply put an 'X' in the customer row for each state they work in. So if a customer works in New York and California then we would put an 'X' under both of those columns.

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.

Thanks in advance for any help offered.

If you go to the link below and advance the video to 28:20, you will see a similar solution. If you have time to watch the whole video--there are some very cool features shown.

http://www.datapigtechnologies.com/W...tTableTips.htm

Alan

Hi Alan,

Thanks for the link to that video, I will check it out when I get home as due to restrictions at work I am unable to access the video in the link.

Once I have seen the video I will feed back to you.

Thanks again for your help.

Hi Alan,

I just wanted to feed back on the link to the Pivot Table tips tutorial. I watched the whole thing it was fantastic and very educational so many thanks.

Thanks also for being so spot on with the time index to addressing my problem this is exactly what I was looking for and has helped me a lot. I shall try to put it into practice tomorrow.

I guess the only question that remains is: For those states where a particular company doesn't service, do I leave those blank or put an N in there? I know the usual rule of thumb is to not have any holes in the source data when doing a pivot report.

Thanks.

I am not sure what would happen if you left it blank. You can try your pivot without doing anything and if there is an issue, then you can always populate the cells.

Alan

Thanks again for your help Alan, I really appreciate it.

Vikash,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

Vikash, please bump your own thread. Do not bump this thread.

Also, the link you provided is in Mr.Excel not this site. So please create a new thread in this forum.