Excel VBA Code for concatenating data in multiple columns and removing duplicates

Question

In reference to the attached "Example Photo" Image...

I would like to concatenate the unique data in Columns I and K into one cell (separated by line break) and remove the duplicated information in the other columns. My goal is to have the data look like rows 2 and 7 without the duplicated rows in between.

Answer 1

You can download Power Query or if you have Excel 2016 it is a default its name is Get & Tranform in the Data Tab.

1. Select any cell in your main table.
2. Go to Power Query or Data and select From Table/Range.
3. It will be a box with the range OK.

It will open the Query Editor

4. Go to Home select Group by.

5. In the Options:

   • Group by: Add all the fields you don't want to concatenate.
   • New Column name: It could be "Group".
   • Operation: Select All Rows.
   • OK.

6. Go to Add Column select Custom Columna.

7. Concatenate field Name

   [Column Named Step 5][Column Name where is the data to concatenate]

8. Go to the new field and click in the right corner (Arrows) and select Extract Values....

9. Select delimiter #(lf) OK.
10. Go to Home tab and select Advanced Editor.
11. There look for ""#(lf)"" and delete the extra "" it should be "#(lf)" click in Done.
12. Got Home select Close & Load.

It will create a new sheet with a table with your new data.

11. Use Wrap Text in Home tab to see the lines break.

You can append more data in the main table and it will be just a right click refresh in the Power Query Table and you will get your data.

I made this tutorial. It is in Spanish but I am using the English Excel version.