Forum Discussion

epslesvou's avatar
epslesvou
Copper Contributor
Aug 12, 2024

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, Spain, USA= 400 etc.... 
 
               USA    Uk
Greece. 1000   2000
Spain.    400    5000
Italy.      450      800
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 12, 2024

    epslesvou 

    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

    • epslesvou's avatar
      epslesvou
      Copper Contributor
      Aug 13, 2024
      Hi again. i send the file.
      As you can see there is a sheet ΛΕΣΒΟΣ with the table (ΑΠΟΣΤΑΣΕΙΣ_ΛΕΣΒΟΣ) .
      I want with a formula to make all combinantion from the table and poduce the results.
      I tried smth with the combinations ...look combinatons sheet but i cant make to give the correct distance from one place to another.
  • djclements's avatar
    djclements
    Bronze Contributor
    Aug 13, 2024

    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...

Resources