Transform every all 5 rows to column

Question

I need to help for you because i'd like to tranform data in my excel.

The example:

I'd like to transfrom to in this form:

Do u have any tips for this? I'm a "basic" excel user.

Please if i need use a VBA please give detailed description :D

Thank u in advance.

There was the exact same question very recently which I posted a solution to, you can try it out: https://stackoverflow.com/a/75280879/9852011 — andrewb, Jan 31 '23 at 06:54
Welcome karesz55! The person who asked a question can mark one answer as "accepted". Accepting doesn't mean it's the best answer, it just means that it worked for the person who asked. https://stackoverflow.com/tour — user10186832, Jan 31 '23 at 07:22
A few steps in PowerQuery if that's a viable option too. See [this](https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-stack-columns-vertically/372a32fe-4d17-4584-8226-c7366011e2aa) — JvdV, Jan 31 '23 at 07:24

VBasic2008 · Accepted Answer · 2023-02-01T04:36:29.847

To Column By Column

Option Explicit

Sub ToColumn()
    
    Const SRC_RANGE As String = "A2:C6"
    Const DST_FIRST_CELL As String = "E2"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim srg As Range: Set srg = ws.Range(SRC_RANGE)
    Dim Data(): Data = ToCol(srg, True)
    
    Dim drg As Range: Set drg = ws.Range(DST_FIRST_CELL).Resize(UBound(Data, 1))
    drg.Value = Data

End Sub

Function ToCol( _
    ByVal rg As Range, _
    Optional ByVal ByColumns As Boolean = False) _
As Variant()
    
    Dim sData(), srCount As Long, scCount As Long
    Dim dData(), IsNotSingleCell As Boolean
    
    With rg.Areas(1)
        
        srCount = .Rows.Count
        scCount = .Columns.Count
        
        If srCount * scCount = 1 Then
            ReDim dData(1 To 1, 1 To 1): dData(1, 1) = .Value
        Else
            sData = .Value
            ReDim dData(1 To srCount * scCount, 1 To 1)
            IsNotSingleCell = True
        End If
    
    End With
    
    If IsNotSingleCell Then
        
        Dim sr As Long, sc As Long, dr As Long
        
        If ByColumns Then
            For sc = 1 To scCount
                For sr = 1 To srCount
                    dr = dr + 1
                    dData(dr, 1) = sData(sr, sc)
                Next sr
            Next sc
        Else
            For sr = 1 To srCount
                For sc = 1 To scCount
                    dr = dr + 1
                    dData(dr, 1) = sData(sr, sc)
                Next sc
            Next sr
        End If
    
    End If
        
    ToCol = dData
        
End Function

(Legacy) Formula Solutions

By Columns

=INDEX($A$2:$C$6,
    MOD(ROWS($A$2:$A2)-1,ROWS($A$2:$A$6))+1,
    QUOTIENT(ROWS($A$2:$A2)-1,ROWS($A$2:$A$6))+1)

By Rows

=INDEX($A$2:$C$6,
    QUOTIENT(ROWS($A$2:$A2)-1,COLUMNS($A$2:$C$2))+1,
    MOD(ROWS($A$2:$A2)-1,COLUMNS($A$2:$C$2))+1)

By Columns

If the data starts in A1...

E1:E15  =MOD(ROW()-1,5)+1
F1:F15  =QUOTIENT(ROW()-1,5)+1
G1:G15  =INDEX($A$1:$C$5,MOD(ROW()-1,5)+1,QUOTIENT(ROW()-1,5)+1)

... but it doesn't.

I2:I16   =MOD(ROW()-2,5)+1
J2:J16  =QUOTIENT(ROW()-2,5)+1
K2       =INDEX($A$2:$C$6,
             MOD(ROWS($A$2:$A2)-1,ROWS($A$2:$A$6))+1,
             QUOTIENT(ROWS($A$2:$A2)-1,ROWS($A$2:$A$6))+1)

By Rows

If the data starts in A1...

M1:M15  =QUOTIENT(ROW()-1,3)+1
N1:N15  =MOD(ROW()-1,3)+1
O1:O15  =INDEX($A$1:$C$5,QUOTIENT(ROW()-1,3)+1,MOD(ROW()-1,3)+1)

... but it doesn't.

Q2:Q16  =QUOTIENT(ROW()-2,3)+1
R2:R16  =MOD(ROW()-2,3)+1
S2      =INDEX($A$2:$C$6,
            QUOTIENT(ROWS($A$2:$A2)-1,COLUMNS($A$2:$C$2))+1,
            MOD(ROWS($A$2:$A2)-1,COLUMNS($A$2:$C$2))+1)

score 1 · Answer 2 · answered Jan 31 '23 at 05:08

1

Use TOCOL() function.

=TOCOL(A1:C5,,TRUE)

Function reference:

TOCOL()

answered Jan 31 '23 at 05:08

Harun24hr

30,391
4
21
36

If you do not have `TOCOL()` then can try `=INDEX(FILTERXML("~~"&TEXTJOIN("",TRUE,$A$1:$C$5)&"~~","//s"),ROW(1:1))` – Harun24hr Jan 31 '23 at 05:20
Unfortunately the TOCOL() function is not available in my office version. – karesz55 Jan 31 '23 at 05:51
@karesz55 What is you Excel version? – Harun24hr Jan 31 '23 at 05:54
2016 (16.0.5378.1000) – karesz55 Jan 31 '23 at 06:19
@karesz55 Can you try above one I give to comment? – Harun24hr Jan 31 '23 at 06:45
of course , That was a first thing, then i saw that. And didn't workk. The result: "#NAME? – karesz55 Jan 31 '23 at 06:50
1

@Harun24hr Unfortunately, [TEXTJOIN](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c) needs at least Office 2019. – VBasic2008 Jan 31 '23 at 06:55
I tried on other PC with 2019 and it didn't work there either.. :\ – karesz55 Jan 31 '23 at 07:02
@karesz55 You have to enter it as an array formula i.e. confirm with `Ctrl,Shift+Enter` in the first cell, and then copy down (start pasting from the 2nd cell). – VBasic2008 Jan 31 '23 at 07:10

score 0 · Answer 3 · answered Jan 31 '23 at 05:37

0

You can try following formula (normally entered) in O365:

=INDEX($A$1:$C$5,0,CEILING(ROWS($A$1:A1)/5,1))

For a setup depicted as below:

You may have to use CTRL+SHIFT+ENTER if you have different version of Excel.

answered Jan 31 '23 at 05:37

shrivallabha.redij

5,832
1
12
27

1

I tried this formula, and didn't work for me. 1,12,13,14,15 row is okay But when 2, 22,23 would follow then te result is: "#value!" – karesz55 Jan 31 '23 at 05:55

score 0 · Answer 4 · answered Jan 31 '23 at 09:42

If you want the result still in the first column, something like the below image (before/after) :

==>

Sub test()
Dim rg As Range: Dim i As Integer

With ActiveSheet 'change as needed
    Set rg = .Range("A2", .Range("C2").End(xlDown)) 'change as needed
End With

For i = 1 To rg.Columns.Count - 1
    rg.End(xlDown).Offset(1, 0).Resize(rg.Rows.Count, 1).Value = _
    rg.Columns(1).Offset(0, i).Value: rg.Columns(1).Offset(0, i).ClearContents
Next

End Sub

the rg is the range of data (in the example case is from cell A2 to C6).
it loops using i as the offset value, and each loop it fill the last blank row with the offset range value.

Transform every all 5 rows to column

4 Answers4

To Column By Column

(Legacy) Formula Solutions

Function reference:

Linked