Need some help from my Smartsheet Community

Pam Ferguson

I have several rows that all have CLINS, I/C/A and BATCH columns. I have created a visual below.

Here is my requirement:

Identify any CLIN that doesn't have a "C" row associated with it. Bottom line is each CLIN and BATCH number requires a C row to be identified. I need to identify any CLIN that doesn't have a C row on my sheet.

So in this example 1334-XXXXX BATCH 3 doesn't have a C row. Any ideas how I can accomplish this?

CLIN I/C/A BATCH

1234-XXXXX C 1

1234-XXXXX 1

1234-XXXXX 1

1234-XXXXX C 2

1234-XXXXX 2

1234-XXXXX 2

1334-XXXXX 3

1334-XXXXX 3

1234-XXXXX 1

2234-XXXXX C 1

2234-XXXXX 1

2234-XXXXX 1

1224-XXXXX C 1

1224-XXXXX 1

1224-XXXXX 1

Paul Newcome

Try something like this...

First we will count how many cells contain the CLIN

=COUNTIFS(CLIN:CLIN, CLIN@row)

Next we will add into the COUNTIFS to count how many have that CLIN and have a "c" in the [I/C/A] column.

=COUNTIFS(CLIN:CLIN, CLIN@row, [I/C/A]:[I/C/A], [I/C/A]@row)

This is going to generate a number of how many rows contain the CLIN and "c". We are going to say that if this number equals zero, check a box (or flag).

=IF(COUNTIFS(CLIN:CLIN, CLIN@row, [I/C/A]:[I/C/A], [I/C/A]@row) = 0, 1)

Paul Newcome

What do you mean by "identify"? Are you just trying to flag the row or check a box?

Pam Ferguson

Yes, that would be perfect. That way we can run a report and let our billers know that they need to be "briefed" in the system. I just don't know how to figure it out.

I really appreciate this community! It is so helpful!!!

Paul Newcome

Try something like this...

First we will count how many cells contain the CLIN

=COUNTIFS(CLIN:CLIN, CLIN@row)

Next we will add into the COUNTIFS to count how many have that CLIN and have a "c" in the [I/C/A] column.

=COUNTIFS(CLIN:CLIN, CLIN@row, [I/C/A]:[I/C/A], [I/C/A]@row)

This is going to generate a number of how many rows contain the CLIN and "c". We are going to say that if this number equals zero, check a box (or flag).

=IF(COUNTIFS(CLIN:CLIN, CLIN@row, [I/C/A]:[I/C/A], [I/C/A]@row) = 0, 1)

Josh Strong

Is the C always in the same location? If so try this. Create a separate column and input the formula below.

=IF(FIND("C", [CLIN I/C/A Batch]1, 12) = 12, "Yes", "No")

This formula is assuming the C is in the 12th location. It will populate a Yes if it is included and a No if there is no C.

Let me know if that helps.

Josh

Pam Ferguson

Awesome!!! Thank you both!!! I love this community!!!

Paul Newcome

@Josh Strong

The way I read the post, [CLIN], [I/C/A], and Batch are 3 separate columns, so I think you could just use a cell reference of

=IF([I/C/A]@row = "c", "Yes", "No")

Either way you would still need to use the COUNTIFS nested inside of the IF to flag a row for a CLIN that has multiple entries all missing a "c".

Pam Ferguson

Yes that is correct. There are three separate columns and about 5,000 rows total. So I basically need to know if one row has a "C" in the I/C/A column that matches the same CLIN and BATCH.

Paul Newcome

@ferguson_pamela107206

Do you need to know if a matching CLIN does or does not have a "C"?

In your original post, you said if it does not, but in your previous comment you said if it does.