@ > Home > Contents > Convert column entries into row cells, depending on key Excel 97+

At the end, see VBA code for the reverse task!

Keys repeating by a fixed count
(accompanied by cells, which are to be converted to record's fields)

name1 text1
name1 text2
name1 text3
name2 text1
name2 text2
name2 text3 has to be converted to

name1 text1 text2 text3
name2 text1 text2 text3 (fixed) or to

name1 text1 text2 text3
name2 text1 text2 text3 text4
name3 text1 text2
name4 text1 text2 text3 text4 (variable, showing additional "records" not written in the example above)

Insert Name Define the first range as "Q". The fixed count is 3 and can be altered in the following formulas:

A1: =INDEX(Q,ROW(A1)*3,1)
B1: =INDEX(Q,ROW(A1)*3-3+MOD(COLUMN(A1)-1,3)+1,2) fill right until D1
Fill A1:D1 down, as far as needed.

Keys occurring with a variable count
(f.e. showing children of a parent, by Dietmar Vollmeier, 2007/11/29)

Name first column of Q as "A" and second as "B". Now refer to Q as follows:



{} means, that you must enter the formulas by pressing Ctrl-Shift-Enter simultaneously (Do not type {} manually).

Fill A1:B1 down as far as needed. Fill B1:Bx right as far as needed. 

Keys occurring with a variable count - Reverse operation (VBA)!

Sub TurnVariableLengthDataIntoFixedLength()

'to-be-assigned data allowed in column B:IP (249 data fields)
'assumption: no headers included
'data beginning in A1               IR IS
'                                 1  2  s
'before:                   after: 2  2  x
'   A  B  C  D                    3  3  b
'1  2  x  s                       4  3  d
'2  4  a                          5  3  e
'3  3  b  d  e                    6  4  a
'4  5  y                          7  5  y


For i = 1 To Range("A1").CurrentRegion.Columns.Count - 1
   Range("IU:IU") = Range("A:A").Value
   Range("IV:IV") = Range("A:A").Offset(0, i).Value
   Range("IU1").Sort Key1:=Range("IV1")
   C = Evaluate("=COUNTA(Z:Z)")
   Range("IR" & B + 1 & ":IS" & B + C) = Range("IU1:IV" & C).Value
   B = B + C

Range("IR1").Sort Key1:=Range("IR1"), Key2:=Range("IS1")

End Sub