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, S...
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) )Thanks - as Patrick2788 mentioned, Power Query makes this very easy. See Sheet1 in the attached version.
