'Switch values and headers

I have a google sheet with a table

card1 | card2 | card3 | card4 | card5 | card6 | card7 
------+-------+-------+-------+-------+-------+------
set3  | set1  | set1  | set2  | set2  | set4  | set1 
set4  | set2  | set3  | set3  | set4  |       | set2
      | set4  |       |       |       |       | set3
      |       |       |       |       |       | set4

How can I switch the values in the table and the values in the header to produce a table like this:

 set1 |  set2 |  set3 |  set4 
------+-------+-------+-------
card2 | card2 | card1 | card1 
card5 | card4 | card3 | card2
      | card5 | card4 | card5  
      | card7 | card7 | card6 
      |       |       | card7

Solution 1:^[1]

use:

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(
 IF(A2:G30="",,A2:G30&"¤×"&A1:G1&"¤")), "×"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"),,9^9)), "¤"))))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	player0

'Switch values and headers

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]