Filter and compare data between files or sheets in Excel

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!

0 Comments

(code-box)