Finding Distinct Values in Two Columns

PM Assist

Hi,

I was wondering if it was possible to find the number of distinct values in two columns. In this case I am looking to find if there is an overlap between start and completion dates for a given crew lead.

Is there a reason that I am getting 1 as my output here?

Is there any other way I can accomplish this?

Paul Newcome

If you are looking to flag rows that have an overlap in dates, you are going to want to count how many rows have an end date after the current row's start date as well as a start date before the current row's end date.

=IF(COUNTIFS([Actual Start Date]:[Actual Start Date], @cell <= [Scheduled Completion Date]@row, [Scheduled Completion Date]:[Scheduled Completion Date], @cell >= [Actual Start Date]@row) > 1, 1)

Paul Newcome

Happy to help. 👍️

I also just noticed that you had the additional criteria of by crew lead. In that case you would need the following adjustment:

=IF(COUNTIFS([Actual Start Date]:[Actual Start Date], @cell <= [Scheduled Completion Date]@row, [Scheduled Completion Date]:[Scheduled Completion Date], @cell >= [Actual Start Date]@row, [Crew Lead]:[Crew Lead], @cell = [Crew Lead]@row) > 1, 1)

Brett Wyrick

Hey @PM Assist -

I used the following and I got "6" as my answer, so hopefully it can work for you too.

For your "helper" column that you currently have named "Column29", add this formula:

=IF(COUNTIFS([Actual Start Date]$1:[Scheduled Completion Date]@row, [Actual Start Date]@row) = 1, COUNT(DISTINCT([Actual Start Date]$1:[Scheduled Completion Date]@row)))

So what you could do is put the following formula in a different column's cell (or use as a Sheet Summary Field) of your data and it'd show the highest number in that column:

=MAX([Column29]:[Column29])

If it had to go in that same column, make the top item a "Parent" then do this formula for all cells:

=IF(COUNT(CHILDREN()) > 0, MAX(CHILDREN()), IF(COUNTIFS([Actual Start Date]$1:[Scheduled Completion Date]@row, [Actual Start Date]@row) = 1, COUNT(DISTINCT([Actual Start Date]$1:[Scheduled Completion Date]@row))))

I adapted the answer from this post from my new friend across the pond, @Paul Newcome, with a slight twist to accommodate the two columns and the particular use case.

Let me know if this answer works for you, @PM Assist.

Paul Newcome

If you are looking to flag rows that have an overlap in dates, you are going to want to count how many rows have an end date after the current row's start date as well as a start date before the current row's end date.

=IF(COUNTIFS([Actual Start Date]:[Actual Start Date], @cell <= [Scheduled Completion Date]@row, [Scheduled Completion Date]:[Scheduled Completion Date], @cell >= [Actual Start Date]@row) > 1, 1)

PM Assist

Thanks Paul!

I think this worked!

Paul Newcome

Happy to help. 👍️

I also just noticed that you had the additional criteria of by crew lead. In that case you would need the following adjustment:

=IF(COUNTIFS([Actual Start Date]:[Actual Start Date], @cell <= [Scheduled Completion Date]@row, [Scheduled Completion Date]:[Scheduled Completion Date], @cell >= [Actual Start Date]@row, [Crew Lead]:[Crew Lead], @cell = [Crew Lead]@row) > 1, 1)

Brett Wyrick

@Paul Newcome Appreciate the input. I was close but not fully there, thanks for chiming in!