Select distcinct Email Address from Another sheet

Options

I'm trying to get the distinct list of contcats (email address) from another sheet. I'm using =DISTINCT(COLLECT({Email Address}, <>"")). The email address is the entire column - not a cell range.

Best Answers

Answers

  • oboutros
    oboutros ✭✭✭
    Options

    I also used =DISTINCT(COLLECT({Email Address}, {Email Address}, ISTEXT(@cell))) and still not working

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 12/24/23 Answer ✓
    Options

    Hi @oboutros

    Your formula is just throwing a range. You need some function to reference the range, JOIN, INDEX, etc.

    Example:

    =JOIN(DISTINCT(email:email), CHAR(10))

    =INDEX(DISTINCT(email:email), 1)

    As mine uses sheet summary fields, the range is "email:email", but you can substitute it with your cross-sheet-reference range, {EWmail Address).

    Example Fix:

    =JOIN(DISTINCT(COLLECT({Email Address}, <>""),",")

    BTW, when you use the JOIN function, you don't have to check <> "" or BLANK, as joining "some text" with BLANK" is still "some text".

    =JOIN(DISTINCT({Email Address}),",")


  • oboutros
    oboutros ✭✭✭
    Options

    It did work - Thanks a lot - is there a way to return one distinct email at the time?

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @oboutros

    The number of distinct emails:

    =COUNT(DISTINCT(email:email))

    First email:

    INDEX(DISTINCT(email:email), 1)

    Seconde email:

    INDEX(DISTINCT(email:email), 2)

    To avoid errors, you can put this in front:

    =IF(COUNT(DISTINCT(email:email))>=2, INDEX(DISTINCT(email:email), 2),"")

  • oboutros
    oboutros ✭✭✭
    Options

    Worked - I greatly appriciate it. Last question, is there a way to select the Min and Max dates per email from another sheet without sorting the sheet? I have another sheet where each email is assigned to multiple rows and I'm trying to select the Min From Date and the Max To Date. I'm using: =MIN(VLOOKUP(Emails@row, {From Date sheet}, 2, false)) but not getting the correct Min Date.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @oboutros

    To get MIN or MAX, you need to feed range values. To get a range with conditions, you can use the COLLECT function.

    In this case, you collect the From Date or To Date of another sheet with the condition that the {Email} range of the sheet equals the Email@row of the current sheet.

    Example Formula

    =MIN(COLLECT({From Date}, {Email}, Email@row))

    =MAX(COLLECT({To Date}, {Email}, Email@row))

    The demo dashboard below shows how to show the Min and Max dates along with the base data dates in the Gantt view, using the Level column to sort the two sheet's data.


  • oboutros
    oboutros ✭✭✭
    Options

    Thanks you so much. It worked perfectly. I just came accross this case:

    I'm trying to get the min and the max for multiple duration - same email. Like the case below, I need to get:

    Duration 1: MyName@me.com Min= 1/1/2023 Max = 5/31/2023

    Duration 2: MyName@me.com Min= 7/1/2023 Max = 10/31/2023

    Is that possible?

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Hi @oboutros

    I need to know the logic behind the two Durations.

    For example, for a given email, "MyName@me.com", find two consecutive rows and return the first From Date as Min and the second To Date as Max. Repeat the same for the 3rd and 4th, in the same way".

    Is this what you want?

  • oboutros
    oboutros ✭✭✭
    Options

    Hi @jmyzk_cloudsmart_jp , It's two consecutive durations not two consecutive rows. So, MyName@me.com is assigned few tasks on a project and have a break in the middle. We need to know the Min and Max of each duration to claculate the number of working days for each duration and skip the break

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 12/29/23
    Options

    Hi @oboutros

    I used the following method to get two consecutive durations of the same Email.

    • Convert the Predecessors value to a number by this formula: (Assumption, only one Predecessor)
      • [Pred 1] =VALUE(Predecessors@row + "")
    • Find a row number of the Predecessor's Predecessor
      • [Pred 2] =IF([Pred 1]@row > 0, INDEX([Pred 1]:[Pred 1], [Pred 1]@row))
    • Find a row number of the [Pred 2]'s Predecessor
      • [Pred 3] =IF([Pred 2]@row > 0, INDEX([Pred 2]:[Pred 2], [Pred 2]@row))
    • Repeat the same procedure, if necessary ([Pred 4], in this example)
    • Get the row number of the same Email upstream of the Gantt chain using those [Pred 1] to [Pred 4] values.
      • [Email Pred] =IFERROR(IF(INDEX(Email:Email, [Pred 1]@row) = Email@row, [Pred 1]@row, IF(INDEX(Email:Email, [Pred 2]@row) = Email@row, [Pred 2]@row, IF(INDEX(Email:Email, [Pred 3]@row) = Email@row, [Pred 3]@row, IF(INDEX(Email:Email, [Pred 4]@row) = Email@row, [Pred 4]@row, "None")))), 0)
    • Use the [Email Pred] value to get the upstream From Date of the same Email.
      • [Min] =IF([Email Pred]@row > 0, INDEX([From Date]:[From Date], [Email Pred]@row))

    If you look at the image below, the bottom Gannt bar of the info@cloudsmart.jp's upstream From Dathe is 12/28/23, and the Min value of the 12th row matches this value, showing the above formulas work.



  • oboutros
    oboutros ✭✭✭
    Options

    Hi  @jmyzk_cloudsmart_jp Thank you so much for all your efforts in making this works. GREAT JOB

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    @oboutros

    Happy to help!😁

    I learned a lot, as well.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!