How To Compare Two Excel Sheets Using Java Code

How to compare two columns in Excel?

When two or more than columns contain complex data, which cannot be compared by just seeing them, Excel provides some predefined function for this.

Excel allows the users to compare the 2 column's values. Information technology tin be washed in several ways using dissimilar methods, such equally IF, MATCH, and ISERROR functions. The method whichever you use will depend on the data construction and what you lot want from it. Either we can compare two or more columns in ane go or one row at a fourth dimension.

For example,

Compare 2 column's information row by row and get the result as either truthful or false.
Compare two columns and highlight all the matching information points (bachelor in both columns) in both columns. Conversely, highlight the differences (unmatched data points) in both columns.

Excel offers various methods and techniques of comparison. In this chapter, we volition embrace all necessary points that require to know for comparing ii columns. Here, nosotros are going to discuss the following topics in this chapter:

Compare ii columns past Verbal row match
1. Row past row comparison (unproblematic comparing)
2. Row by row comparison using IF formula
3. Highlight matching rows
Compare two columns and highlight the matches
1. Compare columns and highlight matches
2. Compare columns and highlights mismatches

Usually, when we have a large dataset with complex values, and nosotros want to find out match and unmatched data. These methods of comparing assist the users to get the expected result using these methods.

Compare two columns by Exact row friction match

It is the simplest style of comparison. It is a row-by-row comparison of data for two or more columns. We tin get exact row matches for comparing 2 columns using three different methods, i.eastward.,

Method one: Row past row comparison (elementary comparison)

Method 2: Row by row comparing using IF formula

Method iii: Highlight matching rows

These methods of comparison are only used for row-by-row comparing of two or more columns. It is helpful when you want to find the match only in the same row, non in the entire dataset.

Normally, comparing two columns row by row is not a good idea when you desire to discover all the values having more than two matches in the whole selected columns instead of in the same row.

Features

You lot can compare each row value of both columns i by one and get the result as either TRUE or False.
All values of both columns cannot be compared in one become using these methods. Therefore, information technology takes time to compare all rows of two columns.

Method 1: Row by Row comparing

This method is used for comparison two cells of the aforementioned row. It is a elementary comparing. Information technology means that we can compare two column's data row past row without using any typical formula. For example -

The following is formula for comparing the first row of two columns, A and B.

We will but compare two columns row by row.

Return Value

It volition return Truthful if both cells have exactly the same values. Otherwise, it volition render FALSE as a result. The returned value stores in some other cavalcade selected by the user while comparison.

Example

Now, see the case of how the comparison actually takes place.

Step 1: Open an Excel canvas and select a jail cell where you lot want to paste the comparing result.

Step 2: Now, go to the formula bar, where you lot tin can write the formulas.

Pace 3: Type the formula to compare the cavalcade row-by-row. E.1000.,

=A2=B2 for comparing the 2nd row of columns A and B and "press the Enter central".

Step 4: You will get the result either Truthful or Simulated. Run into that it returned TRUE as both strings are exactly the same.

Stride 5: Now, we volition prove you lot i for the False result when values are mismatched. We will cheque the 5^th row. And then, type the following formula:

=A5=B5 and printing the Enter primal.

Step half dozen: Run across that it returned FALSE, which means that A5 and B5 are non the same. There is no change in both spellings.

Similarly, you lot can perform more comparisons for other rows.

Method 2: Row by Row comparing using IF formula

This method is some other way to compare two columns. It is almost similar to the in a higher place method. In this method, nosotros will utilize the IF formula to compare two column's data row past row using. For case -

The following is a formula for comparison the first row of 2 columns (A and B) using the IF formula.

This time it will render Match if both column's values are the same, only if the exact friction match is not found, information technology volition return Mismatch equally a resultant value.

Notation: You can as well customize the upshot string in formula according to you whatsoever yous want.

Return Value

It returns the commencement string if both cells contain exactly same data. Otherwise, it returns the second cord that you have passed inside the IF function.

Example

Now, run into the example of how the comparison actually takes place.

Step 1: Open an Excel sheet and select a cell where yous want to paste the comparison result.

Step 2: Now, go to the formula bar, where write the formula to compare the column row-by-row for the seven^th row. E.g.,

=IF(A7=B7, "Match", "Mismatch") and "press the Enter key".

Step three: You will get the event either Match if the values are same or Mismatch if not. Meet that information technology returned Mismatch as both strings are not the aforementioned.

Stride 4: Similarly, bank check ane more row data to get the match upshot. Nosotros will check the 9^thursday row using the following formula:

=IF(A9=B9, "Match", "Mismatch") and press the Enter cardinal.

Step five: Run across that it returned Lucifer as the result, which ways that A9 and B9 are same.

Similarly, you tin can perform more than comparisons for other row's data.

Method iii: Highlight matching rows

Information technology is a bit different method than the above ii 1. Using this method, we highlight the row which has the aforementioned information in both columns. Conditional formatting is used for this method. It does not return any resultant string; it highlights the matches data row instead of returning Truthful or FALSE.

Annotation: You have to do comparison one-by-one for each row. Y'all cannot check multiple rows data at once.

It is a useful method for comparing multiple rows in one go, by highlighting the matched data rather than getting the result in the third column. It saves the fourth dimension of users by comparing and showing the result at one time.

Upshot

The row will get highlighted if found the verbal match in both column'due south cells. Otherwise, the row will not be highlighted if match is not found.

Example

Permit'due south accept an example to step past stride comparison.

Footstep one: Open up your targeted Excel file and select the row y'all desire to check whether the values are the aforementioned or not.

Step 2: In the Home tab, you will see an choice named Conditional Formatting nether the Mode group. Click on this dropdown listing.

Step 3: From the dropdown list, click on the New Rule that will open a console to ready new formatting rules.

Step 4: Here, click on the Employ a formula to decide which cell to format from the rule type list.

Pace v: Here, inside the formula field, specify the cells you want to compare in the following format, e.one thousand., =$A2=$B2. This formula is for the 2nd row.

Step 6: In the end, specify the format for matched cell by clicking on the Format push and meet the preview of it inside the Preview box.

Step 7: Here, navigate to the Fill tab and cull a background color to highlight the matches and click the OK push.

Step 8: See the preview within the preview section and see how row will look like if match is found. After finalizing all the things, click the OK button to save the changes.

Pace 9: You will see that the selected row has not been highlighted because the A2 and B2 cells exercise not contain the aforementioned values.

Step x: By following the same steps, compare the next row data. See that everything is set upwards successfully. At present, click the OK push button to go the outcome.

Step eleven: Look at the following screenshot, that the three^rd row has been highlighted considering it gets the same data in both columns.

Step 12: Similarly, nosotros will check for all rows one-by-by.

Later on comparison all the rows in both columns, see our Excel worksheet; that row having the same information in both columns has been highlighted, and remains are as they are.

It will highlight all the matching data row in the format you have chosen previously.

Compare two columns and highlight the matches

This method of comparison is likewise washed using conditional formatting. It is a useful method to compare i column data with another column values and gets the matches highlighted after comparison.

It is used to find the matches non only in the same row of the selected columns but in the entire selected dataset. Using this method, nosotros highlight the cells data having at to the lowest degree ii or more than 2 matches. For example -

Often, there are matches bachelor but not in the same row. In this scenario, these methods of comparing assistance the users to find the matches. Something as is showing in the below screenshot:

Features

All values of both columns are compared in one become.
It saves the fourth dimension of users by comparison the entire cavalcade with some other cavalcade at in one case.
You tin either highlight all the unique or duplicate values, whichever yous want.

In that location are 2 ways to compare columns and prove their issue, i.east.,

Method 1: Compare columns and highlight matches

Method 2: Compare columns and highlights mismatches

Compare columns and highlight matches

This method of comparison is not same equally the row-by-row comparing. It works differently. In this method, nosotros use duplicate functionality in conditional formatting. It means that the duplicate values will go highlighted after a successful comparison.

Following are the steps for comparing two columns and highlighting the matches:

Steps for cavalcade comparison

Step ane: Open your targeted Excel file. This time select all data using the select all Ctrl+A shortcut fundamental in which you want to find duplicate values.

You can also select only two columns.

Pace 2: In the Home tab, you will see an pick of Conditional Formatting under the Style grouping. Click on this dropdown list.

Step iii: In the dropdown listing, hover the mouse to the Highlight Cell Rules option to expand more options and then click on the Duplicate Values.

Footstep 4: Now, in the Duplicate Values dialogue box, ensure the Duplicate is selected in the given dropdown list. If non, select Duplicate hither.

Footstep five: Next, specify the formatting color for highlighting the duplicate values nowadays in the selected dataset and click the OK button.

Y'all can also customize the color and font other than the given ones. This means that you lot can set up your format to highlight the indistinguishable values from the Custom Format.

Step 6: See the below screenshot that all the indistinguishable values have been highlighted with red color. This comparison is for the entire selected data instead of row-by-row.

Compare columns and highlight mismatches

This method is nigh aforementioned every bit the in a higher place one, simply the difference is that - in this method, nosotros highlight mismatches instead of matches. Thus, it works the same but resultant differently.

In this method, we use duplicate functionality in provisional formatting and highlight the values which are not duplicates in selected columns. It's steps are well-nigh the aforementioned as the above one. Follow the steps below to see how could it be washed:

Steps for column comparison

In this example, nosotros volition use the same excel worksheet with the same content in information technology. Simply this time, we will highlight unique values present in this Excel worksheet.

Stride 1: Open your targeted Excel file and select all data of information technology using the select all Ctrl+A shortcut key. You lot can too select only two columns.

Step ii: In the Domicile tab, you will meet an option of Conditional Formatting under the Style group. Click on this dropdown listing.

Pace 3: In the dropdown list, hover the mouse to the Highlight Cell Rules selection to aggrandize more options and then click on the Duplicate Values.

Step 4: This fourth dimension, choose Unique from the dropdown to highlight the unique values instead of indistinguishable ones.

Pace 5: Choose a format to highlight the unique values nowadays in the selected dataset.

Stride 6: Setup the format appropriately and then click on the OK button hither.

Pace seven: Yous tin can run into that all the values, which are unique (simply one in the selected dataset), have been highlighted. All values are highlighted in one go.