In the Merge box, add Finance Table and Sales Table to the first and second drop-down lists, respectively.Select a sheet, go to Data => New Query => Combined Queries => Merge as shown in the screenshot below: After creating a separate connection for the Finance Table sheet, the Workbook Queries box should contain both connections as shown below:.Repeat the same process for other tables (in this case, Finance Table). A Workbook Queries box will open on the right-hand side of your workbook with the name of your table/range.From the Load To dialog box, select Only Create Connection.Select Close and Load To… from the drop-down list. From the resulting Power Query Editor, click on Close and Load.For this tutorial, we’ll leave it ticked because our Table has headers. From the subsequent Create Table box, tick or untick My table has headers.On your Data tab, go to the Get & Transform section and click From Table. Before merging our sheets, we need to create separate Power Query connections for our Finance Table and Sales Table sheets.Let’s merge two sheets ( Finance Table and Sales Table) with a matching column ( Product ID) as seen in the screenshots below: Power Query is available as a free add-in on Excel 20, while it’s a built-in feature from Excel 2016 onward. You can use Power Query to merge Excel sheets based on a common column. How to merge two Excel sheets based on one column As you can see in the image below, 3 duplicate values were removed, and we’re now left with unique values. Click OK on the box that notifies you of the number of duplicates removed.Then, use the Columns section to tick or untick the columns that contain duplicate values (in our case, we ticked all the columns). In the Remove Duplicates box, untick the My data has headers option to remove duplicate headers.To remove the duplicates in the merged data, go to Data tab => Data Tools => Remove Duplicates.However, you can either copy-paste sheets or merge them using Coupler.io and then use the Remove Duplicate command to remove duplicate entries.įor example, let’s remove duplicate entries (that is, the red and blue cells) from the Data I sheet below. Merge Excel sheets without duplicatesĮxcel doesn’t provide a built-in function to merge sheets and remove duplicates in one click. As well as merging sheets in Excel, you can also use the Coupler.io solution to combine Google Sheets into one. This is our result:Īs you can see in the image above, Coupler.io adds a “Sheet Name” column to help you differentiate the merged data. Toggle the Automatic data refresh to select your schedule preferences.Īfter configuring your schedule settings, click Save and Run to view the combined sheets.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |