Buzz
To compare data on two different sheets within the same Excel file, there are various methods available. For instance, utilizing VLOOKUP or using the COUNTIF function combined with Conditional Formatting to create distinguishing or non-distinguishing comparison effects on data across different sheets. However, in this particular article by TechTips.com, we will use the COUNTIF and Conditional Formatting method to compare data.
Firstly, we have a data column as follows on Sheet 1. It represents a list of items stocked on day 1.
And on the second sheet, we have a list of items stocked for day 2. Currently, we can utilize the COUNTIF function to calculate the number of times items repeat compared to the first day.
Before diving into writing formulas, you need to define the data range to compare on sheet 1 and assign it a name for distinction.
Highlight the data range you want to select, then right-click on the top-left corner of the sheet, and input a name for that data range. The name you input must be without accents or spaces, otherwise Excel won't be able to read it.
Alternatively, you can name the range by going to the 'Formulas' ribbon on the toolbar and selecting 'Define Name' under 'Defined Names'.
Enter the name for the range in the 'Name' box, then click 'OK' to confirm the naming.
Now, the data range on Sheet 1 has been given a specific name, allowing you to refer to that data range in a function formula without needing to specify cell addresses anymore. This method proves convenient for using data across multiple sheets.
The COUNTIF formula we need to use here counts the number of occurrences between two data columns:
=COUNTIF(data array to count, criteria for counting)
And with the example in the image, our function formula is:
=COUNTIF(inventory1;A2)
With this formula, you can count the number of 'Student Notebooks' occurrences in the array of goods entering the warehouse on the first day.
Next, simply copy that formula down by clicking on the bottom right corner of the existing formula cell and dragging it down.
As you'll notice, results showing 0 indicate that this item has never been stocked on the first day.
The steps above may provide us with specific figures of duplicate entries during stock intake, but they don't immediately reflect those duplicates within the data column. We can address this using the Conditional Formatting feature, creating a distinct format so that at a glance, we can identify which items are non-duplicates when compared to the stock intake data from day 1.
Select the data array to compare, then navigate to the Home ribbon on the toolbar and choose Conditional Formatting under the Styles section of this ribbon.
Click on the New Rule row.
Then, the New Formatting Rule dialog box will appear; select the last row from the list of rule types, which is Use a formula to determine which cells to format.
In the 'Format values where this formula is true' box, enter the following formula:
=B2=0
To explain a bit further. Because we want to color cells that don't have duplicates, meaning in that data row, the result in the column 'Duplicates with day 1' that we just filled using the COUNTIF formula is 0.
That's why when we set the condition for this box, we need to specify the condition to find cells equal to 0 in that 'Duplicates with day 1' column.
Keep B2 unchanged as the non-fixed parameter cell so it can adapt with rows in the column.
Then, remember to adjust the formatting for the conditionally formatted cell by going to Format.
Modify the formatting of the conditionally formatted cell in different tabs of the Format Cells dialog box, with Font to change font style, Border to alter cell borders, and Fill to apply background color to the data cell. Then press OK to confirm.
After reviewing a series of settings, click OK to commence formatting changes for cells that meet the set condition.
As you'll see, cells matching the set condition, indicating items not matching the first day's warehouse intake, will be highlighted with a green color overlay.
If you wish to directly change the formatting for cells with non-matching data, you can directly utilize the COUNTIF function formula in the Format values where this formula is true box.
Therefore, the formula for the Format values where this formula is true box is:
=COUNTIF(nhapkho1;A2)=0
And the obtained result is:
Note: If you change the formula from =0 to >0, cells with duplicate data compared to the first day will be formatted differently. This part, Mytour will leave for you to practice.
The article on comparing data on 2 different Sheets from Mytour ends here, thank you for following our article. We hope you can understand the knowledge we want to convey and successfully implement it for your data.
