Creating a unique ID based on two other columns

laura_bfo

I'm trying to create a unique id based on values in 2 columns: partner and label. In our data, we have names associated with partners, but when we share data we don't want to share names, so instead I'd like to create IDs. As you can see in the sample below, we use two columns because names can be repeated between partners.

The "group_id" column is what I'd like the end result to be, but can't get there! I've tried making a helper column "distinct" with a combination of COUNT(COLLECT(DISTINCT.. values of test_id and then using that number to append to the partner.

partner = text we enter

label = text we enter

test_id (formula)= partner@row + "_" + label@row

group_id = your solution :)

For those that may be R savvy, what I would do there is:

group_by(partner, label) %>% mutate(group_id = cur_group_id()

Kelly Moore

Hey @laura_bfo

The solution below requires one system helper column - either [Row ID] or Created column. This also assumes the rows are going down the page in chronological order. If this isn't true, let me know. I'll show the formula using the [Row ID] column. The Created column can be substituted.

group_id formula:

=partner@row + "-" + COUNT(DISTINCT(COLLECT([test_id]:[test_id], partner:partner, partner@row, [Row Display]:[Row Display], @cell <= [Row Display]@row)))

Will this work for you?
Kelly

Kelly Moore

Hey @laura_bfo

The solution below requires one system helper column - either [Row ID] or Created column. This also assumes the rows are going down the page in chronological order. If this isn't true, let me know. I'll show the formula using the [Row ID] column. The Created column can be substituted.

group_id formula:

=partner@row + "-" + COUNT(DISTINCT(COLLECT([test_id]:[test_id], partner:partner, partner@row, [Row Display]:[Row Display], @cell <= [Row Display]@row)))

Will this work for you?
Kelly

laura_bfo

Wow, thank you so much! Yes, that creates the ID just like I needed. One follow-up question, do you know if there's a way to keep the group_id static (i.e. doesn't change if a label gets removed)?

In my example below, as names are added it correctly adds the partner-#. But if a label gets removed (let's say partnerA, George), then partnerA-John becomes partnerA-2 instead of partnerA-3. I'd like to keep him partnerA-3 so id's are remaining consistent in our dataset.

Initial table:

When remove a label, the ID gets renumbered:

Kelly Moore

Hey @laura_bfo

One way of keeping a static list is to have the data stored in a separate sheet and use that as a lookup table, master list or reference table- whatever you want to call it. The master list sheet would also have the columns Partner, label and, I'd probably also include test_id.

Then, in the active sheet the Partner column you could add an Index Match column that pulls the partner designation from your master list. Although this might sound complicated, its straight-forward. I'd be happy to help with that. You would add the label name in your active sheet and always get the same partner name associated with it.

Let me know if you want to proceed this way, and need some help

Kelly