255 character limit in Pivot Table cell

[adambc]

I have a table, "AllGroupMembers", with 2 columns ...

- column A = GroupName
- column B = MemberName

For each MemberName there are multiple rows depending on how many GroupName(s) they belong to - I need to create a list with unique GroupName rows in column A, with all MemberName(s) in the group in the corresponding cell in column B ...

I have previously used a Pivot Table with a custom "GroupMembers" Measure ie (GroupMembers)=CONCATENATEX(AllGroupMembers, [MemberName], ",") - Rows = GroupName, Values = GroupMembers - which has worked fine until now ...

Unfortunately the source table has expanded to c.10,000 rows and I am now hitting the "This PivotTable field isn't available because it exceeds the total number of characters that a cell can contain." error ...

And no matter what I try I cannot find a way round it!!!

Can anyone help me please, either with a workround, or an alternative solution (eg can this be done in Power Query)?

Thanks ...

 

[adambc]

The limit for a cell is not 255 characters, it's 32767.

Let me do some testing with a larger mock data set

@RoryA

Using a combination of =LEN([@MemberName]) in column C and =SUBTOTAL(109, C4:C9999) and then filtering GroupName, I identified one Group that even without ", " in between each entry exceeds 32767 characters ...

I deleted every row for that Group and saved (Save As to preserve my data!) the Workbook ...

And hey presto, the Measure I added works (after refreshing the Pivot Table to remove the "rogue" GroupName first) ...

Thank you as always for your help in getting me to the answer ...

But why is there a 32767 limit?!!!

PS; having scanned manually (sigh!) the "rogue" Group, I now find that the source data is at fault - it's an extract provided by a third party and for some reason there are a LARGE number of VERY LONG "rogue" MemberName(s)!!!

 

[RoryA]

But why is there a 32767 limit?!!!

Why would you ever need that much text in one cell? Who’s going to read it, or even be able to see it all?

 

[GraH]

Why would you ever need that much text in one cell? Who’s going to read it, or even be able to see it all?

Besides this...
Is it because that's 32 bits? So this is the longest possible string that excel can store as a single value? Or am I way off? @adambc, I'm sure we can find the reason on the excel online help Pages of Microsoft.

 

[adambc]

Why would you ever need that much text in one cell? Who’s going to read it, or even be able to see it all?

@RoryA

Agreed - there were 1,496 rogue MemberName(s) each containing 45 characters - I’ll let you do the arithmetic!