Google Sheets: When there are dupe values in a column, determine which value should be included by comparing corresponding dates in a date column

user_16931748 0
Viewed 24

I have a dataset with an ID column and Edit Date column. There are some duplicate IDs but the Edit Dates will be different for each dupe ID. I created 2 helper columns to help me determine if I need to include the row of data in a separate analysis I'm working on. I included Notes in the sample dataset, with a short description of each column.

I want to include any rows that:

  • Have Count value = 1 or
  • Have Count value >1 but the Edit Date value for its corresponding ID is the lowest value of all Edit Dates listed in all rows this value is in

I don't want to include any rows that:

  • Have Count value >1 and Edit Date value that not the lowest Edit Date for the corresponding ID

So far all I have is the first part...I'm having trouble figuring out how to go about the comparison when Count >1, so any help would be greatly appreciated.

This is all I have so far...

=IF(C2:C=1,"YES",...?)

Thank you in advance!

countif vlookup google-sheets if-statement sorting
1 Answers

try:

=ARRAYFORMULA(IF(A2:A="",,VLOOKUP(A2:A&B2:B, 
 {SORT(A2:A&B2:B, B2:B, 1), IF(COUNTIFS(SORT(A2:A, B2:B, 1), SORT(A2:A, B2:B, 1), 
 ROW(A2:A), "<="&ROW(A2:A))=1, "yes", "no")}, 2, 0)))

enter image description here

user_56326290
Related