Auto-number Column and MAX COLLECT with Criteria

Mike Meyer

Hey friends!

I want to combine the two below operations into one column:

I'd like to return the highest value in Auto-numbered column when the criteria is met from another column. ~Return the highest/largest auto-number, from the "Row ID | Auto-Numbered" column, matched with each ID# in the "⚠Mac ID#" column.

Then, when those two columns "Row ID | Auto-Numbered" & "▽Row ID | Max Row ID for Mac ID#" match, check a box.

Row ID | Auto-Numbered = Auto-Numbered column

▽Row ID | Max Row ID for Mac ID# = Checkbox column

⚠Mac ID# = Text/number column

This is what I've tried so far... Aaand not working.

=IF([Row ID | Auto-Numbered]@row = IF([⚠ Mac ID#]@row = "", "", MAX(COLLECT([Row ID | Auto-Numbered]:[Row ID | Auto-Numbered], [⚠ Mac ID#]:[⚠ Mac ID#], [⚠ Mac ID#]@row))), 1, 0)

Your help is appreciated!

Kelly Moore

Hey @Mike Meyer

When you say your formula isn't working, what is it you are expecting that isn't happening? When I tried the formula, it appeared to work.

In a separate column, I then compared the checkmark to the results of the MAX/COLLECT value. The checkmark seemed to coincide as expected.

My testing formula that I compared to the [Row ID | Auto-Numbered] column:

=IF([⚠Mac ID#]@row <> "", MAX(COLLECT([Row ID | Auto-Numbered]:[Row ID | Auto-Numbered], [⚠Mac ID#]:[⚠Mac ID#], [⚠Mac ID#]@row)))

Kelly

Kelly Moore

Hey @Mike Meyer

When you say your formula isn't working, what is it you are expecting that isn't happening? When I tried the formula, it appeared to work.

In a separate column, I then compared the checkmark to the results of the MAX/COLLECT value. The checkmark seemed to coincide as expected.

My testing formula that I compared to the [Row ID | Auto-Numbered] column:

=IF([⚠Mac ID#]@row <> "", MAX(COLLECT([Row ID | Auto-Numbered]:[Row ID | Auto-Numbered], [⚠Mac ID#]:[⚠Mac ID#], [⚠Mac ID#]@row)))

Kelly

Mike Meyer

@Kelly Moore Thanks for the response. We have machines that are assigned ID#'s (⚠Mac ID#). We rent them; and they go on-rent multiple times. I have other sheets that need to pull in the contract # of the most recent time the machine has been (or is currently on-rent). So, I've assigned an auto-numbered column to create a checkbox helper column to identify the highest/largest auto-number assigned to each machine.

So, machine xyz can go out 30x/year. I want the most recent contract #.

With the helper column "▽Row ID | Max Row ID for Mac ID#" at the time being a Text/Number column, I first just tried the MAX COLLECT part of the formula and it only returned 0's. I cannot figure out why. I went ahead and changed the column type to checkbox and added the other part of the formula so I could get the question into the community.

Thank for your help! Let me know what you think...

Kelly Moore

Hey @Mike Meyer

What if you try to eliminate the blanks in your Max/Collect?

MAX(COLLECT([Row ID | Auto-Numbered]:[Row ID | Auto-Numbered],[⚠Mac ID#]:[⚠Mac ID#],@cell<>"", [⚠Mac ID#]:[⚠Mac ID#], [⚠Mac ID#]@row))

Also, with the leading zeros in your autonumber column, does smartsheet consider it a numeric value? A quick check is =IF(ISNUMBER([Row ID | Auto-Numbered]@row), "true"). To calculate a max, the value needs to be numeric. I think though, you would receive an error, not a zero if this was non-numeric

Kelly

Mike Meyer

@Kelly Moore When trying the ISNUMBER function, turns out the auto-numbered column is not a number... who would've thought that? With that known, now what do I do? Is there another function I can use, or someway to make the auto-number a number.

Mike Meyer

@Genevieve P. Hope you are well! Why does an auto-numbered column not actual return a number?

Kelly Moore

@Mike Meyer

Try this for testing your Max/Collect

=IF([⚠Mac ID#]@row <> "", MAX(VALUE(COLLECT([Row ID | Auto-Numbered]:[Row ID | Auto-Numbered], [⚠Mac ID#]:[⚠Mac ID#], [⚠Mac ID#]@row))))

Mike Meyer

Hey @Kelly Moore!

I tried that and it returns a #INVALID DATA TYPE.

I think I may have figured out the issue... When I switched the column (that had already populated the column with 'numbers') from an Auto-numbered column to a Text/Number column, I found that the an apostrophe appeared before the number. *Which is what we were trying to correct with the 'VALUE' part of the formula. But it didn't work.

So, I

1. I switched the 'Row ID | Auto-Numbered' column back from a Text/Number to an Auto-Number column.
2. Created a Text/Number Helper column 'Row ID | Value {H}' and applied a the column formula =VALUE([Row ID | Auto-Numbered]@row). This produced an actual number in this helper column. 👍
3. Created a Text/Number Helper column 'Row ID | Max Collect {H}' and applied the column formula you helped with =IF([⚠ Mac ID#]@row <> "", MAX(COLLECT([Row ID | Value {H}]:[Row ID | Value {H}], [⚠ Mac ID#]:[⚠ Mac ID#], [⚠ Mac ID#]@row))). And it worked. 👍
4. In the Text/Number Helper column 'Row ID | Max Row ID for Mac ID# {H}' applied the checkbox formula =IF([Row ID | Value {H}]@row = [Row ID | Max Collect {H}]@row, 1, 0). 👍

Soooo, with your help and 3 helper columns, we accomplished the goal. I think I can combine the later two into one, just haven't had the time. :)

Thank you for your help!