Searching for Duplicate Data Across Different Column Fields

You can use the IF function to find duplicate data and assign values. You can also use the EXACT function to determine whether values are duplicated and then use the IF function to assign new values to strings returned by the EXACT function.

Problem

Sometimes you may need to find the same data across different column fields (duplicate values appearing in different columns) when processing and cleaning data.

For example, to find out the lost users on a website, you can assume that if Registration Time and Last Access Time are the same, the users are considered Lost User. Otherwise, they are considered Normal User, as shown in the following figure.

Solution

You can use the EXACT function to check if two sets of text are the same.

You can use the IF function to return the desired value.

Example data: User Login and Registration Information.xlsx

1. Create an analysis subject and upload the downloaded table User Login and Registration Information (All fields are selected by default.), as shown in the following figure.

2. Click Formula Column, name the new column User Type, and enter the formula IF(EXACT(Registration Time, Last Access Time), "Lost User", "Normal User"), as shown in the following figure.

The following table describes the formula.

The following figure shows the result of the operation.

The EXACT function is case-sensitive.