How to compare rows within same Smart Sheet

cabbsman

I need to compare rows that have a duplicate key found in the Row # column (see blue highlight). Once matched, I need to further compare the Prev Row Submission Date to the Date column in the matching row (see yellow highlight).

Once the match is identified, I need to compare the CIN Count (Total) most recent row entry to the matching row's CIN Count (Total) and if the most recent entry is less than, equal to greater than the matching row, indicate in the CIN Count Disposition column, see below for example.

In the above example, the Row 1 key was submitted on 5/2/2023 and 4/2/2023. Because the Row 1 at the top of the sheet's "Prev Row Submission Date" column is 5/2/2023 I need to match on the Row 1 blue highlight , since it's Date is 5/2/2023.

Once found, I need to compare the CIN Count (Total) from the most recent entry (at the top of the table) to the matching row's CIN Count (Total).

If the most recent Row 1's CIN Count (Total) is less than the matching Row 1's CIN Count (Total), I need to indicate "NEG", if greater "POS", if matching, "MATCH" in the most recent Row #1's CIN Count Disposition column. In this example , 2 is less than 5 so the row's column has a "NEG" result (see red bold).

Having difficulties writing up a formula and hoping someone can help. The yellow and blue highlights are just for this example and not needed in the Smart Sheet. Much appreciated.

Paul Newcome

You will need an INDEX/COLLECT to pull in the previous total based on row number and date. From there you would drop it into a nested IF comparing to the count "@row" to get your outputs. Then wrap the whole thing in an IFERROR to output a blank if there is no match.

=INDEX(COLLECT([Cin Count (Total)]:[Cin Count (Total)], [Row #]:[Row #], @cell = [Row #]@row, Date:Date, @cell = Date@row), 1)

=IF(index/collect < [Cin Count (Total)]@row, "NEG", IF(index/collect = [Cin Count (Total)]@row, "MATCH", "POS"))

=IFERROR(nested_if, "")

Paul Newcome

You will need an INDEX/COLLECT to pull in the previous total based on row number and date. From there you would drop it into a nested IF comparing to the count "@row" to get your outputs. Then wrap the whole thing in an IFERROR to output a blank if there is no match.

=INDEX(COLLECT([Cin Count (Total)]:[Cin Count (Total)], [Row #]:[Row #], @cell = [Row #]@row, Date:Date, @cell = Date@row), 1)

=IF(index/collect < [Cin Count (Total)]@row, "NEG", IF(index/collect = [Cin Count (Total)]@row, "MATCH", "POS"))

=IFERROR(nested_if, "")

cabbsman

That did it! Thank you so much for your help.

Paul Newcome

Happy to help. 👍️