Group Pivot Table based on Member Number

[dawn922]

Hello - My Excel 2007 pivot table is mostly established, but I'm having problems with grouping of member types based on a member number. It is the last character in a member number which allows me to know if a person is a member, or a spouse or child of that member.

I need to group spouse as Spouse, and everyone else as NonSpouse for demographic purposes.

My pivot table rows include MemNum, age, gender, and membership type.

Under MemNum, I have successfully grouped everyone with a member number ending in 0 as NonSpouse, and all member numbers ending in 1 as Spouse. But I then need to group numbers ending in 2, 3, 4, etc. as NonSpouse too. That's where I'm stuck in this inherited project, as I cannot seem to reuse NonSpouse for the other groups.

Thanks in advance.

 

[dawn922]

Nothing like figuring it out shortly after posting...seems like I can select multiple rows in my report filter and include all but numbers ending in 1 in my NonSpouse group. Maybe this will help someone else.

 

[Dan Rittgers]

That is a good solution.

For fleibility, I insert a column in the "Data" and use Vlookup in that column


In the "data, as your your example, you are concerned with Member, Spouse and Other

Range Name XXX
Column A Column B Original Data
0 Member Member
1 Spouse Spouse
2 Other (Otherwise 2=Son)
3 Other (Otherwise 3 = Daughter)
4 Other (Parent)

 

[dawn922]

Thank you for your comments. I will give it a go.