The solution proposed today with this tutorial will allow you to compare two tables in Excel in a very short time. To apply it I used three solutions including the macro of Excel. I don't know if you are practical, but if you are not familiar with VBA or Excel formulas, I will help you, explaining the steps to follow. Don't worry, you don't need to be an Excel expert, don't be scared and read carefully what I'm about to tell you.
It happens very often to have tables in different sheets in Excel that contain the same headings but different fields. Imagine having a registry table with lots of obsolete records and having to match it with an updated registry table. Doing it manually would be a huge undertaking.
Here is the purpose of this tutorial. I will reveal to you like compare two tables in Excel with a simple click and saving you precious time. You choose the mode that is most comfortable for you.
How to compare two tables in Excel
If you have two tables in Excel and want to compare their columns, a great solution is to use the Conditional Formatting. First of all highlight the first column starting from the second row and in the top menu, go up Formulas and next to Management names, awarded his Define name.
In the window that appears, assign a name to the column, for example Table 1, and press on Ok to confirm. Do the same for the other column you want to compare by naming it Table 2.
How to name cells in Excel
Then click on Management names to view the result and to check the presence of the two tables whose names you have defined.
Then go on Home> Conditional Formatting> New rule.
Choose the voice Use a formula to determine which cells to format. In the field Format the values for which this formula returns True inserts this line of code:
=ACCOUNT IF(Table2;A2)=0
Then press on Size and choose a fill color. Confirm everything by pressing the button OK.
Do the same for the other column but in the field Format the values for which this formula returns True enter the code:
= CONTA.SE (Tabella1; C2) = 0
Then press on Size and choose a different fill color than the one you chose for the other column. Confirm everything by pressing the button OK.
The result will be the following:
Excel will color the different cells in a different color, leaving the corresponding cells colorless. Simple isn't it?
How to view the formulas of an Excel sheet
How to compare two columns and cells in Excel
For a quicker and easier solution you can compare two columns and cells in Excel using the Formula SE.
What you will need to do is enter in column D, starting with the cell D2 the following formula:
= IF (A2 = C2; "TRUE"; "FALSE")
Drag or select the other cells as well and right click to choose Paste Special e Paste Formula (the icon with Fx).
This way you will immediately identify which cells are mismatched and which ones should have value VERO.
Excel: the formula entered does not return any results
How to compare two tables in Excel with VBA
apri Excel and go to the menu Development> Visual Basic (alternatively you can press Alt + F11). The console of Microsoft Visual Basic Applications Edition.
If you don't find the menu in Excel Development, you can add it by going to File> Options> Ribbon Customization. In the drop-down menu of Choose commands da (in the left column), select Development and then press the button Add to insert it into your Ribbon. Press the button OK and returning to the Excel workbook you should find the menu Development.
In the console of Microsoft Visual Basic Applications Edition, from the left column, in VBA Project, insert in This_work_folder the following lines of code:
Private Sub Workbook_Open ()
Call RunCompare
End Sub
In the top menu, click on Insert> Form and copy / paste the lines you find below:
Sub RunCompare()
Call sheet comparison (InputBox ("Enter the name of the first sheet"), InputBox ("Enter the name of the second sheet"))
End Sub
The function proposed above is used to make you indicate the names of the sheets to be compared.
Sub comparafogli(NomeFoglio1 As String, NomeFoglio2 As String)
Dim myCell As Range
Dim differences As Integer
For Each myCell In ActiveWorkbook.Worksheets(NomeFoglio2).UsedRange
If Not myCell.Value = ActiveWorkbook.Worksheets(NomeFoglio1).Cells(myCell.Row, myCell.Column).Value Then
myCell.Interior.Color = vbYellow
differences = differences + 1
else
myCell.Interior.Color = vbWhite
End If
Next
MsgBox differences & "differences found", vbInformation
ActiveWorkbook.Sheets(NomeFoglio2).Select
End Sub
Save the code you just entered by going to File> Save File Name and choose the format .xlsm, or by pressing CTRL + S on your keyboard at the same time.
Launch the macro you just set by pressing F5 on the keyboard or from the VBA console, Run> Run Sub / UserForm.
At the end a message will tell you how many differences have been found while the compared sheets will highlight in yellow all those cells where the content is different from sheet to sheet.
How to compare two Excel tables with third party software
Ok I understand you are not an Excel expert and you have difficulty applying the suggestions I indicated in the previous lines. Then you just have to resort to Compare Sheets. It is an easy-to-use software that does not require installation.
To use Compare Sheets, create a new workbook and select the first table to compare including the column headings. Copy the selected table and paste it into a new workbook by placing it in Sheet1 doing Paste Special to paste only the values.
Do the same with the second table, put it in the same workbook but in Sheet2 (Paste Special> Paste Values)
Try to make the column headings of the two tables identical for comparison.
Save the new workbook with the name you want but in the format Excel 97-2003.
Open a new workbook in Compare Sheets.
Type the file name in the "Result file”To have the result in a separate file or save it in a new sheet to the current file.
Compare the tables.
