How to Compare Two Lists or Datasets in Excel - GeeksforGeeks (2024)

Are you struggling to find differences or similarities between two lists or datasets in Excel? Whether you’re managing large data sets for business analysis, academic research, or personal projects, efficiently comparing data is crucial. This article will help you learn the most effective methods to compare two lists or datasets in Excel. From using simple formulas like VLOOKUP and MATCH to advanced techniques with conditional formatting and Power Query, we cover everything you need to streamline your data comparison tasks. Discover how Excel can help you identify discrepancies, track changes, and ensure data integrity with ease.

How to Compare Two Lists or Datasets in Excel

There are a few methods to compare datasets and two lists in MS Excel are given below for your reference. Read the full article to know more about the same,

• Method 1: Compare Two Lists Using Equal Sign Operator.
• Method 2: Match Data by Using Row Difference Technique.
• Method 3: Match Row Difference by Using the IF Condition.
• Method 4: Match Data Even If There is a Row Difference.
• Method 5: Highlight All the Matching Data utilizing Conditional Formatting.

How to Compare Two Lists Using Equal Sign Operator

We should follow the below moves toward looking at the two records. read below to know about the Comparing two lists using equal sign operator,

Step 1: Insert a new Column

Immediately after the two columns, we must insert a new column in the next column.

Step 2: Put the formula in Cell C2

We need place the equation in cell C2 as =A2=B2.

Step 3: Check the outcome as “TRUE” or “FALSE”

This equation tests whether cell A2 esteem is equivalent to cell B2. On the off chance that both the cell values are coordinated, we will come by the outcome as “TRUE” or “FALSE.”

Step 4: Drag the formula to cell C9

We will drag the recipe to cell C9 to decide on different qualities and outcomes.

How to Match Data by Using Row Difference Technique

You probably won’t have utilized the “Column Difference” strategy in your working environment. Be that as it may, today, we will tell you the best way to utilize this procedure to match information column by line.

Step 1: Choose the whole data and select it

To feature non-matching cells column by line, we should choose the whole information first.

Step 2: Press the ‘F5’ key to open the ‘Go to special’ box then press the “Special” tab

Presently, we should press the successful easy route key “F5” to open the “Go to Special” instrument. Presently in the “Go-To” window, press the “Special” tab.

Step 3: Select ‘Row difference’ and click OK

In the following window, we should go to the “Go To Special” and pick the “Row differences” choice. Then, at that point, click on “OK.”

Step 4: Check the Outcome

We will obtain the accompanying outcome.

As we can find in the above window, it has chosen the cells in any place there is a line distinction. Hence, we should fill in a variety to feature the line contrast values.

How to Match Row Difference by Using IF Condition

How might we leave out the IF condition when we have any desire to match information column by line? In the primary model, we have by the same token “TRUE” or “FALSE.” But consider the possibility that we want an alternate outcome rather than the default consequences of by the same token “TRUE or FALSE.” Assume we want an outcome as “Coordinating” if there is no column distinction and the outcome ought to be “Not Matching” on the off chance that there is a line contrast.

• To begin with, we should open the IF condition in cell C2. Apply the logical test as A2=B2.
• Note: Logical Test=>In Excel, toward the early phases of learning, it is a seriously troublesome errand to grasp the idea of consistent tests. Be that as it may, when you ace this, it will be significant expertise to your CV. As a general rule, in succession, we utilize a legitimate test to match various models also, show up at the ideal arrangement. To succeed, we have numerous consistent equations. Go to the Formula tab and snap on the Logical Function gathering to see every one of the sensible recipes.

We want to apply the recipe to come by the outcome. We should drag the recipe to cell C9 to decide on different qualities.

How to Match Data Even If There is a Row Difference

The matching information on the column distinctions system may not work continually; the value may be in various cells too. So we truly need to include different advances in these circ*mstances. As of now, look at the data below. So let us utilize our capability VLOOKUP(Note: The VLOOKUP succeed capability looks for a specific worth and returns a related match given an exceptional identifier. An exceptional identifier is extraordinarily connected with every one of the records of the information base.)

Along these lines, if the data matches, we get the Name; regardless, we get the bumble regarded as #N/A. Showing mistake values doesn’t look great. So rather than showing the mistake, let us supplant them with “Not Available.” For this, utilization of the IFERROR( Note: The IFERROR capacity in Excel truly investigates a recipe for mix-ups and returns a foreordained worth rather than the goof. This returned worth can be a text, an empty string, a reasonable worth, a number, etc. The bumbles are dealt with by the capacity to consolidate “#N/A,” “#DIV/0!,” “#NAME, and so forth) capabilities in Excel.

How to Highlight All the Matching Data utilizing Conditional Formatting

On the off chance that you hate Excel recipes, relax. We can in any case match information without the equation. For instance, utilizing straightforward restrictive formatting (Note: Conditional designing in Excel helps design the scope of cells given the satisfaction of at least one circ*mstance. The design to be applied can either be chosen from the different choices given in Excel or made without any preparation by the client. Contingent designing chips away at the standard “if so, then that ought to be finished.”) In Excel, we can feature every one of the matching information of two records.

Step 1: Go to the home tab and select ‘Conditional Formatting’ then choose ‘Highlight Cell Rules’> ‘Duplicate values’

We ought to first pick the data. Presently, we should go to “Home” and “Conditional Formatting” and pick “Highlight Cell Rules” > “Duplicate Values.” Subsequently, we can see the “Duplicate Cell Values” arranging window.

Step 2: Select the primary arranging tone and click OK

We can pick the different design tones starting from the drop list in Excel. Select the primary arranging tone and press the “OK” button. This will feature every one of the matching information from the two records.

Step 3: Choose the ‘Unique’ option from the dropdown in the Duplicate Values tab

Simply for the situation, rather than featuring every one of the matching information, to feature not matching information, then, at that point, we can go to the “Duplicate Values” window and pick the choice “Unique.” Subsequently, it will feature every one of the non-matching qualities, as displayed beneath.

Conclusion

Comparing two lists or datasets in Excel is an essential skill for data analysis, ensuring data accuracy, and improving efficiency in your workflow. By learning powerful Excel functions like VLOOKUand P, MATCH, and advanced tools such as Conditional Formatting and Power Query, you can easily identify matches, and discrepancies, and ensure data integrity. Whether you’re handling large Excel databases or comparing data files, these methods provide reliable solutions for your data comparison needs. Enhance your data management capabilities today and streamline your analytical processes with these expert Excel techniques.

Explore more about Excel data comparison and stay ahead with our comprehensive guides. Optimize your Excel skills for business intelligence, data validation, and more to boost productivity and achieve precise results.

FAQs – How to Compare Two Lists of Data in Excel

How do I compare two lists in Excel to find matches?

To compare two lists in Excel to find matches, you can use the VLOOKUP function. For example, if you have List A in Column A and List B in Column B, you can use the formula =VLOOKUP(A1, B:B, 1, FALSE) in Column C to check for matches. If a match is found, the value from List A will be displayed; otherwise, you’ll get an error.

How to check if two sets of data match in Excel?

To check if two sets of data match, you can use the IF and COUNTIF functions together. For instance, use =IF(COUNTIF(B:B, A1)>0, “Match”, “No Match”) to see if each value in List A is present in List B. This will return “Match” or “No Match” accordingly.

How do I compare two Excel databases?

To compare two Excel databases, you can utilize Conditional Formatting. Select the data range in the first database, go to Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula like =A1<>B1. This will highlight differences between the two databases.

How do I compare two data files in Excel?

To compare two data files, open both files and use Excel’s View Side by Side feature. Go to the View tab and click “View Side by Side.” You can also use Power Query to load data from both files and merge or compare them using advanced querying capabilities.