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!
Résolu ! Accéder à la solution.
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,
@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?
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,
@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!
Hi @cireost
Try this in your multi-row formula:
IF REGEX_Match([General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure], ".*\bTenant\b.*") THEN [Row-1:Unique ID] + 1 ELSE [Row-1:Unique ID] ENDIF
Basically, if Tenant is found anywhere in your text (it has to be exactly Tenant, it can't be mispelled or anything) the ID is added +1 to the previous Unique ID, else it returns the same previous Unique ID.
Cheers,
hmm it is still showing up as a number in the unique ID instead of tenant name. see workflow attached with the new formula. also, in the real data set, "Tenant" is not always shown. Is there another way to grab just the text instead? again, looking for the column to read the tenant names, rather than the numbers.
So Unique ID column should look like this:
Unique ID | General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure | General Tenant Information Initial Area Building Share % | General Tenant Information Lease Period Lease Status Market Leasing Lease Type | Rent Details Rate Per Year Amount Per Year Rate Per Month Amount Per Month Rental Value Per Year | Rent Details Rent Changes On Date | Rent Details Rent Changes To $/SF-Annual | Rent Details Rent Changes To $/SF-Monthly |
XYZ Tenant | 1. XYZ Tenant | 50000 | Base | 0 | Apr-16 | 5.712 | 0.4 |
XYZ Tenant | Suite: 100 | 0.25 | Contract | 0 | 17-May | 5.6882 | 0.4 |
XYZ Tenant | 10/1/2014 - 8/31/2019 | $0.38 (1.5/.5) | 0 | 18-Sep | 6.1642 | 0.43 | |
XYZ Tenant | 8 Years | Office | 0 | ||||
XYZ Tenant | Freehold | 245000 | |||||
LOL Tenant | 2. LOL Tenant | 112312 | Base | 0 | Apr-16 | 4.9266 | 0.35 |
LOL Tenant | Suite: 200 | 0.25 | Contract | 0 | Jun-16 | 0 | 0 |
LOL Tenant | 1/1/1999 - 7/31/2021 | $0.38 (1.5/.5) | 0 | Aug-16 | 4.641 | 0.33 | |
LOL Tenant | 20 Years 2 Months | Office | 0 | Jun-17 | 4.7838 | 0.33 | |
LOL Tenant | Freehold | 900000 | Jun-18 | 4.9266 | 0.34 | ||
LOL Tenant | Jun-19 | 5.0694 | 0.36 | ||||
LOL Tenant | Jun-20 | 5.2241 | 0.37 | ||||
LOL Tenant | Jun-21 | 5.3788 | 0.38 | ||||
Random Tenant | 3. Random Tenant | 86034 | Base | 4.44 | Oct-19 | 5.3907 | 0.38 |
Random Tenant | Suite: 500 | 0.5 | Speculative | 381991 | Oct-20 | 5.4978 | 0.38 |
Random Tenant | 10/1/2018 - 9/30/2025 | $0.37 (1.5/.5) | 0.37 | Oct-21 | 5.6049 | 0.39 | |
Random Tenant | 2 Years | Office | 31833 | Oct-22 | 5.7239 | 0.4 | |
Random Tenant | Freehold | 389900 |
IF REGEX_Match([General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure], ".*\bTenant\b.*") THEN REGEX_Replace([General Tenant Information Tenant Name Suite Number Lease Dates Lease Term Tenure], "[^a-z]+(.*\bTenant\b).*", "$1") ELSE [Row-1:Unique ID] ENDIF
There has to be an identifier for RegEX to capture. So it there isn't a "Tenant", you have to decide what other pattern happens when there's a change in the dataset.
Cheers,
@Thableaus could the pattern be that we always want the text string (which is the tenant name) that is followed by the numbers?
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,