Finding Distinct Values in Two Columns

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?


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    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)

Answers

  • Brett Wyrick
    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.

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there πŸ‘€) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    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)

  • Thanks Paul!


    I think this worked!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    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
    Brett Wyrick ✭✭✭✭

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

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there πŸ‘€) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!