Home

How to populate a column from multiple other sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-806779%22%20slang%3D%22en-US%22%3EHow%20to%20populate%20a%20column%20from%20multiple%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806779%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20populate%20a%20column%20on%20a%20master%20sheet%20from%20multiple%20different%20sup%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20have%20the%20master%20sheet%20that%20has%20a%20column%20labeled%20companies.%20I%20would%20like%20to%20have%20this%20column%20automatically%20populated%20from%20the%20other%20sheets%20that%20are%20in%20the%20workbook.%20But%20I%20dont%20want%20the%20same%20company%20listed%20twice%20on%20the%20master%20sheet.%20So%20If%20the%20master%20sheet%20is%20empty%20and%20Sheet%202%20has%20company%20A%20listed%20under%20it%20and%20sheet%203%20has%20company%20A%20and%20B%2C%20how%20do%20I%20get%20company%20A%20and%20company%20B%20listed%20on%20the%20master%20sheet%20only%20once.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-806779%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-806947%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20populate%20a%20column%20from%20multiple%20other%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-806947%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F392948%22%20target%3D%22_blank%22%3E%40cisloisa%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20do%20so%20using%20Get%20%26amp%3B%20Transform%20Data%20(aka%20Power%20Query)%20by%20creating%20a%20query%20from%20each%20table%20in%20the%20other%20sheets%2C%20then%20append%20all%20queries%20tables%20into%20one%20table.%3C%2FP%3E%3CP%3EAfter%20then%2C%20you%20can%20remove%20the%20duplicates%20from%20the%20table%20and%20then%20load%20it%20to%20the%20Master%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20did%20in%20the%20attached%20Example%20workbook.%3C%2FP%3E%3CP%3EPlease%20download%20it%20and%20test%20it%20by%20adding%20the%20same%20company%20in%20Sheet2%20and%20Sheet3%2C%20then%20go%20to%20Master%2C%20Right-click%20the%20table%20and%20select%20%3CSTRONG%3ERefresh%20%3C%2FSTRONG%3Eand%20see%20the%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20learn%20more%20about%20the%20Append%20process%20in%20Power%20Query%2C%20please%20check%20out%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DTTMODKl6GNY%26amp%3Bfeature%3Dyoutu.be%26amp%3Blist%3DPLHrPHBbDHgT0XiXLpBaREfRpRlx8SyGkf%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Evideo%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
cisloisa
Occasional Visitor

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. 

1 Reply

@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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies