How to populate a column from multiple other sheets

[cisloisa]

Hi all,

I am trying to populate a column on a master sheet from multiple different sup sheets. 

For example, I have the master sheet that has a column labeled companies. I would like to have this column automatically populated from the other sheets that are in the workbook. But I dont want the same company listed twice on the master sheet. So If the master sheet is empty and Sheet 2 has company A listed under it and sheet 3 has company A and B, how do I get company A and company B listed on the master sheet only once. 

[#2]

@cisloisa

Hi,

You can do so using Get & Transform Data (aka Power Query) by creating a query from each table in the other sheets, then append all queries tables into one table.

After then, you can remove the duplicates from the table and then load it to the Master worksheet.

This is what I did in the attached Example workbook.

Please download it and test it by adding the same company in Sheet2 and Sheet3, then go to Master, Right-click the table and select Refresh and see the result.

To learn more about the Append process in Power Query, please check out this video.

Regards