Forum Discussion
epslesvou
Aug 12, 2024Copper Contributor
Combinations
Hello. I need some help, I want to know if there is a way to : If I have the following table, I want to extract all combination with the values..... Greece, USA = 1000 Greece, UK=2000, S...
djclements
Aug 13, 2024Bronze Contributor
epslesvou As a variant, you could try the following formula to unpivot your table:
=LET(
table, DROP(ΑΠΟΣΤΑΣΕΙΣ_ΛΕΣΒΟΣ,, 1),
row_labels, TAKE(DROP(table, 1),, 1),
col_labels, DROP(TAKE(table, 1),, 1),
values, DROP(table, 1, 1),
VSTACK(
HSTACK("ΤΟΠΟΣ ΔΙΑΙΤΗΤΗ", "ΓΗΠΕΔΟ", "ΑΠΟΣΤΑΣΗ"),
HSTACK(TOCOL(IF({1}, row_labels, col_labels)), TOCOL(IF({1}, col_labels, row_labels)), TOCOL(values))
)
)
Or, as a slightly less verbose option with CHOOSE instead of IF:
=LET(
table, DROP(ΑΠΟΣΤΑΣΕΙΣ_ΛΕΣΒΟΣ,, 1),
arr, LAMBDA(n, CHOOSE(n, TAKE(DROP(table, 1),, 1), DROP(TAKE(table, 1),, 1))),
VSTACK(
HSTACK("ΤΟΠΟΣ ΔΙΑΙΤΗΤΗ", "ΓΗΠΕΔΟ", "ΑΠΟΣΤΑΣΗ"),
HSTACK(TOCOL(arr({1})), TOCOL(arr({2})), TOCOL(DROP(table, 1, 1)))
)
)
See attached...