Aug 16 2021 10:11 PM
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
Aug 17 2021 02:08 AM
SolutionFormulas 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
Aug 17 2021 05:54 AM
Aug 17 2021 06:21 AM
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.
Aug 17 2021 02:08 AM
SolutionFormulas 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