Compare a Field if Column Can Contain Same Value More Than Once.

AlexP

Hi All,

I'm creating a booking system and think I am running into trouble with my formula because it's possible to assign the same value in a column more than once (so the formula doesn't know which row to look at possibly). Formula below.

=IF(AND({DateCheckFlag}@row=1,[Asset Name]@row={DeviceName}@row),1,0)

In my case, I am allowing for future bookings and so need to check for date, however {DeviceName} can appear in the column more than once.

Essentially trying to say, check for this particular asset and check the date flag. The end result (on a different sheet) is a traffic light symbol saying Yes or no for the equipment being currently available.

Is this possible or is a different approach required?

Thank you.

Mark Cronk

Hi Alex,

I read your question again. To confirm that I understand, you have a sheet with an asset name and date check flag columns. If you find the assets without a check in the date check flag, you want it to return a stop light symbol that is green signifying that the item is available. Correct?

The formula to do that would be:

=IF(COUNTIF({DateCheckFlag}, @cell=0,{devicename}, @cell=[Asset Name]@row)>0, "Green", "Red")

Where {datecheckflag} is your date checkbox column and {device name} is your asset name column.

Work?

Mark

Mark Cronk

Hi @AlexP ,

You need to add a date reference of some type if the device name can occur more than once. Add a criteria with the date reference to your AND statement.

Need more help?

Mark

AlexP

Hi @Mark Cronk

Thanks for your reply - I am not quite sure I follow.

Do you mean something like an auto-number system generated column where a new line comes into the sheet and auto-assigns a reference number (in a new column)?

If that's the case, I'm not sure how incorporate that in - best I can think of is checking if the field is not blank? Although the following formula is unparseable - it's possible that it isn't correct.

=IF(AND({DateCheckFlag}@row=1,[Asset Name]@row={DeviceName}@row, ISBLANK({DateReference})=false,1,0)

Appreciate your assistance.

Mark Cronk

Hi Alex,

I read your question again. To confirm that I understand, you have a sheet with an asset name and date check flag columns. If you find the assets without a check in the date check flag, you want it to return a stop light symbol that is green signifying that the item is available. Correct?

The formula to do that would be:

=IF(COUNTIF({DateCheckFlag}, @cell=0,{devicename}, @cell=[Asset Name]@row)>0, "Green", "Red")

Where {datecheckflag} is your date checkbox column and {device name} is your asset name column.

Work?

Mark

AlexP

@Mark Cronk

Thank you, this is very clever. Works great.

Correct, I had to tweak this ever so slightly to use COUNTIFS and change the conditions of available or not, but this works great.

In the end I have

=IF(COUNTIFS({DateCheckFlag}, @cell = 1, {DeviceName}, @cell = [Asset Name]@row) > 0, "Red", "Green")

Thanks for your help.

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark