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!

    Không có nhận xét nào