Not sure what kind of formula to use

Sharon Castiglia

Hello,

I am creating a project deconfliction sheet. I have a column where a date is manually entered. I would like to create a flag when any of the cells (so far 13) in a row that contain the same date will check the box.

Thank you in advance for your assistance.

Sharon C

Mark Cronk

@Sharon Castiglia ,

Ah. sorry. Try this in the Crash column:

If(AND([Sample Install Date]@row = v-lift@row, [Sample Install Date]@row = [PDI Minn]@row, [Sample Install Date]@row = shell@row, [Sample Install Date]@row =chase@row, [Sample Install Date]@row =valero@row, [Sample Install Date]@row =irving@row, [Sample Install Date]@row =Exxon@row, [Sample Install Date]@row =[flash foods]@row, [Sample Install Date]@row =[fry's]@row, [Sample Install Date]@row =bp@row, [Sample Install Date]@row =marathon@row, [Sample Install Date]@row =p66@row, [Sample Install Date]@row =rebrands@row),1,0)

The formula checks the sample data against each of the other dates. If they all equal (AND function) the sample it checks the box (=1).

Mark

Mark Cronk

Hi @Sharon Castiglia ,

Create a check box column containing the formula:

=IF(COUNTIF(date:date, =date@row) > 1, 1, 0) where date:date is the column with the dates you want to check and date@row is the date in the row being checked.

The formula will flag all duplicates. If you only want to flag the newest duplicate than you can use:

=IF(COUNTIF(date@row:date$50, =date@row) > 1, 1, 0) where date$50 is someplace below the last row in your sheet. If you go down too far you'll get an error. This formula starts at your current entry and looks down only. If you're adding New rows to the bottom you'd use date1:data@row so it looks up.

Work?

Mark

Sharon Castiglia

Thank you for the quick response. I don't think this will work. I am looking to get this to check if all the cells from V-Lift thru Rebrands contain the same date as the sample date.

Mark Cronk

@Sharon Castiglia ,

Ah. sorry. Try this in the Crash column:

If(AND([Sample Install Date]@row = v-lift@row, [Sample Install Date]@row = [PDI Minn]@row, [Sample Install Date]@row = shell@row, [Sample Install Date]@row =chase@row, [Sample Install Date]@row =valero@row, [Sample Install Date]@row =irving@row, [Sample Install Date]@row =Exxon@row, [Sample Install Date]@row =[flash foods]@row, [Sample Install Date]@row =[fry's]@row, [Sample Install Date]@row =bp@row, [Sample Install Date]@row =marathon@row, [Sample Install Date]@row =p66@row, [Sample Install Date]@row =rebrands@row),1,0)

The formula checks the sample data against each of the other dates. If they all equal (AND function) the sample it checks the box (=1).

Mark

Sharon Castiglia

I am getting an Unparseable. Would I need to add an OR somewhere in there?

Sharon Castiglia

I got this to work:

=IF(AND(VLift@row = [Sample Install Date]@row, OR([PDI Minn]@row = [Sample Install Date]@row, Chase@row = [Sample Install Date]@row, Valero@row = [Sample Install Date]@row, Irving@row = [Sample Install Date]@row, Frys@row = [Sample Install Date]@row, BP@row = [Sample Install Date]@row, Marathon@row = [Sample Install Date]@row, [P66]@row = [Sample Install Date]@row, Rebrands@row = [Sample Install Date]@row)), 1, 0)

Mark Cronk

Excellent! The OR function means that if any of the criteria in the parens following is true then the statement is true. So it's looking to see if vlift = sample data and then whether any of the other dates match sample data. If 1 of them matches then it will check the box. Is that what you're trying to do? If you want all the dates to match then remove the OR(). If you get an error confirm that all the cells references are named correctly. When you highlight the formula all of the date cells in the row should be highlighted.

Glad you're able to make it work.

Mark