1

For a website internationalization project, I have a Google Sheets with countries and languages that we would like to offer within that country.

Shortened sample sheet: https://docs.google.com/spreadsheets/d/1JNftjuEy97KeHfEH80bwl6H-40nNohPQrnQ-W_lzDZA/ The actual matrix is much bigger.

en de fr
US 1
DE 1 2
FR 2 1

The numbers determine the order in which the languages should be offered in the country's language menu.

Now, I would like to use a formula to extract a list of required locales.

Such as: US-en,DE-en,DE-de,FR-fr,FR-en

The table keeps on changing, so a formula would be preferred to a one-time solution.

Ken White
  • 123,280
  • 14
  • 225
  • 444
SomeBdyElse
  • 427
  • 1
  • 4
  • 11
  • I realize that the syntax for the locales is uncommon for anyone using linux/unix. Yet, it is the strange way that we would like to express locales within that project. – SomeBdyElse Nov 04 '22 at 14:40

2 Answers2

0

try:

=INDEX(TEXTJOIN(",", 1, IF(B2:D4>0, A2:A4&"-"&B1:D1, )))

enter image description here

update:

=INDEX(TEXTJOIN(",", 1, INDEX(IFERROR(SORT(SPLIT(FLATTEN(
 IF(B2:D4>0, A2:A4&"-"&B1:D1&"​"&B2:D4&"​"&ROW(B2:D4), )), "​"), 3, 1, 2, 1)),,1)))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you! This is already really helpful. It just gets the order wrong. It should be `…,FR-fr,FR-en` in the end. – SomeBdyElse Nov 04 '22 at 22:34
  • 1
    Thank you a lot! The update gets the order right and I accepted it as the answer. In the end, I even understood how the formular works, allthough I would not have been able to produce it myself. :) – SomeBdyElse Nov 06 '22 at 08:46
0

Otherwise you can use:

=ARRAYFORMULA(TEXTJOIN(",",1,MID(SORT(FLATTEN(IF(B2:D4<>"",ROW(A2:A4)*1000+B2:D4&A2:A4&"-"&B1:D1,""))),5,100)))

create a sort order by value in the range, then join them.

Terio
  • 507
  • 2
  • 5