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 !
#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:
+ 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 F4 to 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 Không and Có => press Enter to execute.
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 Excel
After creating the formula and comparing the values, select the title area, and open the tab Data => then select Filter.
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.
To be the result of values Có.
In contrast, with non-duplicate values, you do the same and uncheck Có.
To display Duplicate values on Sheet (Thủ kho) and Sheet (Kế toán).
# 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