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.
For example, I want to compare between 2 Sheets Stocker and Accountant Please !
#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.
Sheet1(Stocker) you click on the cell where you want to extract the value, here is the column
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")
- first that right
- 0 is wrong
- "No"; "Yes" is the result returned after the comparison is made.
+ Step 2: Then you open the Compare Sheet (
Kế toán) up => and scan the selection.
+ 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
F4to fix the column rows to be compared.
Complete the correct formula with
1, and equal to
0=> then enter the return value of the IF function as
Applying to the example we get:
= IF (ISNA (VLOOKUP (B2; "Accounting"! $ B $ 2: $ B10; 1; 0)) "No"; "Yes")
+ Step 4: Finally, you fill the formula cell down the list is done.
To get the result of comparison between the two sheets as shown below.
Alternatively, you can apply the same formula to compare data from two different Excel files. To return the required value.
#2. How to filter data by value in ExcelAfter creating the formula and comparing the values, select the title area, and open the tab
Data=> then select
Then click the drop down triangle button in the title bar
So sánh=> and deselect the value
Không=> and press
To be the result of values
In contrast, with non-duplicate values, you do the same and uncheck
To display Duplicate values on Sheet (
Thủ kho) and Sheet (
# 3. EpilogueOkay, 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.
CTV: Luong Trung - Blogchiasekienthuc.com