How to compare two lists of data in Excel

How to compare two lists of data in Excel
How to compare two lists of data in Excel
Anonim

With Microsoft Excel and its formulas, it is possible to follow, analyze, compare various data. If you have, for example, a document that represents the benchmark in your field of activity, you will be able to compare other documents to it by using the formula MATCH. Another interesting aspect of Excel is being able to cross-reference the information of two files and thus identify the differences to allow an analysis of the latter. To perform this operation, you will need to use the formula NB.SI.

Steps

Method 1 of 2: Compare data with the MATCH function

Match Data in Excel Step 1

Step 1. Have your data on one spreadsheet

You can have two lists of data that you want to compare, but they are on different sheets or even in different Excel files. To make it easier to compare the data, copy the data and paste it into a single spreadsheet. This will make the comparison easier and prevent you from making mistakes.

Match Data in Excel Step 2

Step 2. Find a common identifier

To be able to perform a comparison between two lists of data, you must have a common element in each of the two lists. On the other hand, if you do not have a common reference for each of the lists, you will have to create one. It will therefore be necessary to add a new column in which you will have to display a piece of data which will be unique for a row in one list, but common to a row in the other list.

  • For example, you have a monthly file on the orders of your products in all the stores in France where they are sold. To compare the data between each month and know the most interesting months of your business, you will need a common data which will correspond to the same row in each of the lists. The element that will create the correspondence could be the product label, its referencing code, etc. Note that for this to work, you will need tables with identical formatting, i.e. the same number of columns, headers, headings, etc.
  • It happens that two lists have a common datum that would allow them to be compared, but that would not meet your need. For example, you can compare the sale of an item between two months or two years, but the result will be for all of France and you want a specific department. It is possible to overcome this drawback by using the formula CONCATENATE. You combine, for example, the reference code of the article that is in the cell A2 with the date in B2 and the department number which is noted in the cell C2. This gives you the formula = CONCATENATE (A2; B2; C2) which you can place at the end of the line, for example by D2. If for the same combination of information, you only want to have the year or the month, you will just have to include the appropriate formula with B2. With only the year, the formula becomes = CONCATENATE (A2; YEAR (B2); C2) and with only the month, the formula is written = CONCATENATE (A2; MONTH (B2); C2).
  • When you have chosen how you are going to create your identifier and you have written the formula for the first row of your table, all you have to do is copy it for all the other rows. Select the cell that contains the formula, then click on the fill handle that is at the bottom right of your cell, stretch to the last row of your table. Repeat with your second table. You now have two tables with a specific identifier that will allow you to compare your data.
Match Data in Excel Step 3

Step 3. Standardize your writing style

This is an important point, if you want to compare all your data. You should preferably use the same names for your data, for example, “assoc. "And" association "are the same for you, but for Excel they are two different data.

  • If you want, the formula LEFT can help you. It allows, depending on a selected cell, to give as a result in another cell the number of characters from the left that you have defined. However, this complicates your picture and you are not immune to making mistakes. So, instead, try to standardize the way you enter data in your tables. Don't complicate your picture, just be rigorous. Keep it simple and you will follow up quickly and efficiently.
  • In your table, if you have a column with the postal codes, you can with the formula LEFT identify the departments and thus perform a geographically targeted comparison. For a column with sums, you can use the formula ROUND to round the values ​​and thus compare them more easily, because Excel will not do it, if you do not tell it to do it.
  • If you have multiple spaces between data, you can correct by using the formula DELETION which will remove superfluous spaces.
Match Data in Excel Step 4

Step 4. Insert a new column

Depending on your needs, you have added a column to each of your tables to insert a specific identifier. Now, you will have to add a new column, to your two tables, to enter the formula that will allow you to know the result of the comparison of your two lists of data.

For the column header, you can write "Absent", for example

Match Data in Excel Step 5

Step 5. Type the formula for the data comparison

Use the formula MATCH to compare the information. However, it may happen that the formula does not find an answer and gives the result "# N / A" instead of "FALSE" or "TRUE" and therefore you may ignore these lines. To overcome this point, you will include the result of your comparison in the formula ESTNA which will write "TRUE" when the result of the formula MATCH will be "# N / A" and it will cause you to remember these lines.

  • In your spreadsheet, you have two tables. The first painting, for example, is on the beach A1: E21 and your second painting on the beach G1: K21. The first line is used for headers and the other twenty lines are for data. In the first table, column "D" is the one entitled "Identifier" and column "E" is called "Absent", it is in this last column that you will enter the following formula = ESTNA (EQUIV (D2; $ J $ 2: $ J $ 21; FALSE)). This formula searches the data range $ J $ 2: $ J $ 21 (data in the "Identifier" column of the second table) if there is a data corresponding to that of the cell D2. If the search is successful, the result will be "FALSE" and if the search does not find a match or cells are empty, the result will be "TRUE" in the cell E2.
  • Now that you have written the formula in the first cell provided in column "E", you can copy the formula to the other cells that follow. Select your cell E2, then click the fill handle that is in the lower right corner of your cell. Stretch your selection to the last cell of your table column. You will be able to see in the different formulas that what is surrounded by the sign "$" has not changed, only the first data changes.
  • Copy the formula to the cell K2 which is the first cell to compare the data in the second table with the data in the first table. Then change the data in the formula. Replace D2 through D2 the first identification value of the second array and $ J $ 2: $ J $ 21 through $ D $ 2: $ D $ 21 which represents the identification data range of the first table. Your formula therefore becomes = ESTNA (MATCH (J2; $ D $ 2: $ D $ 21; FALSE)). Then copy the formula below into the cells of the column.
Match Data in Excel Step 6

Step 6. Organize your data

If you have very long lists, it will quickly become difficult to visualize the rows you want to see. The ideal is to sort your data according to a criterion starting from the column “Absent” which compares the data of the two lists. To begin with, it will be interesting to change the formulas into data so as not to have problems with the various calculations, then to sort.

  • Select all cells in a data range from one of your tables.
  • In the 2003 version of Excel, click in the menu bar on Editing, then on To copy. For the 2007 and 2010 versions of Excel, select the tab Home in the ribbon, then in the section Clipboard, tap the icon To copy.
  • For the collage, you will have to click on Special bonding after opening the drop-down menu of Editing in the 2003 version of Excel. For the 2007 and 2010 versions, press ▼ of the icon To stick on tab Home, then on Special bonding.
  • In the dialog box Special bonding, select Values, then on the button OK to validate and close the dialog box.
  • To sort, go to the Excel 2003 menu bar and press Data, then on to sourt out. For Excel 2007 and 2010, click on the tab Data ribbon and then, on the icon to sourt out.
  • Be sure that Yes for option My data page has one row of titles is selected in the 2003 version of Excel. For the 2007 and 2010 versions, the option My data has headers must be checked. For option sort by (the 3 versions of Excel), choose Absent (header of the column that compares the data). Then click on OK.
  • Now that you've set up your first board, do the same for the second.
Match Data in Excel Step 7

Step 7. Do a visual check

Check the different rows yourself to make sure you don't have a small exception that would be present in one of your lists. Be aware that Excel will look for an exact match between the data, except of course, if you ask it to look for an approximate value. It is important to do a visual check, as you may have made a typo.

Method 2 of 2: Use the NB.SI formula to see the differences

Match Data in Excel Step 8

Step 1. Import your data

Make a copy of your lists and paste them into one worksheet.

Match Data in Excel Step 9

Step 2. Define your approach

You can highlight data that is the same on both lists or identify data in one list that is different from the other list. You will be able, for example, to distinguish the data of the first list which does not have an equivalent in the second list. Another approach, make appear the data which is identical in the two lists. To present an approach correctly, we will start from the fact that your first list starts at the cell B2 and ends at cell B21. The second list will be found between the cells E2 and E21.

Match Data in Excel Step 10

Step 3. Select the data list

Click in the first cell of the list for which you want to display the unique elements or those which are common to the other list. To bring out similar data in both lists, it will be interesting if you apply the formula to each of the two lists.

Match Data in Excel Step 11

Step 4. Use the formula and a conditional format

In Excel 2003, go to the menu bar and click on Format, then on ' Conditional formatting. For the 2007 and 2010 versions, press the tab Home in the ribbon and select the icon Conditional formatting, then New rules. A dialog box opens. For the 2003 version, change The cell value is through The formula is. For the other two versions of Excel, select Use a formula to determine for which cells the format will be applied. Finally, enter the formula in the dedicated space.

  • To be able to display the elements which are unique from the first list, type the formula = NB.SI ($ E $ 2: $ E $ 21; B2). This formula controls in the range $ E $ 2: $ E $ 21 which is the second list, if there is a value that is equal to the one in B2 which is a data item from the first list. Note the "$" sign is there so that when copying over the other cells, this data range remains unchanged. Conversely, B2 will evolve and pass B3 by dragging the cell B2 selected to the cell below. Do not forget to correct once the copy is finished by checking Copy only the formatting in the small icon that will appear.
  • To perform the same verification of the data of the second list according to the elements contained in the first list. You will need to apply the same formula, but with the appropriate elements. The formula will be = NB.SI ($ B $ 2: $ B $ 21; E2). The formula will search in $ B $ 2: $ B $ 21 which is the data range of the first list, if there is a data which is equal to that which is in E2 which is part of the second list.
  • To know the data common between the two lists, it is very simple, since if a data is not unique to a list that means that it is common to the two lists. So you don't need to add or modify anything, you just have to use the function Filter. Select your column headers which contain data, then click on Filter in the drop-down menu of ' Data which is in the menu bar of Excel 2003 and in the tab Data of the ribbon for the 2007 and 2010 versions. Then you can filter on the data that is unique or common.
  • Depending on the formula used, you will have to choose the type of format and here the color that will appear in the cell, if the data is common to both lists. Note that using the formula IF, in another column, would be just as simple and especially useful in the 2003 version, because you could make a value appear and it would be easier to filter (Excel 2003 does not filter on colors).

Advice

  • Depending on the type of lists you want to compare, you can in the formula NB.SI entered in the window of Conditional formatting looking for a value and not the contents of a cell, then filter on that value found in some rows.
  • If you want, you can name your lists, for example "List1" and "List2" for more convenience and thus enter the names instead of the data ranges when you have to write your formulas to compare the data of your two lists.

Popular by topic