The Compare Tables add-in compares two Excel lists for duplicate and unique rows.
Duplicate rows are the rows that are present in both the first and the second table. Unique rows are the rows that are present in table 1 but are missing in table 2. The add-in searches for duplicates and uniques by the key columns you select. The dupes and uniques are identified only in table 1.
Please make sure both tables are opened in Excel. They can be in different files but the add-in will compare them as long as they are open in the same instance of Excel.
We strongly recommend keeping the checkbox Create a backup copy of the worksheet selected, as Excel won't let you undo the changes made by the add-in.
On the Ablebits Data tab, in the Merge group, click Compare > Compare Tables:
Once you start the add-in, you will see the step you are in at the top of the window.
First, select the range with your table 1: all changes will be applied to the table you select in this step.
If you select just one cell in the table, the add-in will automatically highlight the used range at the start. You can change the range by editing it manually in the Select your first table field, using the Select range icon there, or simply by choosing the right cells in Excel.
In this step, you can see all open workbooks and worksheets in the Select your second table field. Choose the second Excel worksheet to check for the same values there:
Click any cell in a table, and get the entire used range selected automatically. If you need to use only a specific range for comparison, click the Select range icon and highlight the necessary cells.
When the second table is chosen, click Next.
Specify whether you are going to search for duplicate or unique values:
This step displays a list of columns in both Excel spreadsheets.
Select the column(s) from the first table and the matching column(s) from the second worksheet to compare them and check for duplicate or unique data.
Once the columns are chosen, click the Next button.
Finally, you can choose what to do with the found duplicate or unique rows:
Click Finish and voila! The dupes or uniques are found in your table 1 and processed the way you need.
In Step 5, before clicking the Finish button, click Save scenario:Decide on a name for your scenario and enter it into the Compare Tables dialog. Then click OK:
To run any of your saved scenarios, start the Compare Tables tool, select the necessary scenario from the list, specify which worksheet is going to be used as the first table, and click Start:
Thank you for the data shared. However l have data of shop that make transactions that are also received by the bank. in this two diffirent sheets DATE, TERMINAL ID, CARD #,REF #, and VALUE are similar. How do l match this data showing me that the very same transcation made at the shop is the same that was seen at the bank.
For us to be able to help you better, please send us a small sample workbook with your source data and the result you expect to get to email@example.com. I kindly ask you to shorten the tables to 10-20 rows.
Note! The result sheet is of great importance and often gives us a better understanding of your task rather than any text description. Please don't forget to include it.
We'll look into your task and see if our software can help.
I have question about duplicate data.
I want to check any duplicated data between group of sheets.
What can I refer for this problem?
I have 10K data and 10 sheets, I want to check all this data not duplicate in any sheets.
Thank you for your question. It looks like the Compare Multiple Sheets tool of our Ultimate Suite can help you. Please visit a detailed guide for the tool: https://www.ablebits.com/docs/excel-compare-multiple-sheets/
In case you need more specific help with you task, please email us at firstname.lastname@example.org.
I see with the newer version it does not remember which sheet/book you compared to last and have to re-select every time. Is it possible to set this as the previous version to remember last compared.
Example I have Sheet 1, Sheet 2, Sheet 3, Sheet 4. I compare Sheet 4 to Sheet 3. If I was to compare Sheet 2 it must automatically reselect Sheet 3 for my next compare. Currently soon as you open compare and decide to select a sheet it goes to the first book opened to first sheet, which in the example will be Sheet 1.
Please advise if it is possible to set it as previous versions such as 16
Thank you for your message. Is it possible for you to send us a screenshot of the previous version you mentioned to email@example.com? Alternatively, specify which steps of the add-in must include the requested functionality. Thank you in advance.
is there a way to have it copy the whole row the unique value is detected in, into the new workbook?
Thank you for your question.
Sure, you can copy the found results to another Excel workbook. Simply choose the corresponding option on step 5 of the add-in and select the location of the result. Thank you.
Could you let me know how to compare multiple sheets? I just see "Compare Two Tables", but for our research, we have to compare four sheets from different date.
Please try out another tool, Compare Multiple Sheets, that may turn out to be more helpful for your task. If you have any questions or need further assistance, please email to firstname.lastname@example.org. Thank you.
Hi, I have one spreadsheet with a list of names, account numbers and addresses for a contact list and a second spreadsheet with names and account numbers that is a do not call list. I need to compare the two sheets, looking for account numbers, and delete the entire row from the main contact worksheet if the same account number exists on the do not call sheet. I have looked through the tools from Ablebits and don't see any way to be able to do this. Can this be done with your tools, or is there another way to do this that you know of? Any assistance would be appreciated.
Thank you for the comment.
Have you tried the Compare Sheets tool?
Looks like it does what you need.
Please feel free to contact us if you need any further assistance.
I have tried the both the compare two sheets and compare multiple sheets tools, but I don't see an option to show matches, only differences.
I have looked into your task once again, and if I understand it correctly, the Compare Tables - the tool of this page - can help you.
Your steps should be the following:
Step 1. Select your main table (the contact list with names, account numbers, and addresses).
Step 2. Select the entire range of your "do not call list".
Step 3. Choose the Duplicate values option.
Step 4. Select the columns with account numbers in table 1 and table 2.
Step 5. Choose the Delete values option.
Please let me know if this helped.
I have a question about a voter 'name matching' problem. *
BTW, news about the 'name matching' problem went 'viral' in Nov 2018 when the man who won for governor of Georgia was also the Secretary of State who wrote the rules for deciding who got to vote. He narrowly defeated Stacey Abrams, who had a chance to become the first black female governor. Even in defeat, she became nationally famous for her fight against the disenfranchisement of a large block of voters whose votes weren't counted because the name was not precisely identical to the registered name and there was no chance to appeal.
* The 'name matching' problem leads me to ask: Do you have plans to enhance the table comparison Action options to = ('equals') with familiar options elsewhere in Microsoft Office such as 'contains'? Right now, when you search large databases of voters Gwen Clifton doesn't match Gwendolyn Clifton.
Thank you for the informative and interesting comment.
I think that our Fuzzy Duplicate Finder or Find Similar Cells are the tools that may help with the described problem.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!