SOLVED
Home

Align Columns with Duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-763169%22%20slang%3D%22en-US%22%3EAlign%20Columns%20with%20Duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763169%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20currently%20working%20on%20a%20user%20list%20that%20says%20what%20applications%20they%20have%20installed.%20I%20wish%20to%20align%20the%20values%20like%20you%20would%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexcelribbon.tips.net%2FT008316_Synchronizing_Lists.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcelribbon.tips.net%2FT008316_Synchronizing_Lists.html%3C%2FA%3E%3C%2FP%3E%3CP%3EIt%20uses%20this%20formula%2C%26nbsp%3B%3DIF(ISNA(VLOOKUP(A2%2CF%3AG%2C2%2CFALSE))%2C0%2CVLOOKUP(A2%2CF%3AG%2C2%2CFALSE))%3C%2FP%3E%3CP%3Ewhich%20only%20aligns%20if%20the%20two%20master%20columns%20that%20match%20together%20align%20well.%3C%2FP%3E%3CP%3EHowever%2C%20there%20are%20some%20duplicates%20in%20the%20list%2C%20and%20I%20would%20like%20those%20duplicates%20to%20go%20into%20the%20same%20cell.%20Let%20me%20give%20you%20an%20example.%3C%2FP%3E%3CP%3EWhat%20I%20have%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EJohn%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EJohn%26nbsp%3B%3C%2FTD%3E%3CTD%3EApp%20%231%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJane%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EJohn%26nbsp%3B%3C%2FTD%3E%3CTD%3EApp%20%232%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETom%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EJane%26nbsp%3B%3C%2FTD%3E%3CTD%3EApp%20%231%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERob%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERob%3C%2FTD%3E%3CTD%3EApp%20%231%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEtc.%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERob%3C%2FTD%3E%3CTD%3EApp%20%233%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEtc.%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERob%3C%2FTD%3E%3CTD%3EApp%20%234%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EWhat%20I%20would%20like%20to%20do%2C%20is%20that%20those%20apps%20all%20appear%20next%20to%20the%20user's%20name%20in%20one%20cell%2C%20like%20so.%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EJohn%26nbsp%3B%3C%2FTD%3E%3CTD%3EApp%20%231%2C%20App%20%232%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EJohn%26nbsp%3B%3C%2FTD%3E%3CTD%3EApp%20%231%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJane%26nbsp%3B%3C%2FTD%3E%3CTD%3EApp%20%231%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EJohn%26nbsp%3B%3C%2FTD%3E%3CTD%3EApp%20%232%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERob%3C%2FTD%3E%3CTD%3EApp%20%231%2C%20App%20%233%2C%20App%20%234%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EJane%26nbsp%3B%3C%2FTD%3E%3CTD%3EApp%20%231%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEtc.%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERob%3C%2FTD%3E%3CTD%3EApp%20%231%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEtc.%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERob%3C%2FTD%3E%3CTD%3EApp%20%233%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEtc.%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ERob%3C%2FTD%3E%3CTD%3EApp%20%234%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20this%20be%20possible%2C%20through%20some%20godly%20formula%3F%3C%2FP%3E%3CP%3EIts%20a%20300%20row%20file%20that%20I%20might%20have%20to%20do%20again%20sometime%2C%20so%20I%20would%20rather%20not%20do%20it%20manually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EAndre%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-763169%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763271%22%20slang%3D%22en-US%22%3ERe%3A%20Align%20Columns%20with%20Duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763271%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378431%22%20target%3D%22_blank%22%3E%40Mazarith%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20Excel%20365%2C%20you%20may%20use%20the%20TEXTJOIN()%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20example%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fchandoo.org%2Fwp%2Fvlookup-multiple-matches-trick%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fchandoo.org%2Fwp%2Fvlookup-multiple-matches-trick%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3CBR%20%2F%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763338%22%20slang%3D%22en-US%22%3ERe%3A%20Align%20Columns%20with%20Duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3BThis%20is%26nbsp%3B%3CSTRONG%3Eexactly%3C%2FSTRONG%3Ewhat%20I%20needed%2C%20thank%20you%20so%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Mazarith
New Contributor

Hi there!

 

I'm currently working on a user list that says what applications they have installed. I wish to align the values like you would here: https://excelribbon.tips.net/T008316_Synchronizing_Lists.html

It uses this formula, =IF(ISNA(VLOOKUP(A2,F:G,2,FALSE)),0,VLOOKUP(A2,F:G,2,FALSE))

which only aligns if the two master columns that match together align well.

However, there are some duplicates in the list, and I would like those duplicates to go into the same cell. Let me give you an example.

What I have:

John   John App #1
Jane   John App #2
Tom  Jane App #1
Rob  RobApp #1
Etc.  RobApp #3
Etc.  RobApp #4

What I would like to do, is that those apps all appear next to the user's name in one cell, like so.

John App #1, App #2 John App #1
Jane App #1 John App #2
RobApp #1, App #3, App #4 Jane App #1
Etc.  RobApp #1
Etc.  RobApp #3
Etc.  RobApp #4

 

Would this be possible, through some godly formula?

Its a 300 row file that I might have to do again sometime, so I would rather not do it manually.

 

Thanks,

Andre

2 Replies
Solution

Hello @Mazarith,

 

If you have Excel 365, you may use the TEXTJOIN() function.

 

See example here:

https://chandoo.org/wp/vlookup-multiple-matches-trick/ 

 

Hope this helps!
PReagan

@PReagan This is exactly what I needed, thank you so much.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies