0
I have a complex set of data that I need to rearrange in excel.
The table looks something like this
+----+--------+--------+--------+--------+
| ID | Text A | Text B | Text C | Text D |
+----+--------+--------+--------+--------+
| 1 | xx | | yy | zz |
+----+--------+--------+--------+--------+
| 2 | | aa | bb | |
+----+--------+--------+--------+--------+
| 3 | cc | | | dd |
+----+--------+--------+--------+--------+
The result I need to output is essentially a combination of a transpose and a coalesce. But I have no idea how to tackle this problem in Excel.
+----+------+
| ID | Text |
+----+------+
| 1 | xx |
+----+------+
| 1 | yy |
+----+------+
| 1 | zz |
+----+------+
| 2 | aa |
+----+------+
| 2 | bb |
+----+------+
| 3 | cc |
+----+------+
| 3 | dd |
+----+------+
1
Possible duplicate of Excel 2007 transpose/combine multiple rows into one
– Máté Juhász – 2018-03-06T10:22:48.047reference question gives you a good start, after performing unpivot all you need is to delete extra column and remove rows with blank cell in second column. – Máté Juhász – 2018-03-06T10:23:34.877
Possible duplicate of Turn one row into multiple rows in Excel
– G-Man Says 'Reinstate Monica' – 2018-03-07T03:48:18.063