How would I write a formula to locate duplicates in 1 column and the difference in another column.

Jamieruffinfsva

I have 1 sheet that has duplicated Classroom IDs but different scores on each line. I need to flag any instances during the same term of a difference over 1.00 higher or lower with the same Classroom ID.

I need to find the repeat instances within column "Classroom ID" that occur during the same Column "Term Name" and then find the difference between the 2 rows being compared in column "ES Domain", highlighting any differences with a difference of over +/- 1.00.

I have been able to come up with a formula to identify instances of the same classroom ID but I am struggling to compare one found row to another found row to get the difference. I also have not yet added in the requirement for the term to match. I am including what I have below which leads all the answers to be 0 since it is subtracting the ES Domain cell from itself.

=IF(INDEX([Classroom ID]:[Classroom ID], MATCH([Classroom ID]@row, [Classroom ID]:[Classroom ID]), 0) = [Classroom ID]@row, [ES Domain]@row - [ES Domain]@row, " ")

Thank you!

Paul Newcome

This should do the trick:

=IF(MAX(COLLECT([ES Domain]:[ES Domain], [Classroom ID]:[Classroom ID], @cell = [Classroom ID]@row, [Term Name]:[Term Name], @cell = [Term Name]@row)) - MIN(COLLECT([ES Domain]:[ES Domain], [Classroom ID]:[Classroom ID], @cell = [Classroom ID]@row, [Term Name]:[Term Name], @cell = [Term Name]@row)) > 1, MAX(COLLECT([ES Domain]:[ES Domain], [Classroom ID]:[Classroom ID], @cell = [Classroom ID]@row, [Term Name]:[Term Name], @cell = [Term Name]@row)) - MIN(COLLECT([ES Domain]:[ES Domain], [Classroom ID]:[Classroom ID], @cell = [Classroom ID]@row, [Term Name]:[Term Name], @cell = [Term Name]@row)))

Paul Newcome

Are you able to provide some screenshots for context?

Jamieruffinfsva

Yes, this is the sample sheet I've been using to test it.

Paul Newcome

Try something like this:

=IF(MAX(COLLECT([ES Domain]:[ES Domain], [Classroom ID]:[Classroom ID], @cell = [Classroom ID]@row, [Term Name]:[Term Name], @cell = [Term Name]@row)) - MIN(COLLECT([ES Domain]:[ES Domain], [Classroom ID]:[Classroom ID], @cell = [Classroom ID]@row, [Term Name]:[Term Name], @cell = [Term Name]@row)) > 1, "large discrepancy output")

Jamieruffinfsva

That is very helpful, thank you!

Is there a way to make it so that it will display the difference between the cells compared?

Paul Newcome

This should do the trick:

=IF(MAX(COLLECT([ES Domain]:[ES Domain], [Classroom ID]:[Classroom ID], @cell = [Classroom ID]@row, [Term Name]:[Term Name], @cell = [Term Name]@row)) - MIN(COLLECT([ES Domain]:[ES Domain], [Classroom ID]:[Classroom ID], @cell = [Classroom ID]@row, [Term Name]:[Term Name], @cell = [Term Name]@row)) > 1, MAX(COLLECT([ES Domain]:[ES Domain], [Classroom ID]:[Classroom ID], @cell = [Classroom ID]@row, [Term Name]:[Term Name], @cell = [Term Name]@row)) - MIN(COLLECT([ES Domain]:[ES Domain], [Classroom ID]:[Classroom ID], @cell = [Classroom ID]@row, [Term Name]:[Term Name], @cell = [Term Name]@row)))

Jamieruffinfsva

Thank you so much! That is amazing. It worked perfectly!