SOLVED

Trying to consolidate a long list of notes

%3CLINGO-SUB%20id%3D%22lingo-sub-3195749%22%20slang%3D%22en-US%22%3ETrying%20to%20consolidate%20a%20long%20list%20of%20notes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3195749%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20nearly%2041%2C000%20individual%20notes.%20I%20have%20a%20column%20for%20notes%20and%20a%20column%20for%20contacts.%20Each%20contact%20may%20have%20multiple%20notes%20listed%20(from%20a%20single%20note%20up%20to%20dozens%20of%20notes).%3C%2FP%3E%3CP%3ESimple%20and%20abbreviated%20example%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22128%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3ENotes%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EContact%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENotes%201%3C%2FTD%3E%3CTD%3EJohn%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENotes%202%3C%2FTD%3E%3CTD%3EJill%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENotes%203%3C%2FTD%3E%3CTD%3EJohn%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENotes%204%3C%2FTD%3E%3CTD%3Egreg%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENotes%205%3C%2FTD%3E%3CTD%3Ezacharias%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENotes%206%3C%2FTD%3E%3CTD%3Emillie%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENotes%207%3C%2FTD%3E%3CTD%3EJohn%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENotes%208%3C%2FTD%3E%3CTD%3Ejon%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENotes%209%3C%2FTD%3E%3CTD%3Egreg%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ENotes%2010%3C%2FTD%3E%3CTD%3Ewilson%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUltimately%2C%20want%20to%20combine%20them%20all%20to%20look%20like%3A%3C%2FP%3E%3CTABLE%20width%3D%22216%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EContact%3C%2FTD%3E%3CTD%20width%3D%22152%22%3EFull%20Notes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJohn%3C%2FTD%3E%3CTD%3ENotes%201%20Notes%203%20Notes%207%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJill%3C%2FTD%3E%3CTD%3ENotes%202%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Egreg%3C%2FTD%3E%3CTD%3ENotes%204%20Notes%209%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ezacharias%3C%2FTD%3E%3CTD%3ENotes%205%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Emillie%3C%2FTD%3E%3CTD%3ENotes%206%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ejon%3C%2FTD%3E%3CTD%3ENotes%208%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Ewilson%3C%2FTD%3E%3CTD%3ENotes%2010%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20make%20that%20happen%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3195749%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3195835%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20consolidate%20a%20long%20list%20of%20notes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3195835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313944%22%20target%3D%22_blank%22%3E%40EtterOps%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20your%20list%20is%20in%20columns%20A%20and%20B.%3C%2FP%3E%0A%3CP%3EEnter%20'Contact'%20in%20D1%20and%20'Full%20Notes'%20in%20E1.%3C%2FP%3E%0A%3CP%3EIn%20D2%2C%20enter%20the%20formula%20%3DSORT(UNIQUE(B2%3AB11))%3C%2FP%3E%0A%3CP%3EThis%20will%20automatically%20spill%20to%20the%20cells%20below.%3C%2FP%3E%0A%3CP%3EIn%20E2%2C%20enter%20the%20formula%20%3DTEXTJOIN(%22%20%22%2CTRUE%2CIF(%24B%242%3A%24B%2411%3DD2%2C%24A%242%3A%24A%2411%2C%22%22))%3C%2FP%3E%0A%3CP%3EFill%20down%20from%20E2%20by%20double-clicking%20the%20fill%20handle%20in%20the%20lower%20right%20corner%20of%20E2.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3195870%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20consolidate%20a%20long%20list%20of%20notes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3195870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313944%22%20target%3D%22_blank%22%3E%40EtterOps%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20with%20Power%20Query%20as%20shown%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3198468%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20consolidate%20a%20long%20list%20of%20notes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3198468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313944%22%20target%3D%22_blank%22%3E%40EtterOps%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20small%20sample%20workbook%20that%20demonstrates%20the%20error%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3200250%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20consolidate%20a%20long%20list%20of%20notes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3200250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BHere%20is%20a%20sample%20of%20what%20I%20get%20back.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fetteropscom-my.sharepoint.com%2F%3Ax%3A%2Fg%2Fpersonal%2Ftodd_etter_etterops_com%2FEbJoxKcK9fhPtp6dwTIYB2ABO3a_Bqzc3eDDmcYqpmQTsA%3Fe%3Dj5IHP6%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EMS%20Tech%20Help%20Test.xlsx%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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.