Forum Discussion
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?
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.
7 Replies
Maybe with Power Query as shown in the attached file.
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.
- 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.
Could you attach a small sample workbook that demonstrates the error?
