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
Deleted
Not applicable

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
Highlighted
Solution

Hello @Deleted,

 

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
Taking values from certain rows based on data input
Drakerla in Excel on
5 Replies
Add Column Using Count
Tim Hunter in SQL Server on
0 Replies
reverse text to columns for .csv import
milly87 in Excel on
3 Replies
Last Value Column from Another Column in Same List
Yepitsme in SharePoint on
6 Replies