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 😀

 

DOWNLOAD THE EXCEL FILE

 

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

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI