how to use arrayformula in google sheets

Question

Google Sheets

Please tell me how to work with this function Google. There is a table and I need to transfer the names of cities using this formula to other columns and put them in alphabetical order in one column, with a capital letter in another column and without repetition in the third column. Here is a screenshot: https://sun9-37.userapi.com/c857228/v857228200/1b7...
The formula has already been applied in column D. I can not find normal information about this formula on the Internet so that I can figure it out. I tried to transfer the names of cities with the arrayformula function to column E and sort from A to Z. But he removed half of the cities altogether, leaving only three, and then not in alphabetical order. I can't figure out what this function is for and how it works. Describe in as much detail as possible how the formula is compiled, I want to understand at last already. Thanks in advance!

Answer 1

The most important thing to know about ARRAYFORMULAis that it ARRAYFORMULAwill return the same as ARRAYFORMULA(ARRAYFORMULA(ARRAYFORMULA)). I do not know how important this is for you now, but this is fundamental knowledge.
You don't need ARRAYFORMULA to sort everything alphabetically

=SORT(FILTER({A2:A;B2:B;C2:C};{A2:A;B2:B;C2:C}<>"");1;1)

Another variant of range concatenation (feature is not documented):

=FILTER(SORT(FLATTEN(A2:C);1;1);SORT(FLATTEN(A2:C);1;1)<>"")

Then the dancing begins with ARRAYFORMULA

=ARRAYFORMULA(IFERROR(UPPER(LEFT(F2:F;1)) & LOWER(RIGHT(F2:F;LEN(F2:F)-1));""))

• Example https://docs.google.com/spreadsheets/d/13nCH_MELvu...
  

Answer 2

ARRAYFORMULA is needed to use non-array functions on arrays. And you need other functions: FILTER, SORT and UNIQUE. Read their descriptions carefully .