Assigning a Unique ID based on Multiple Criteria

Question

Hi!

I need assistance with updating a Unique ID (Column B) .

Some background on what I've currently done:

To create the Unique ID, I built an iterative macro that transposes ID #1 & ID#2, sorts the data, and assigns each entry a unique ID using the Multi-Row Formula (IF ID = Row-1 ID, THEN Row-1 Unique ID, ELSE Row-1 Unique ID +1). Then, I append back the unique ID to the original data using the Record ID. However, the data goes through multiple iterations as the same record ID can be assigned different unique IDs through the process, and I want to ensure that Record IDs get assigned the same Unique ID. I reviewed the data I have so far, and this piece of the workflow is working as expected.

What I need help with:

I also need to take into consideration ID #3 (concatenation of ID #3a and ID #3b). I'd like to update Unique IDs if ID #3 is the same as other records, but I also need to maintain the grouping I've established with my macro. In the attached example, RecordID 3986696 has a unique ID of 18, but you'll notice that there are other records with the same ID #3 that are assigned a unique ID of 17. I'd like to update the unique ID for 3986696 to 17 as well as the other record that is assigned 18 (156436840). Please note that if ID #3a is blank, it shouldn't be grouped with other blanks under ID #3a. It should maintain the grouping established with the macro.

Please let me know if any additional information or clarification is needed.

Thank you!

AlteryxExample.xlsx

aband · Answer

Hi M,

Thanks for your quick response. It's going to take me some time to anonymize the workflow and full data, and I don't necessarily think it's needed to address my issue. I'm hoping to create a second workflow to run after the original workflow to address the issue I'm currently facing, which is grouping based on ID #3 while maintaining the current grouping (i.e., consolidating the grouping). To answer your question around the logic for the record in question, l am not so much concerned about if the unique ID is 17 or 18. My issues lies in the fact that all the records are related but are not being grouped together under the same unique ID.

Thank you.

mceleavey · Answer

Hi @aband ,

Can you post the workflow with the Iterative macro with the starting data to show what you are trying to do?

If we can see the process by which you are arriving at the current output then we can look into what you're trying to achieve. Given your current explanation, you have not provided the logic by which to determine why RecordID 3986696 should be changed to 17 rather than the others being assigned 18.

Thanks,

M.