Alpha order

jgneely72151 · 04/22/21

I have a list of sites that I need to break up in alpha order meaning that I need to see sites that only start with the letter of the first word of that site. Is there a formula for that? I'm going to create a directory portal so that when a person clicks on any letter of the alphabet, they only see the sites starting with that alphabet. I believe in Excel there is a LEFT function. I've never used it before; not really sure how to. Nevertheless, I will be using reports on the dashboard to display this information. Any help with a formula and the best way to display this is most appreciative.

Paul Newcome · 04/22/21

I would suggest setting up a separate sheet with a form attached. The only field on the form is a dropdown that has each letter of the alphabet and set the form to populate at the top of the sheet. Also set the form to take you to a specific URL. Have that URL lead to the report.

Then go back to your master listing and insert a checkbox column with this column formula:

=IF(LEFT([Site Name Column]@row) = INDEX({Form Sheet Letter Column}, 1), 1)

This will check the box for all rows that start with the letter of the most recent form submission.

Now set your report to only pull rows where this column is checked.

So basically the user will open the form and select a letter which will redirect them to the report that contains only those rows that start with that letter.

.

Another option would be to insert a txt/number column in the master listing and enter

=LEFT([Site Name Column]@row)

Then build a report that looks at this column and only pulls rows with an "A". Next duplicate this report and change it to "B". So on and so forth until you have created 26 reports (one for each letter).

You can keep all of these reports in a workspace or you can use a shortcut widget on a dashboard to display them all.

.

Finally you could build off of the multiple reports idea by adding the "A" report to a dashboard, then create a "B" dashboard, so on and so forth and use the shortcut widget to link to the other dashboards. That would make it much easier to jump from one letter to another.

jgneely72151 · 04/22/21

Thank you so much! I had started working in similar fashion to your 2nd option and added a helper field as a means to code each entry and then I created the 26 reports and linked them all to my portal. I do like the 1st option as I won't have to manually code each entry but I really needed to get this portal up and running today. So thanks and I will hold on to this as I may revise at a later date.

Paul Newcome · 04/22/21

Happy to help. 👍️

By the way... Both of the formulas I provided can be set as column formulas, so each entry should automatically be added to the appropriate report as soon as it is added to the sheet without you having to manually enter a letter or formula.

Paul Newcome · 04/22/21