– Bloggen som ger dig kraftfulla tips och trix till Excel
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
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
I bloggen Excelerera listar vi tips & trix som hjälper dig få ut mer av ditt Excel-användande.
Fyll i kontaktformuläret nedan så hjälper vi dig komma igång med PowerPage. Du kan även boka in en kostnadsfri konsultation för att få en bättre inblick i hur PowerPage kan hjälpa dig nå dina mål.