Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

How to merge sheets into one and remove the duplicates in Excel?

If there are some sheets with same structure and some duplicates in a workbook, the job is to combine the sheets into one sheet and remove the duplicate data, how can you quickly handle it in Excel?
doc merge sheets into one remove duplicates 1

Merge sheets into one and remove duplicates with Copy and Paste

Merge sheets into one and remove duplicates with Kutools for Excel’s Combine functiongood idea3

Merge two tables into one with duplicates removed and new data updated by Kutools for Excel’s Tables Mergegood idea3

Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10!

You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free trial of Office Tab!

ot excel

Merge sheets into one and remove duplicates with Copy and Paste

In Excel, there is no built-in function can quickly merge sheets and remove duplicates, you just can copy and paste the sheet contents one by one then apply Remove Duplicates function to remove the duplicates.

1. Select the contents in Sheet1 you use, press Ctrl+C to copy the contents, then go to a new sheet to place the cursor in one cell, press Ctrl + V to paste the first part.

2. Repeat above step to copy and paste all sheet contents into one sheet.
doc merge sheets into one remove duplicates 2

3. Select the combined contents, click Data > Remove Duplicates.
doc merge sheets into one remove duplicates 3

4. In the Remove Duplicates dialog, check or uncheck My data has headers as you need, keep all columns in your selection checked.
doc merge sheets into one remove duplicates 4

Tip: If your headers have been repeated in the selection, uncheck My data has headers, if not, check it.

5. Click OK. A dialog pops out to remind you the number of removed items and remain items.
doc merge sheets into one remove duplicates 5

6. Click OK. The sheets have been combined into one with all duplicates removed.
doc merge sheets into one remove duplicates 6


Merge sheets into one and remove duplicates with Kutools for Excel’s Combine function

If there are dozens or hundreds of sheets needed to be merged into one, manual copy and paste will be time-wasted. Fortunately, the Combine function of Kutools for Excel can quickly combine multiple sheets into one sheet.

Kutools for Excel, with more than 200 handy functions, makes your jobs easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Enable Excel, and click Kutools Plus > Combine, in the Combine Worksheets dialog, check Combine multiple worksheets from workbooks into one worksheet option.
doc merge sheets into one remove duplicates 7
doc merge sheets into one remove duplicates 8

2. Click Next. In the second Combine Worksheets window, click Add > File or Folder to open the Open dialog, then select the workbook you want to combine all sheets into one.
doc merge sheets into one remove duplicates 9

3. Then check the workbook in Workbook list pane you have added just now, go to the right section of Worksheet list pane, confirm the sheets you want to combine into one are checked.
doc merge sheets into one remove duplicates 10

4. Click Next, in the last step of the dialog, specify the setting of the combine sheet as you need. If you have one row header of each sheet, you just need to type 1 in the textbox of Title row number.
doc merge sheets into one remove duplicates 12

5. Click Finish. Select one location and give a name for the combined workbook.
doc merge sheets into one remove duplicates 13

7. Click Save. And after combining, a dialog pops out to remind you if open the combined workbook.
doc merge sheets into one remove duplicates 14

8. Click Yes to open the combined workbook, then the data have been combined into one sheet.
doc merge sheets into one remove duplicates 15

9. Select the combined data, click Kutools > Select > Select Duplicate & Unique Cells.
doc merge sheets into one remove duplicates 16

10. In the Select Duplicate & Unique Cells dialog, check Duplicates ( Except 1st one) option and Select entire rows option.
doc merge sheets into one remove duplicates 17

11. Click Ok > OK to close Select Duplicate & Unique Cells dialogs. Now the duplicate rows have been selected.
doc merge sheets into one remove duplicates 18

12. Right click at one of the selected row number, select Delete from context menu.
doc merge sheets into one remove duplicates 19

Now the sheets have been combined without duplicate data.
doc merge sheets into one remove duplicates 20


Merge two tables into one with duplicates removed and new data updated by Kutools for Excel’s Tables Merge

If you have two tables needed to be combine into one, and you want to remove the duplicates and update the table with new data, you can use Kutools for Excel’s Tables Merge utility.
doc merge sheets into one remove duplicates 21

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Select the main table you want to update with new data of other table, click Kutools Plus > Tables Merge.
doc merge sheets into one remove duplicates 22

2. In the popping Tables Merge dialog, select the second table in Select the lookup table section.
doc merge sheets into one remove duplicates 23

3. Click Next, in step 2, check the key column you use to compare two tables based on.
doc merge sheets into one remove duplicates 24

4. Click Next,in step 3,select the columns you will use to update the main table. In this case, I check all columns.
doc merge sheets into one remove duplicates 25

5. Click Next, in the step 5 (in this tutorial, there is no new column to be added, so the step 4 of the dialog has been skipped), check Add mismatched rows to the end of the main table option in Add options section, check Only update the cells when there is data in the lookup table option in Update options section.
doc merge sheets into one remove duplicates 26

6. Click Finish. Now the two tables are merged into the main table. You can see the main table has been updated with new data of the second table.
doc merge sheets into one remove duplicates 27


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.