Data conversion Vertical to Horizontal Format

[Nitin Shardul]

Dear Sir,

Attached two files want to convert the family details to vertical format

Regards
N3

 

[vletm]

Nitin Shardul
I hope that those are Sample Details.

 

[Nitin Shardul]

Dear Sir,

Attached two files want to convert the family details to vertical format

Regards
N3

Find attached sample file

 

[p45cal]

Try:

Sub blah()
With ActiveSheet
  LR = .Cells(.Rows.Count, "A").End(xlUp).Row
  Set rngSceDataBody = .Range("A2").Resize(LR - 1, 31)
End With
Set rngEmpCode = rngSceDataBody.Columns(1)
Set Destn = Sheets.Add(after:=Sheets(Sheets.Count)).Cells(1)
Destn.Resize(, 6) = Array("Employee Code", "Full Name", "Gender", "Date of Birth", "Employee's Age", "Relation")
Set Destn = Destn.Offset(1)
For Colm = 2 To 27 Step 5
  rngEmpCode.Copy Destn
  rngSceDataBody.Columns(Colm).Resize(, 5).Copy Destn.Offset(, 1)
  Set Destn = Destn.Offset(LR - 1)
Next Colm
Destn.Parent.Columns("A:F").EntireColumn.AutoFit
End Sub

See also button in attached.

 

[bosco_yip]

Another option in formula solution.

1] In A3, copied down :

=IFERROR(INDEX('Horizontal Data'!$A$2:$A$398,INT((ROW(A1)-1)/6)+1),"")

2] In B3, copied across to E3 and all copied down :

=IF($A3="","",OFFSET('Horizontal Data'!$A$1,MATCH($A3,'Horizontal Data'!$A$2:$A$398,0),MATCH($F3,'Horizontal Data'!$B$1:$AE$1,0)+MATCH(B$2,'Horizontal Data'!$B$1:$AE$1,0)-5))

3] In F3, copied down :

=IF($A3="","",INDEX({"Self";"Spouse";"Child1";"Child2";"Father";"Mother"},MOD(ROW(A1)-1,6)+1))

ps. the attachment formulas haven't copied down fully, please copied down all formulas until blank.

Regards
Bosco

 

[Nitin Shardul]

Try:

Sub blah()
With ActiveSheet
  LR = .Cells(.Rows.Count, "A").End(xlUp).Row
  Set rngSceDataBody = .Range("A2").Resize(LR - 1, 31)
End With
Set rngEmpCode = rngSceDataBody.Columns(1)
Set Destn = Sheets.Add(after:=Sheets(Sheets.Count)).Cells(1)
Destn.Resize(, 6) = Array("Employee Code", "Full Name", "Gender", "Date of Birth", "Employee's Age", "Relation")
Set Destn = Destn.Offset(1)
For Colm = 2 To 27 Step 5
  rngEmpCode.Copy Destn
  rngSceDataBody.Columns(Colm).Resize(, 5).Copy Destn.Offset(, 1)
  Set Destn = Destn.Offset(LR - 1)
Next Colm
Destn.Parent.Columns("A:F").EntireColumn.AutoFit
End Sub

See also button in attached.

Working Great

Thank you

 

[Nitin Shardul]

Another option in formula solution.

1] In A3, copied down :

=IFERROR(INDEX('Horizontal Data'!$A$2:$A$398,INT((ROW(A1)-1)/6)+1),"")

2] In B3, copied across to E3 and all copied down :

=IF($A3="","",OFFSET('Horizontal Data'!$A$1,MATCH($A3,'Horizontal Data'!$A$2:$A$398,0),MATCH($F3,'Horizontal Data'!$B$1:$AE$1,0)+MATCH(B$2,'Horizontal Data'!$B$1:$AE$1,0)-5))

3] In F3, copied down :

=IF($A3="","",INDEX({"Self";"Spouse";"Child1";"Child2";"Father";"Mother"},MOD(ROW(A1)-1,6)+1))

ps. the attachment formulas haven't copied down fully, please copied down all formulas until blank.

Regards
Bosco

Working Sir,
Thank you,