I have 3 columns and multiple rows and the structure of the columns/rows needs some changes. Since there is alot of rows i would want to do it with a formula or other to achieve the results and not manually change it.
Original:
| Currency | Date | Value |
|---|---|---|
| EUR | 2022-05-30 | 0.93 |
| DKK | 2022-05-30 | 1.93 |
| SEK | 2022-05-30 | 2.92 |
| EUR | 2022-05-31 | 0.95 |
| DKK | 2022-05-31 | 1.93 |
| SEK | 2022-05-31 | 2.93 |
| EUR | 2022-06-01 | 0.98 |
| DKK | 2022-06-01 | 1.93 |
| SEK | 2022-06-01 | 2.95 |
Expected:
| Date | EUR | DKK | SEK |
|---|---|---|---|
| 2022-05-30 | 0.93 | 1.93 | 2.93 |
| 2022-05-31 | 0.95 | 1.93 | 2.92 |
| 2022-06-01 | 0.98 | 1.93 | 2.95 |
So for each line there is originally a currency with a date and value. For each currency there is a new row (with same date). What i need is one date-row with multiple currency in one row (make currencies a column instead of rows)
I tried the Transponer in excel but it did not get me the result i wanted. This is what Transponer got me:
| DKK | EUR | SEK |
|---|---|---|
| 2022-05-30 | 2022-05-30 | 2022-05-30 |
| 1.93 | 0.93 | 2.93 |
CodePudding user response：
The easiest way to achieve your aim, is to use a PivotTable.
To do it only using formulas.
In Excel for O365.
Extract a unique list of dates by using the UNIQUE function for the date column.
=SORT(UNIQUE(B2:B10))
Extract a unique list of currencies by using the UNIQUE function and then transpose it with the TRANSPOSE function to populate the column headings for the currencies.
=TRANSPOSE(UNIQUE(A2:A10))
Then use XLOOKUP with multiple criteria and ranges to return the matching exchange rates. The ampersand & symbol is used to join the lookup_value and lookup_array allowing multiple criteria.
=XLOOKUP(G$1&$F2,$A$2:$A$10&$B$2:$B$10,$C$2:$C$10,0)
