Formula to assign a number based on criteria

AshleeEBI · 03/21/24

Hi All,

I am looking for some advice here. I work with patients who are being scanned for research. Each scan takes place at an imaging site. I have data that is automatically exported in via our API with our platform that records the orders for a scan. The sheet automatically copies rows based on date to a new sheet where I log the scan and send update requests for analysis to the site. There are probably 30 sites. Currently, when a Update Request is sent - the site is receiving a UR for EACH scan that took place - I want a request to go for only 1 scan and they can upload the analysis to that request.

The sheet I am using to request the images has 4 informational columns - that I need to mark in a sequential order.

Order #
Date
Time
Patient ID

I am trying to assign a numerical value to each order in a batch. The batch is all orders happening in a day. So ex is 03/15/2024 (this is everything for one imaging site on 03/15/2024):

Order # DATE Time PATIENT ID

20240306-58D514F4-1A06 03/15/2024 10:00 1005

20240306-6678E678-C117 03/15/2024 11:00 1006

20240306-85FF6491-A961 03/15/2024 13:00 1007

20240306-F0BEB50E-F3DE 03/15/2024 14:30 1008

To this I want to add a column labeled "Batch Count" and I want to label each 1,2,3,4.... Then I can send an update to all #1's in a batch. But I cannot figure out how to make a sequence that counts them and returns the value!!

It should look like this when finished

Order # DATE Time PATIENT ID Batch Count

20240306-58D514F4-1A06 03/15/2024 10:00 1005 1

20240306-6678E678-C117 03/15/2024 11:00 1006 2

20240306-85FF6491-A961 03/15/2024 13:00 1007 3

20240306-F0BEB50E-F3DE 03/15/2024 14:30 1008 4

I have tried checkboxes? But the time is variable, the dates are variable.. PATIENT ID is sometimes duplicated.. I feel like I might be able to use a MIN or MAX but nothing i try is working?

someone help! Lol

Thank you in advance if you help! 😁

brianschmidt · 03/21/24

I figured out a way to mark the first iteration of a batch (as opposed to marking 1, 2, 3, 4, etc.).

First, I added a "Row ID" (aka Auto Number) column so that each row can be distinctly referenced (shows up in column formula below).

Then, instead of a "Batch Count" column, I made it a "First in Batch" checkbox column with the following column formula:

=IF(ISBLANK(DATE@row), 0, IF(INDEX(COLLECT([Row ID]:[Row ID], DATE:DATE, DATE@row), 1) = [Row ID]@row, 1, 0))

This checks the first entry on a specific day. You should be able to use the checkbox to condition your update request automation.

Hope this helps!:)

brianschmidt · 03/21/24

I figured out a way to mark the first iteration of a batch (as opposed to marking 1, 2, 3, 4, etc.).

First, I added a "Row ID" (aka Auto Number) column so that each row can be distinctly referenced (shows up in column formula below).

Then, instead of a "Batch Count" column, I made it a "First in Batch" checkbox column with the following column formula:

=IF(ISBLANK(DATE@row), 0, IF(INDEX(COLLECT([Row ID]:[Row ID], DATE:DATE, DATE@row), 1) = [Row ID]@row, 1, 0))

This checks the first entry on a specific day. You should be able to use the checkbox to condition your update request automation.

Hope this helps!:)

AshleeEBI · 03/21/24

@brianschmidt YOU ARE AMAZING!! THANK YOU!!!

brianschmidt · 03/21/24

https://community.smartsheet.com/discussion/comment/426640#Comment_426640

Glad it worked!:)

Formula to assign a number based on criteria

Best Answer

Answers

Help Article Resources

Categories