Feb 21 2022 02:24 PM
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?
Feb 21 2022 02:38 PM
SolutionLet'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.
Feb 21 2022 02:46 PM
Maybe with Power Query as shown in the attached file.
Feb 21 2022 03:53 PM
Feb 22 2022 03:55 AM
Could you attach a small sample workbook that demonstrates the error?
Feb 22 2022 09:56 AM - edited Feb 22 2022 10:12 AM
Feb 22 2022 11:54 AM
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!
Feb 22 2022 12:44 PM
Feb 21 2022 02:38 PM
SolutionLet'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.