Sort not working

[Daryl_Zernick]

I am using the following "Valid If" formula:

SORT(Form Responses 1[Email Address]+StateOfMichiganCompleted[Email Address], False)

The drop down does not sort the combined list, only each tables list then combines the two lists into one so that you have to scroll through the list to find where one table's list ends and the other's begins. Could someone help me solve this so that the combined drop down list is sorted all together?

Thank you!

Daryl

[Suvrutt_Gurjar]

Not tested but you could try the below

SORT((Form Responses 1[Email Address]+StateOfMichiganCompleted[Email Address]), False)

Please revert if it works or not so that we can look into alternatives to get it work.

[Daryl_Zernick]

Thank you very much Suvrutt_Gurjar for your time!! Unfortunately, that does not work, but I do greatly appreciate it!!

[Suvrutt_Gurjar]

You may want to ensure [Email Address] column is of same type in both the tables.

Also you may wan to see in test results pane any clues you may have.

My quick testing ( even though not exactly on two tables but randomly sorted list in a single table) showed that SORT() works well even with a format of your originally shared expression.

The below screenshot is of randomly added emails started with randomly arranged alphabets.

The below is a sorted list with SORT() applied. 🙂

[Daryl_Zernick]

Thank you again Suvrutt_Gurjar! The email types are both the same in each table. The sort definitely works for each list, but just does not combine the two as one sorted list, as the expression is written.

[Markus_Malessa]

I know it seems redundant, but try:

SORT(LIST(Form Responses 1[Email Address] + StateOfMichiganCompleted[Email Address]), FALSE)

[Daryl_Zernick]

Unfortunately this solutions returns the following error: "SORT does not accept a list of lists"

Thank you for your time though Markus!

[Markus_Malessa]

Thanks for trying. I wasn't sure if it would work that way or not. Certainly you seem to be on the right track with combining the lists of emails, but it appears that it sorts each list of emails and then combines them? So the resulting dropdown is one list sorted and then the other list sorted? I wonder if the following would result in something different:

SORT(UNIQUE(Form Responses 1[Email Address] + StateOfMichiganCompleted[Email Address]),FALSE)

[Daryl_Zernick]

Unfortunately, that produced the same results as my original expression, where each list is sorted and put into the dropdown but not sorted as one list. So, you have list one ending with Z and list 2 starting with A in the same dropdown instead of all emails being sorted A to Z in the dropdown.

[Daryl_Zernick]

Looking more closely at these two separate sorted lists in one drop down that my expression is creating, I had assumed the resulting list was simply both table's lists being sorted independently then added into one dropdown. However, the dropdown with the two sorted lists is pulling emails from both tables and sorting them into two. Weird! There is no duplication of emails either. It's as if there was a space before the email address, forcing the two sorts, but that is not the case.

[Markus_Malessa · Accepted solution]

I got one more possible solution. I am guessing each list of email addresses is probably {"email1" , "email2" , "etc"} so try the following:

SORT(SPLIT(CONCATENATE(Form Responses 1[Email Address], " , ", StateOfMichiganCompleted[Email Address]), " , "), FALSE)

[Daryl_Zernick]

I also sorted one of the sheets to see if it would reproduce the error in the sheet and it did not.

[Markus_Malessa]

Could test the output by doing a virtual column of type LongText with formula 

Form Responses 1[Email Address]

See what that actually looks like. Then test with just adding the second list of emails and see if there is a comma missing between the two lists. Then keep adding more of the formula to see how the output changes. I frequently test subsets of my formulas in virtual columns to make sure the individual parts of a longer formula produce the expected results.

[Daryl_Zernick]

Markus,

At some point I had selected the wrong table to apply this solution. This DOES work!

Thank you so much for all your assistance! It is very graciously appreciated sir!

Warm regards,

Daryl

[Markus_Malessa]

Glad something ended up working in the blackbox that is Appsheet. I have found that list addition/subtraction works in odd ways in Appsheet. It appears that it is not always a one size fits all situations. I have even had to do LIST(List1)+LIST(List2) before to make something work.