Unique ID on groups of records

Hi - I am trying to create a unique ID on a group of records. The pattern to the group is separated by empty rows in either Column A or E. Column A always has 5 records but column E is unpredictable. Please see attached input and expected output. Thanks!

Unique ID help.xlsx

Transformation

Accepted answers

Thableaus

@cireost

Ok, this is a solution considering Column A will always be grouped by 5 Non-Null records.

- Created a Record ID to list a sequence of 1 to 5, considering Nulls to be taking the value from the row before (all based on Column A)

- Created Unique ID considering that an unique ID is added 1 everytime the previous Record ID is 5.

Workflow appended.

Cheers,

5RecordsColumnA.yxmd

Thableaus

@cireost

Try it:

IF REGEX_Match([General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure], "\d+\.\s+.*") THEN REGEX_Replace([General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure], "\d+\.\s+(.*)", "$1") ELSE [Row-1:Unique ID] ENDIF

Pattern followed: Number, "." (dot), Spaces (one or more) then Text

Everything that lies within Text will be used as Unique ID.

Cheers,

All comments

Thableaus

Hi @cireost

Wouldn't it be more trustable if we used the first field as a unique identifier - everytime there's a number followed by a dot (Ex: 1. XYZ Tenant), there's a new unique ID.

So I'd use RegEX to identify these patterns and Multi-Row formula tool to replicate it.

Cheers,

Solution1.yxmd

cireost

@Thableaus Unfortunately the first field doesn't always have a number and therefore, I would like to create my own unique identifier. would you be able to simply modify the workflow for that?

Thableaus

@cireost

Ok, this is a solution considering Column A will always be grouped by 5 Non-Null records.

- Created a Record ID to list a sequence of 1 to 5, considering Nulls to be taking the value from the row before (all based on Column A)

- Created Unique ID considering that an unique ID is added 1 everytime the previous Record ID is 5.

Workflow appended.

Cheers,

5RecordsColumnA.yxmd

cireost

@Thableaus hi - this is resolved but wanted to tack on a quick question. In solution 1 that you attached, how do i update the regex formula so that instead of Unique ID being a number, it is the Tenant name being pulled instead.

so in Unique ID column, instead of 1, 2 or 3 it will show XYZ Tenant, LOL Tenant, Random Tenant.

Thanks!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703