Attempting to look for duplicate names in one column that are between dates in another

Bryce_H

Essentially I am trying to find out if a name in one column shows up a certain amount of times within a certain range.

Essentially being, does "employee name" appear at least 2 times within 1/1/2023 and 1/31/2023.

More than the 2 is not an issue, but essentially trying to find out those that are at least twice. I appreciate any help as I've been all over the forums trying to figure this one out and if it is possible.

Thanks!

Danielle Arteaga

Hey, Bryce. Might have a solution for you.

I set up my sample sheet like screen cap below. Everything is a text column except Start Date, which is a Date column. (Note: my example has 30 rows, but they didn't all fit in the screen cap.)

In the 1H22 column, I put this column-level formula: =COUNTIFS(Name:Name, Name@row, [Start Date]:[Start Date], >=DATE(2022, 1, 1), [Start Date]:[Start Date], <=DATE(2022, 6, 30))

This formula finds every instance of the name in the Name column that repeats between January 1, 2022 and June 30, 2022.

In the 2H22 column, I put essentially the same formula, with a modification of the dates to capture repeats between July 1, 2022 and December 31, 2022: =COUNTIFS(Name:Name, [Start Date]@row, [Start Date]:[Start Date], >=DATE(2022, 7, 1), [Start Date]:[Start Date], <=DATE(2022, 12, 31))

You can guess what the formula is for 1H23.

In the First Unique Value column, I placed this row level formula: =IF(COUNTIFS(Name$1:Name@row, Name@row) = 1, 1) (Note that because this formula needs an absolute cell reference, you can't convert it to column-level; you have to drag it down your column.) What it does is identify the first unique occurrence of a value in the Name column. So, each cell that as a "1" in it represents the distinct values in your column.

Finally, in the Total Repeats column, I summed the total times a unique value repeated using this column-level formula: =IF([1st Unique Value]@row = 1, COUNTIF(Name:Name, Name@row))

You may not need all of this to accomplish your goal, but hoping it helps!

Danielle Arteaga

Hey, Bryce. Might have a solution for you.

I set up my sample sheet like screen cap below. Everything is a text column except Start Date, which is a Date column. (Note: my example has 30 rows, but they didn't all fit in the screen cap.)

In the 1H22 column, I put this column-level formula: =COUNTIFS(Name:Name, Name@row, [Start Date]:[Start Date], >=DATE(2022, 1, 1), [Start Date]:[Start Date], <=DATE(2022, 6, 30))

This formula finds every instance of the name in the Name column that repeats between January 1, 2022 and June 30, 2022.

In the 2H22 column, I put essentially the same formula, with a modification of the dates to capture repeats between July 1, 2022 and December 31, 2022: =COUNTIFS(Name:Name, [Start Date]@row, [Start Date]:[Start Date], >=DATE(2022, 7, 1), [Start Date]:[Start Date], <=DATE(2022, 12, 31))

You can guess what the formula is for 1H23.

In the First Unique Value column, I placed this row level formula: =IF(COUNTIFS(Name$1:Name@row, Name@row) = 1, 1) (Note that because this formula needs an absolute cell reference, you can't convert it to column-level; you have to drag it down your column.) What it does is identify the first unique occurrence of a value in the Name column. So, each cell that as a "1" in it represents the distinct values in your column.

Finally, in the Total Repeats column, I summed the total times a unique value repeated using this column-level formula: =IF([1st Unique Value]@row = 1, COUNTIF(Name:Name, Name@row))

You may not need all of this to accomplish your goal, but hoping it helps!

Bryce_H

Danielle,

Thanks so much for this! This worked out great and gave me the numbers I was trying to find. I appreciate the help and no longer have to bang my head on the desk trying to figure this out!