Looking to get all possible combinations of currencies from a list:
| Currency Code |
|---|
| USD |
| JPY |
| BGN |
| CZK |
| DKK |
| GBP |
| HUF |
| PLN |
| RON |
| SEK |
| CHF |
| ISK |
| NOK |
| TRY |
| AUD |
| BRL |
| CAD |
| CNY |
| HKD |
| IDR |
| ILS |
| INR |
| KRW |
| MXN |
| MYR |
| NZD |
| PHP |
| SGD |
| THB |
| ZAR |
| EUR |
Looking to output USD-USD, USD-JPY, JPY-USD etc for all possible combinations.
Is there a way to have the output be a dynamic array using the MS365 formulas?
what ive tried:
=TOCOL(MyTable[@Currency]&TRANSPOSE(MyTable[@Currency]))
>Solution :
One way:
=TOCOL(MAP(TOCOL(A1:A31&"-"&TRANSPOSE(A1:A31)),LAMBDA(x,IF(INDEX(TEXTSPLIT(x,"-"),,1)<>INDEX(TEXTSPLIT(x,"-"),,2),x,NA()))),3)
Result:
=TOCOL(A1:A31&"-"&TRANSPOSE(A1:A31))
Is used to get full list, rest of the formula is to exclude USD-USD, JPY-JPY and etc.