SOLVED

Trying to consolidate a long list of notes

Copper Contributor

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 and abbreviated example:

NotesContact
Notes 1John
Notes 2Jill
Notes 3John
Notes 4greg
Notes 5zacharias
Notes 6millie
Notes 7John
Notes 8jon
Notes 9greg
Notes 10wilson

 

Ultimately, want to combine them all to look like:

ContactFull Notes
JohnNotes 1 Notes 3 Notes 7
JillNotes 2
gregNotes 4 Notes 9
zachariasNotes 5
millieNotes 6
jonNotes 8
wilsonNotes 10

 

How can I make that happen?

7 Replies
best response confirmed by EtterOps (Copper Contributor)
Solution

@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 

Maybe with Power Query as shown in the attached file.

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.

@EtterOps 

Could you attach a small sample workbook that demonstrates the error?

@Hans Vogelaar Here is a sample of what I get back. 

 

MS Tech Help Test.xlsx

@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!

Thanks, @Hans Vogelaar. You've saved me a ton of work.
1 best response

Accepted Solutions
best response confirmed by EtterOps (Copper Contributor)
Solution

@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.

View solution in original post