Forum Discussion

Copper Contributor

Feb 21, 2022

Solved

Trying to consolidate a long list of notes

I have a list of nearly 41,000 individual notes. I have a column for notes and a column for contacts. Each contact may have multiple notes listed (from a single note up to dozens of notes). Simple a...

excel

office 365

HansVogelaar
Feb 21, 2022
EtterOps

Let's say your list is in columns A and B.

Enter 'Contact' in D1 and 'Full Notes' in E1.

In D2, enter the formula =SORT(UNIQUE(B2:B11))

This will automatically spill to the cells below.

In E2, enter the formula =TEXTJOIN(" ",TRUE,IF($B$2:$B$11=D2,$A$2:$A$11,""))

Fill down from E2 by double-clicking the fill handle in the lower right corner of E2.

HansVogelaar

MVP

Feb 21, 2022

EtterOps

Let's say your list is in columns A and B.

Enter 'Contact' in D1 and 'Full Notes' in E1.

In D2, enter the formula =SORT(UNIQUE(B2:B11))

This will automatically spill to the cells below.

In E2, enter the formula =TEXTJOIN(" ",TRUE,IF($B$2:$B$11=D2,$A$2:$A$11,""))

Fill down from E2 by double-clicking the fill handle in the lower right corner of E2.

EtterOps

Copper Contributor

Feb 21, 2022

Thanks, it helped with sorting about 50% of them, but I received #VALUE! errors for the other half. This does not seem to denote there are too many characters in the cell, as I combined several Contacts with the CONCATENATE function.

HansVogelaar
MVP
Feb 22, 2022
EtterOps

Could you attach a small sample workbook that demonstrates the error?
- EtterOps
  Copper Contributor
  Feb 22, 2022
  HansVogelaar Here is a sample of what I get back.
  
  https://etteropscom-my.sharepoint.com/:x:/g/personal/todd_etter_etterops_com/EbJoxKcK9fhPtp6dwTIYB2ABO3a_Bqzc3eDDmcYqpmQTsA?e=j5IHP6
  - HansVogelaar
    MVP
    Feb 22, 2022
    EtterOps
    
    Thanks! It fails when any of the notes is longer than 255 characters. This works better:
    
    =TEXTJOIN(" ",TRUE,FILTER($A$2:$A$207,$B$2:$B$207=D2,""))
    
    It still fails if the combined length of the notes exceeds 32767 characters, because that is the maximum number of characters that fits in a cell. In your example, this occurs for Email address removed - the total length of the 67 notes is 65242. So that won't work!