Overview
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.
Procedures
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:
| Formula | Description |
EXACT(Registration Time, Last Access Time) | If two times are exactly the same, the system will return TRUE, otherwise return FALSE. |
IF(EXACT(Registration Time,Last Access Time),"Lost User","Normal User") | You need to use the IF function together, the system will return Lost User if TRUE, otherwise return Normal User. |
The result of the operation is shown in the figure below.
Notes
The EXACT function is case-sensitive.
Previous：Field Cleaning
