Grouping and Ungrouping within pivot: non-unique names

[staregirl]

Hi all

I need advice on the best way to deal with a recurring problem. I'm sure I'm not thinking about it properly!

I have to produce a list of our most profitable clients each month.

The data is extracted from our back office software. Unlike our previous back office software, the new kit does not have a way to relate client records together as families, or say Pension Trusts with their Trustees, etc.

So in some cases I have Mr Smith, Mrs Smith, little Bobby Smith Jr. and the Smith Family Trust, and as far as the humans who want the report are concerned, they are the 'same thing'. Sadly the software thinks they are very much different things.

On the other hand, I have many Jones's, not all of whom are related.

I can use Group within my pivot table to associate the disparate Smith data, but I can't work out how to separate the Jones's.

The data is extracted fresh each month so although I know that going into my data and changing it is an option, I'd like to be able to build something I can plumb my raw data into that will do the trick, because it's a terrible faff.

Here's a snip of my pivot table showing two groups I've created, and a bunch of Coopers I have to split into two separate Cooper families:



I might be approaching the whole thing the wrong way, I'm not wedded to using a pivot table I'll do whatever works.

There's probably 100 or so of these cases. I may just have to bite it and build a big lookup table.

Thanks in advance for any advice!

 

[Chihiro]

Lookup table is the most robust and easy to maintain in this case. I assume there are no identifier to link groups (unless your backend allows additional field and that can be managed).

 

[staregirl]

*sigh* No, it's the joys of in-built reports - the system does have unique identifiers for clients but it does not include them in this report, or anything that would work as a key.

There's a user report-builder but it isn't flexible enough to build this specific report and add to it, sadly.

I was hoping there was a way to split the 'group' of Coopers into sub-groups or something, but I guess it only works the one way round.

I'm investigating the possibilities of extracting various other reports and using Power Pivot to join them to minimise manual upkeep of a lookup table - then I'd only need aliases for the problem families, I guess.

Thanks for your reply

 

[staregirl]

Oh, no I won't, apparently Power Pivot isn't available for my version of Excel. Gah.