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:

A1: {=IF(SUM(IF(A="",0,1/COUNTIF(A;A)))<ROW(A1),"",INDEX(A,SMALL(IF(COUNTIF(OFFSET(A,,,ROW(A)),A)=1,A),ROW(A1))))}

B1: {=IF(OR(COLUMN(A1)>COUNTIF(A,$A1),$A1=""),"",INDEX(B,SMALL(IF(A=$A1,ROW(A)),COLUMN(A1))))}

{} 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 Range("IR:IS").ClearContents 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 Next Range("IR1").Sort Key1:=Range("IR1"), Key2:=Range("IS1") Range("IU:IV").ClearContents End Sub