Searching Duplicate Data in Different Column Fields

Problem

Sometimes you may need to find the same data in 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 as Lost User, otherwise, they are considered as Normal User.

Solution

Use the EXACT function to check if two sets of text are the same.

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 example data table User Login and Registration Information (default to selecting all fields).

2. Add Formula Column, name the new column User Type, and enter the formula IF(EXACT(Registration Time, Last Access Time), "Lost User", "Normal User").

Formula description:

The result of the operation is shown in the figure below.

The EXACT function is case-sensitive.