How to transpose data when values appears different number of times?

user_5691558 0
Viewed 60

I'd like to transpose a data that is in 2 columns (A:B). for example in below input, values A,B,C and D in column A appears 3 times each of them.

With this input (input 1) I use this formula and get the transposition correctly like the image below:

=INDEX($B$1:$B$12,4*ROWS(D$2:D2)+COLUMNS($D2:D2)-4)

INPUT1

+---+----+
| A |  2 |
+---+----+
| B |  3 |
+---+----+
| C |  4 |
+---+----+
| D |  1 |
+---+----+
| A |  6 |
+---+----+
| B | 12 |
+---+----+
| C |  4 |
+---+----+
| D | 76 |
+---+----+
| A |  1 |
+---+----+
| B |  2 |
+---+----+
| C | 37 |
+---+----+
| D |  9 |
+---+----+

enter image description here

But if input change (input2) in such way that A, B appears less times than C and D, my output is incorrect.

INPUT2

+---+----+
| A |  2 |
+---+----+
| B |  3 |
+---+----+
| C |  4 |
+---+----+
| D |  1 |
+---+----+
| C |  4 |
+---+----+
| D | 76 |
+---+----+
| C | 37 |
+---+----+
| D |  9 |
+---+----+
| A | 47 |
+---+----+
| B |  2 |
+---+----+
| C | 37 |
+---+----+
| D |  9 |
+---+----+

I show in image below the incorrect output and expected output. enter image description here

Thanks in advance for any help.

transpose excel-formula excel
1 Answers

Here is a fairly simple approach based on counting how many cells have been filled in so far:

=IF(INDEX($A:$A,COUNT($D$1:$G1)+COUNT($C2:C2)+1)=D$1,
INDEX($B:$B,COUNT($D$1:$G1)+COUNT($C2:C2)+1),"")

copied down and across starting from D2. Assumes that a blank column is available in column C.

enter image description here

If you want to make it more dynamic but also want it to work for earlier versions of Excel than Microsoft 365, it gets a bit ugly unfortunately. You can use a conventional way of listing out the unique values in column A in alphabetical order to get the headers:

=IFERROR(INDEX($A:$A, MATCH(SMALL(IF((COUNTIF(C$1:$C1, $A$1:INDEX($A:$A,COUNTA($A:$A)))=0), COUNTIF($A$1:INDEX($A:$A,COUNTA($A:$A)), "<"&$A$1:INDEX($A:$A,COUNTA($A:$A))), ""), 1), COUNTIF($A$1:INDEX($A:$A,COUNTA($A:$A)), "<"&$A$1:INDEX($A:$A,COUNTA($A:$A))), 0)),"")

adapted from this, pulled across as required (say, to column Z).

Then a slight modification to the main formula to avoid zeroes appearing under the blank headers:

=IF(AND(INDEX($A:$A,COUNT($D$1:$Z1)+COUNT($C2:C2)+1)=D$1,D$1<>""),
INDEX($B:$B,COUNT($D$1:$Z1)+COUNT($C2:C2)+1),"")

Copied down and across as far as column Z.

enter image description here

user_38949170
Related