Filter and compare data between files or sheets in Excel - Knowledge sharing blog

Note: Please read the article carefully before proceeding! If in the process of using you encounter any errors, such as broken download links, slow loading blog, or unable to access a certain page on the blog ... then please inform me here. Thanks!

Yes, in the accounting profession, comparing data on Excel files or sheets in Excel, to set up a total report is one of the quite essential tasks.

In previous posts shared on the Blog, I have also guided you how to filter, highlight, and delete duplicate data, as well as summing up those values ​​in Excel files already.

And to complement this topic, today I will continue to share with you a little trick in comparing, and filtering data from many different Sheets or Excel files. Catering to the needs of creating spreadsheets, your reports are faster and more professional.

Read more:

For example, I want to compare between 2 Sheets Stocker and Accountant Please !

Excel-locator-excel-format-data-(-)

#first. How to compare data on worksheets of different Excel files

+ Step 1: First, open the Excel file you want to make a comparison to.

In Sheet1 (Stocker) you click on the cell where you want to extract the value, here is the column So Sánh there.

Then now we will use a combination of functions IF (conditional function), ISNA (function that distinguishes true and false values, VLOOKUP (data search function) => and then click on the first cell in the Sheet to compare.

The formula would be:

= IF(ISNA(VLOOKUP(The value you want to compare;Sheet to compare '!region to be compared;first;0)) "No"; "Yes")

Inside:

  • first that right
  • 0 is wrong
  • "No"; "Yes" is the result returned after the comparison is made.

Start making:

Excel-locator-excel-format-data-(-)

+ Step 2: Then you open the Compare Sheet (Kế toán) up => and scan the selection.

excel-locale-excel-format-data-in-excel (4)

+ Step 3: Now go back to Sheet (Thủ kho) and adjust the position of Sheet as the comparison Sheet name => and click between the letters and numbers => and press the key F4 to fix the column rows to be compared.

Excel-locator-excel-format-data-(-)

Complete the correct formula with 1, and equal to 0 => then enter the return value of the IF function as Không and => press Enter to execute.

Applying to the example we get:

= IF (ISNA (VLOOKUP (B2; "Accounting"! $ B $ 2: $ B10; 1; 0)) "No"; "Yes")

Excel-locator-excel-format-(-6)

+ Step 4: Finally, you fill the formula cell down the list is done.

excel-locale-excel-format-data-in-excel (7)

To get the result of comparison between the two sheets as shown below.

Excel-locator-excel-format-(-8)

Alternatively, you can apply the same formula to compare data from two different Excel files. To return the required value.

excel-locale-excel-format-data-in-excel (14)

#2. How to filter data by value in Excel

After creating the formula and comparing the values, select the title area, and open the tab Data => then select Filter.

excel-locale-excel-format-data-in-excel (9)

Then click the drop down triangle button in the title bar So sánh => and deselect the value Không => and press OK to establish.

excel-locale-excel-format-data-in-excel (10)

To be the result of values .

Excel-locator-excel-format-data-(-)

In contrast, with non-duplicate values, you do the same and uncheck .

Excel-locator-excel-format-data-(-)

To display Duplicate values ​​on Sheet (Thủ kho) and Sheet (Kế toán).

excel-locale-excel-format-in-excel-format (13)

# 3. Epilogue

Okay, so I just gave you very detailed instructions on how to compare, filter duplicated, or non-duplicated data on different sheets or Excel files.

This function will help you a lot in the process or training and working, when you often have to gather and compare conditional data on spreadsheets, helping you get results faster and more accurately. .

Here, the instructions on how to filter, Compare duplicate data between sheets, or multiple Excel files I also would like to pause. Hope this article will be helpful to you.

Good luck.

CTV: Luong Trung - Blogchiasekienthuc.com

Note: Was this article helpful to you? Do not forget to rate the article, like and share it with your friends and relatives!