Looking for excel Formula to Move multiple data to specific columns

[Cherbpt]

I am running windows 10 on my Surface pro 6 using 2017 Microsoft Excel .

I have astrological midpoints  ex: abcd cell A3 , efgh A7 & ijkl  A11) with two sets of delineations.

(cell A4, A8 & A12) psychological

(cell A5, , A9 , & A13) physical.

This is for roughly 350 differing midpoints.

I need to move the delineations to their own columns which would be column columnB & C

Column B would be for the psychological

Column C for the physical

The asterick shown on the attached example is on my actual spreadsheet.

They pulled in from the source imported from.

Is there a way to move them with a specific formula to their own  columns 

[#2 · Accepted solution]

@Cherbpt 

Formulas can return values from the referenced cells but they cannot move cell values. For that, you will need VBA.

Module1 in the attached contains the following code and contains a button called "Transpose Data" on Sheet1 which you may click to run the code and the code will transpose the data in the desired format.

Sub TransformData()
Dim lr      As Long
Dim Rng     As Range
Dim RngArea As Range
Dim arr     As Variant
Dim i       As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False

On Error Resume Next
Set RngArea = Range("A1:A" & lr).SpecialCells(xlCellTypeConstants, 2)
On Error GoTo 0
If RngArea Is Nothing Then Exit Sub
ReDim arr(1 To RngArea.Areas.Count, 1 To 3)
For Each Rng In RngArea.Areas
    If Rng.Cells.Count = 3 Then
        i = i + 1
        arr(i, 1) = Rng.Cells(1).Value
        arr(i, 2) = Rng.Cells(2).Value
        arr(i, 3) = Rng.Cells(2).Value
    End If
Next Rng

Columns(1).Clear
Range("A2").Resize(UBound(arr, 1), 3).Value = arr
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

[#3]

wow ty so so much! Have an awesome day!

[#4]

You're welcome @Cherbpt! Glad it worked as desired.

Please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.