Select distcinct Email Address from Another sheet

oboutros

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.

jmyzk_cloudsmart_jp

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}),",")

Sheet

https://app.smartsheet.com/sheets/XQJq76gMGM6gjH55fjQQgpjMHfqpV42655FWpx91?view=grid

Log in to view this sheet.

jmyzk_cloudsmart_jp

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),"")

jmyzk_cloudsmart_jp

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.

COLLECT Function | Smartsheet Learning Center

https://help.smartsheet.com/function/collect

Used within another function to collect specific values in a range that meet the provided criteria.

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.

Smartsheet

https://app.smartsheet.com/b/publish?EQBCT=a9a3cdac712a4e34a1c8e4028a965e8e

oboutros

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

jmyzk_cloudsmart_jp

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}),",")

Sheet

https://app.smartsheet.com/sheets/XQJq76gMGM6gjH55fjQQgpjMHfqpV42655FWpx91?view=grid

Log in to view this sheet.

oboutros

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

oboutros

jmyzk_cloudsmart_jp

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

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

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.

COLLECT Function | Smartsheet Learning Center

https://help.smartsheet.com/function/collect

Used within another function to collect specific values in a range that meet the provided criteria.

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.

Smartsheet

https://app.smartsheet.com/b/publish?EQBCT=a9a3cdac712a4e34a1c8e4028a965e8e

oboutros

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

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

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

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.

Smartsheet

https://app.smartsheet.com/b/publish?EQBCT=abd243c482734dd48d43a9add2bdbb7d

oboutros

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

jmyzk_cloudsmart_jp

@oboutros

Happy to help!😁

I learned a lot, as well.