Formula Help - Check the box once per unique ID

MHalvey

Hello - I will try my best to explain my formula question. I am using DataTable to import a few hundred to a few thousand rows of data monthly into a targeted sheet. After 180 days from a specific date column, they roll off the targeted sheet.

My goal is to show the Unique Rows like the "delete duplicate" functionality in Excel but I don't want to delete the rows, I want just checkbox the first, and leave the rest unchecked. So I can use formulas to count those checked and filters for additional functionality.

Looking at the image below, I need to use a formula to check a box to show a row only if a unique ID column (basic join formula) and only once. The Unique ID is just a basic join formula from data to the right.

The formula in the Unique Row is: =IF(COUNTIF([Unique ID]$1:[Unique ID]@row, [Unique ID]@row) > 1, 0, 1)

This formula is working as I need it to however after I add 6,500 rows of information, my connection between DataTable and the targeted sheet stopped because I hit the 25,000,000 cell reference by formula limit. This formula is checking the cells above it if it matches and been matched before. With each additional row, it keeps growing and growing and thus I hit the limit.

Any help with creating a different formula that isn't building on itself, maybe one that's a column formula would be extremely helpful. I am open to adding additional helper columns as well.

Formula Demo.png

I hope my request is clear enough and thank you for your help.

-Michael

Lucas Rayala

@MHalvey -- if you have DataMesh, you could make this checkbox happen with a helper page and no formulas. On your original page, create a RowID column (this will create a generic unique id for each row). Set up the Datamesh using your Unique Id as the key on both pages. The only thing you need to copy over is the RowID and the unique id. Set the DataMesh configuration to "Copy and add New" so it creates a new row for each Unique ID. Leave the option for "Duplicate in Source Sheet" as "Pick First Match". The helper sheet will populate with a list of the unique IDs and associated RowID. Because you selected "Pick First Match" it will give you the RowID of the first instance in your original page. Create a helper column in the helper sheet which is just a checked checkbox (column formula with "=1" should do it). Create another DataMesh config pointing from the helper sheet to the original sheet. This time use the row ID as your key. The only thing you need to copy over is the checkbox.

Lucas Rayala

@MHalvey -- if you have DataMesh, you could make this checkbox happen with a helper page and no formulas. On your original page, create a RowID column (this will create a generic unique id for each row). Set up the Datamesh using your Unique Id as the key on both pages. The only thing you need to copy over is the RowID and the unique id. Set the DataMesh configuration to "Copy and add New" so it creates a new row for each Unique ID. Leave the option for "Duplicate in Source Sheet" as "Pick First Match". The helper sheet will populate with a list of the unique IDs and associated RowID. Because you selected "Pick First Match" it will give you the RowID of the first instance in your original page. Create a helper column in the helper sheet which is just a checked checkbox (column formula with "=1" should do it). Create another DataMesh config pointing from the helper sheet to the original sheet. This time use the row ID as your key. The only thing you need to copy over is the checkbox.

MHalvey

@Lucas Rayala - Thank you for idea unfortunately the Unique IDs aren't consistent and change each month so having the user copy those over each time would be too much for them as they don't work in Smartsheet regularly or licensed users. I was hoping for a different formula that didn't build on itself and thus hit the 25 million cell reference limit would work. However after speaking more with tech support, we are just moving forward with a simple Move Row automation off the targeted sheet from DataTable. That way the targeted sheet can still use the above formula but not store more than a months worth of data, which won't hit the 25million limit. After it's moved to the final sheet, the formulas will just be text.

We loose the functionality of DataTable removing the rows that don't meet the filter criteria but I made a simple column formula off a date in the row with conditional formatting to tell the user after 6 months, you can delete this row. It's not the best but it will work for now.

Thank you again for your ideas and have a great night!

-Michael