Forum Discussion
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
8 Replies
- I am just posting so I can keep track of this thread.
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...
It's possible to do this with a formula but it's very easy to do this with PowerQuery.
Unpivot columns (Power Query) - Microsoft Support
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?
- 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.This is what a formula solution looks like for this one. It's a bit long because the table has two header rows and the first column is not used so those must be discarded in-formula.
=LET( data, DROP(ΑΠΟΣΤΑΣΕΙΣ_ΛΕΣΒΟΣ[#All], 1, 1), a, TOCOL(TAKE(data, , 1), 1), x, ROWS(a), b, TOCOL(TAKE(data, 1), 1), y, ROWS(b), Row_LabelA, CHOOSEROWS(a, TOCOL(SEQUENCE(x) * SEQUENCE(, y, 1, 0))), Row_LabelB, CHOOSEROWS(b, TOCOL(SEQUENCE(y) * SEQUENCE(, x, 1, 0), , 1)), values, TOCOL(DROP(data, 1, 1)), HSTACK(Row_LabelA, Row_LabelB, values) )
