Creating a custom number formula with conditions

Brooks

Hello!

I am trying to create a formula in which a row is automatically assigned the next number in a series if the progress column blank, "Not started," "Processing," or "Equipment checked out." If the progress column is something else ("Wait list," "Completed, Equipment returned," or "Completed, No response"), then no number is assigned and the cell is blank.

For example:

Thank you for your assistance!

Genevieve P.

Hi @Brooks

Yes, exactly! So the first formula is to create the AutoNumber column which is then used in the second formula. You will need two separate columns.

Once you have the VALUE(MID( formula creating your sequential numbers, you can then input the RANKEQ formula in a second column referencing it. I will call the VALUE formula column "Helper Column", like so:

=IFERROR(RANKEQ([Helper Column]@row, COLLECT([Helper Column]:[Helper Column], Progress:Progress, OR(@cell = "Not started", @cell = "Processing", @cell = "Equipment checked out")), 1), "")

Genevieve P.

Hi @Brooks

To do this, I would first add in an Auto-Number column to the sheet to number each row. Then you can reference this number column in a RANKEQ formula.

A RANKEQ formula works like this:

=RANKEQ(number, range, order)

In your case, the RANGE to look through needs to be filtered. We can do this by adding the COLLECT function in that place:

=RANKEQ([Auto Number]@row, COLLECT([Auto Number]:[Auto Number], Progress:Progress, OR(@cell = "Not started", @cell = "Processing", @cell = "Equipment checked out"), 1)

Then in order for it to turn blank on the other rows instead of producing a NOMATCH error, we'll want to wrap the whole thing in an IFERROR, like so:

=IFERROR(RANKEQ([Auto Number]@row, COLLECT([Auto Number]:[Auto Number], Progress:Progress, OR(@cell = "Not started", @cell = "Processing", @cell = "Equipment checked out")), 1), "")

Cheers!

Genevieve

Brooks

Hi @Genevieve P.

Thanks for your help!

Is this possible to do without the auto numbered column? I already have a ticket number column in the sheet that is using the auto number function.

If it isn't possible to do this without the auto number column, I have a clarifying question:

I used the formula that you kindly provided and nothing happened. The numbers kept going in order and did not skip any row. I put the formula in a new column as it wouldn't let me add it to the auto number column. Is this correct?

Thanks again!

Genevieve P.

Hi @Brooks

The RANKEQ function needs numerical values to rank... I would use an auto-number since it numbers rows as they are created, so it can identify the order of the rows.

How is the current auto-number column being used, does it just generate numerical values or is there text associated with it? If there's text with it, we could create a Text/Number column to remove that text and return just the number, then use this other column to reference in the formula.

You are correct, the formula should be placed in its own Text/Number column, separate from the Auto-Number or any other column currently being used.

It would be helpful to see a screen capture of your sheet to help further, but please block out sensitive data!

Cheers,

Genevieve

Brooks

Hi @Genevieve P.

Sorry for the delay in my response! While I can't share a screenshot of the actual sheet, I made an example of the result I am after.

I do need to keep the auto-numbered column as is because it is used as a ticket number column. Every submission (or request) made needs to be addressed and tracked in this way. The number assigned refers to items that can be loaned to an individual.

Does that help? Thanks again for your help!

Genevieve P.

Hi @Brooks

This is great, thank you! So I can see that your Ticket Number does use numbers, but it has some text appended before hand. That's fine, we can strip out the text by using MID and FIND, wrapped in a VALUE function like so:

=VALUE(MID([Ticket Number]@row, FIND(".", [Ticket Number]@row) + 1, 5))

Then once you set this as a Column Formula in a helper column, this is what you can use the auto-number column to reference. Will that work?

Brooks

Hi @Genevieve P.

When I use this formula, it goes in sequence rather than skipping over certain rows based on the progress column.

If there a way to combine this formula:

=VALUE(MID([Ticket Number]@row, FIND(".", [Ticket Number]@row) + 1, 5))

With the the RANKEQ formula you wrote previously:

=IFERROR(RANKEQ([Ticket Number]@row, COLLECT([Ticket Number]:[ Ticket Number], Progress:Progress, OR(@cell = "Not started", @cell = "Processing", @cell = "Equipment checked out")), 1), "")

Thanks again! 😊

Genevieve P.

Hi @Brooks

Yes, exactly! So the first formula is to create the AutoNumber column which is then used in the second formula. You will need two separate columns.

Once you have the VALUE(MID( formula creating your sequential numbers, you can then input the RANKEQ formula in a second column referencing it. I will call the VALUE formula column "Helper Column", like so:

=IFERROR(RANKEQ([Helper Column]@row, COLLECT([Helper Column]:[Helper Column], Progress:Progress, OR(@cell = "Not started", @cell = "Processing", @cell = "Equipment checked out")), 1), "")

Brooks

Hi @Genevieve P.

Ah! I see! Yes, that works perfectly. Once again, you are a life-saver! Thanks so much 😊

Genevieve P.

No problem at all! I'm glad we got there in the end 🙂

Thanks for taking the time to set up an example sheet and screen capture, that really helped.