Creating Index for Unique Values

Travis Myers

I am trying to create a formula that can be converted to a column formula but am having issues. I referred to https://community.smartsheet.com/discussion/54816/extracting-a-list-of-unique-values-from-a-list-on-another-sheet to come up with the formula to use, however it uses absolute references, which can't be made into column formulas.

What do I need to modify to allow this to work and become a column wide formula? My formula is:

=IF([Project Sum Hierarchy Helper]@row = 1, "", (IF(COUNTIFS([Job Name]$1:[Job Name]@row, [Job Name]@row) = 1, COUNTIFS([Job Name]$1:[Job Name]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell)))))))

I added a bit to ignore parent rows, as the data I am trying to index are always child rows.

Genevieve P.

Hi @Travis Myers

Taking a look at the original thread/solution, this type of formula requires the absolute reference to create the Range in which to identify the number for that row. It has to be locked to row $1 in order for this formula to calculate properly. This means that, as you've found, this formula is not able to be set as a Column Formula, nor can I think of an alternate solution to return the same result without the use of an absolute reference.

That said, formulas in Smartsheet will auto-populate as new rows are added in. As long as two rows above have the same formula, the next row created/inserted will automatically fill the formula into that row's cell as well (see: Use or Override Automatic Formatting and Formula Autofill).

You could add this formula into your column, drag-fill it down to fill all current rows, and then lock the column so editors can't delete the formula. As long as new rows are added directly beneath current content (versus after a few blank rows) your formula will continue to populate, similar to a Column Formula. Will this work for you?

Cheers,

Genevieve

Genevieve P.

Hi @Travis Myers

Taking a look at the original thread/solution, this type of formula requires the absolute reference to create the Range in which to identify the number for that row. It has to be locked to row $1 in order for this formula to calculate properly. This means that, as you've found, this formula is not able to be set as a Column Formula, nor can I think of an alternate solution to return the same result without the use of an absolute reference.

That said, formulas in Smartsheet will auto-populate as new rows are added in. As long as two rows above have the same formula, the next row created/inserted will automatically fill the formula into that row's cell as well (see: Use or Override Automatic Formatting and Formula Autofill).

You could add this formula into your column, drag-fill it down to fill all current rows, and then lock the column so editors can't delete the formula. As long as new rows are added directly beneath current content (versus after a few blank rows) your formula will continue to populate, similar to a Column Formula. Will this work for you?

Cheers,

Genevieve

Travis Myers

I believe that will work. Will adding rows in say, the middle of the sheet cause problems? Or as long as there are at least two rows above it, it will populate?

EDIT:

Never mind. After reading the article you posted, it appears that it will still work even if inserted as long as there is content above and below it. This should work then!

Genevieve P.

Hi @Travis Myers

Adding rows between other rows won't be a problem at all! As long as the formula is in the row above and the row below.

Here is the list of conditions in order for a formula to populate automatically:

• At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.
• Directly between two others that contain the same formula in adjacent cells.

This list is outlined in the Help Article for Autofill, here.

Cheers,

Genevieve

Travis Myers

Genevieve, now I am having problems implementing the second part of Paul's solution in the linked post. I don't follow how he created the list of names from the helper column. Any chance you could provide a bit of insight?

Genevieve P.

Hi @Travis Myers

In @Paul Newcome's second screen capture he has the second formula listed on the left:

=IFERROR(INDEX({Name List}, SMALL({Row ID}, [Column2]@row)), "")

Is this where you're having trouble?

Travis Myers

It was. I apparently just needed a little more coffee as I have figured it out. Thanks for checking back!

Genevieve P.

Haha no problem! Glad you got it working. 🙂

Paul Newcome

The linked solution was actually pre-column formula. To modify everything to allow column formulas, we will need to insert two helper columns and adjust the formulas only a bit.

The first helper column is a system generated auto-number column with no special formatting required. I'll call this column "Auto".

Next we need a text/number column that I'll call "Row", and we will use this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Now that we can leverage our row numbers, we can update the first COUNTIFS to remove the specified rows in the first range so that it is looking at the entire column and then add in a range/criteria set of row numbers that are less than or equal to the current row number. The second COUNTIFS is outputting the row number, but since we already have that on the sheet now, we can replace it with a basic cell reference to get rid of the redundancy.

That gives us a final column formula of:

=IF([Project Sum Hierarchy Helper]@row = 1, "", IF(COUNTIFS([Job Name]:[Job Name]@row, [Job Name]@row, Row:Row, @cell <= Row@row) = 1, Row@row)