SOLVED

# Trying to consolidate a long list of notes

Occasional Contributor

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

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

Ultimately, want to combine them all to look like:

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

How can I make that happen?

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

# Re: Trying to consolidate a long list of notes

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.

# Re: Trying to consolidate a long list of notes

Maybe with Power Query as shown in the attached file.

# Re: Trying to consolidate a long list of notes

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.

# Re: Trying to consolidate a long list of notes

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

# Re: Trying to consolidate a long list of notes

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

MS Tech Help Test.xlsx

# Re: Trying to consolidate a long list of notes

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!

# Re: Trying to consolidate a long list of notes

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