Combine Duplicate Vlookup values in a Single Row

One of the common issues with VLOOKUP is it’s inability to handle duplicate values! And there is no straight way of getting around it. In this post I will share 2 methods of dealing with duplicates while performing a vlookup and combining all values in a single row.

Let’s dive in…

Consider this quick data

The Problem – Since the employees could have multiple hobbies, I would like to combine all their hobbies in a single row (separated by a comma)

Method 1: Using Excel’s TEXTJOIN function

About this formula =TEXTJOIN(“, “,TRUE,IF(F5=EmpTable[Emp ID],EmpTable[Hobbies],””))

1. The TEXTJOIN formula is recently added and is available in Excel 2016 onwards
2. The formula logic is to match
   • How many times a single employee is appearing in the list – Using IF
   • If the Employee match is found then getting the list of their hobbies
   • Then Combining that list using TEXTJOIN and separating them using a (,) Comma
3. Of course the IF function does not accept a range, so it becomes an array formula and has to be confirmed using CTRL SHIFT ENTER
4. Nifty isn’t it ?

Method 2 : Using Power Query in Excel

A simple 4 liner query will do this trick

let
Source = Excel.CurrentWorkbook(){[Name=”EmpTable”]}[Content],
#”Grouped Rows” = Table.Group(Source, {“Emp ID”}, {{“Hobbies”, each _, type table}}),
#”Added Custom” = Table.AddColumn(#”Grouped Rows”, “Hobby”, each Table.Column([Hobbies],”Hobbies”)),
#”Extracted Values” = Table.TransformColumns(#”Added Custom”, {“Hobby”, each Text.Combine(List.Transform(_, Text.From), “, “), type text}),
#”Removed Columns” = Table.RemoveColumns(#”Extracted Values”,{“Hobbies”})
in
#”Removed Columns”

I thought instead of just dumping the power query code, may be a video will be helpful, take a look 😀

More Power Query Tutorials

1. Combine Multiple Files using Power Query
2. How to do a VLOOKUP in Power Query
3. Convert Multiple Rows in Columns
4. VLOOKUP Horizontally for multiple Records
5. Repeat Row N Times
6. Ranking Problem in Power Query