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

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!

Find more posts tagged with

Accepted answers

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!

All comments

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.

You can guess what the formula is for 1H23.

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.