Second thought from a-touch-of-unpivot-and-pivot, making it fully dynamic.

1. Use TEXTJOIN to have the table look like one column in an HTML table. (or any more explicit XML if you prefer)

A11
="<table><tr><td>" & TEXTJOIN("</td></tr><tr><td>";0;Table2[#Headers]&"|"& Table2[#Data]) & "</td></tr></table>"

Result:
<table><tr><td>USA|Yes</td></tr><tr><td>UK|No</td></tr> (snip)

‍

2. FILTERXML to a range of values.

A15
=FILTERXML(A11;"//tr/td")

‍

3. SUBSTITUTE the pipe character and FILTERXML into two columns (xpath = //a vs //b).

E15
=FILTERXML("<t><a>" & SUBSTITUTE(A15#;"|";"</a><b>") & "</b></t>";"//a")

F15
=FILTERXML("<t><a>" & SUBSTITUTE(A15#;"|";"</a><b>") & "</b></t>";"//b")

‍

4. Make a pivot table or summarize using UIQUE and SUMIFS.

i15
=UNIQUE(E15#)

J14
=TRANSPOSE(UNIQUE(F15#))

J15
=COUNTIFS(E15#;I15#;F15#;J14#)

The formulas may be combined into fewer calculations tagging the unpivoted columns as //a and //b in cells A45 and B45.

Using FILTERXML to parse the
=FILTERXML("<t><a>" &
 SUBSTITUTE(
   FILTERXML("<table><tr><td>" &
     TEXTJOIN("</td></tr><tr><td>";0;Table2[#Headers] &"|"& Table2[#Data]) &
      "</td></tr></table>";"//tr/td");
   "|";
   "</a><b>"
 ) & "</b></t>"
;"//a")

Example file with Table2 at https://ftp.excelerera.se/1006/unpivot-columns.xlsx

‍

Unpivot Rows and Columns

Yet another development - combine also with rows from first column when unpivoting a table.

Both as step by step and more combined into four formulas.

‍

B52 is the unpivoting cell.

=FILTERXML("<t><fld1>" &

     SUBSTITUTE(

          SUBSTITUTE(

               FILTERXML(

                    "<table><tr><td>" &

                         TEXTJOIN("</td></tr><tr><td>";0;Table2 A:A &"|1"&Table2[#Headers] B:SIT&"|2"&Table2[#Data] B:SIT)

                    & "</td></tr></table>"

               ;"//tr/td")

          ;"|1";"</fld1><fld2>")

     ;"|2"

     ;"</fld2><fld3>"

     ) & "</fld3></t>"

;"//fld" & {1\2\3})

‍

Wihtout non breaking spaces showing the build, it looks like:

=FILTERXML("<t><fld1>"&SUBSTITUTE(SUBSTITUTE(FILTERXML("<table><tr><td>" &TEXTJOIN("</td></tr><tr><td>";0;Table2 A:A &"|1"&Table2[#Headers] B:SIT&"|2"&Table2[#Data] B:SIT)&"</td></tr></table>";"//tr/td");"|1";"</fld1><fld2>");"|2";"</fld2><fld3>")&"</fld3></t>";"//fld"&{1\2\3})

Explained from TEXTJOIN and upwards;

                    "<table><tr><td>" & starts table, table row and table data

‍

TEXTJOIN("</td></tr><tr><td>";0;Table2 A:A &"|1"&Table2[#Headers] B:SIT&"|2"&Table2[#Data] B:SIT)

     "</td></tr><tr><td>" text between values. Looks end table data and tbale row, startas table row and table data.

     0 = do not skip empty cells

     Table2 A:A & "|1" & Table2[#Headers] B:SIT & "|2" & Table2[#Data] B:SIT

          Table2 A:A & "|1" addresses the cells where Table2 and Column A intersects and concatenates |1 to mark end of field one

          Table2[#Headers] B:SIT & "|2" dito table headers including margin for more columns all the way to column SIT.

‍

                    & "</td></tr></table>" ends table data, table row and table

‍

     SUBSTITUTE(...;"|1";"</fld1><fld2>) substitutes|1 to XML end of fld1 and start of fld2. Dito |2

‍

FILTERXML("<t><fld1>" & ... & "</fld3></t>";"//fld" & {1\2\3})

     "<t><fld1>" & adds opening tags of XML code

     & "</fld3></t>" adds closing tagsof XML code

  

"//fld" & {1\2\3}) results in fld1, fld2 and fld3 in in absolute arrays of cells sideways resulting in fields one, two and three.

‍

Example file at https://ftp.excelerera.se/1006/unpivot-rows-and-columns.xlsx