how to remove duplicate values in exc excel for mac

Remove Duplicate Values In Exc Excel Upgrade When It

When the MATCH function looks for 0s it will now return the 2nd item in the list (which is the next unique item).

These new formulas are being rolled out to Office 365 subscribers over the next few months.

Remove Duplicate Values In Exc Excel Upgrade When It

However, not everybody has the subscription and will upgrade when it is sensible for their business, often combining it with a hardware refresh.

Therefore, it could be 6 or more years before enough users have the new functionality to use it safely and ensure compatibility.

Counting cells which meet specific criteria may not seem particularly useful, but when combined with other functions, and boolean (truefalse) logic, it creates new capabilities you never thought possible.

Well start with some basic scenarios and slowly layer on the complexity until we achieve some advanced formula magic.

In the screenshot below there is a list of students from St Johns school (Cells A2 A7) and a list of students who attended a specific exam (Cells B2-B6).

We have been asked to identify the number of individuals who are on both lists (i.e., how many from St Johns school attended the exam).

In this case, we have not provided a single criteria, but have used a range of cells.

As we have not used any logic operators, such as greater than ( ), Excel by default, applies equals ( ) as its logic operator.

This formula will therefore, compare each cell in the Range B2 B6 to identify if it is equal to any cells in the Range A2 A7.

Calculation 1 compares Cell B2 to Cells A2:A7, calculation 2 compares Cell B3 to Cells A2:A7, etc etc.

By wrapping this within the SUM function it will add the list of 1s and 0s, which is the number of items which appear on both lists.

In the way we are using it, there is no multiplication occurring within the SUMPRODUCT function, so it will just sum the result.

I have expanded the example to include duplicates for two Exam Attendees (see screenshot below), Lucy King and Billy Thompson now appear twice in column B.

The result of the previous calculation is shown in cells E2 and E4.

However, when there are duplicates the first range in the function must be the range containing the duplicates and the second range containing the unique values.

Please note this change within the examples for the remainder of this post.

These will need to be converted into values of 1 for TRUE or 0 for FALSE to enable the SUM function to work.

For this, the logic statement changes again to show only the items where the count is equal to zero.

Column A contains a list of students who attended an exam, but it contains duplicates.

We will use this data to create a unique list of value, as shown in Column B.

The result of the MATCH function is 1 because the first 0 is in the first position.

These occur each time the values in Cells B2 B3 appear in Cells A2 A16.

How To Remove Duplicate Values In Exc Excel For Mac