Repeat Values Like Desired Output Column

[ExcelToDAX]

How can I repeat the columns like in Column A Desired Output? I want it to repeat like the following screenshot below:

Current

NAME1

Column1

Test2

Row3

Cell4

NAME2

Column1

Test2

Row3

Cell4

NAME3

Column1

Test2

Row3

Cell4

NAME4

Column1

Test2

Row3

Cell4

 

[Joe4]

I am not quite clear.
Between columns A and B, which one is the column you are STARTING with, and which column is what you want as your expected output?
And does the pattern ALWAYS repeat after 5 rows, like shown in your example?

 

[Cubist]

If it does not repeat every 5 rows, how can you tell which rows to repeat?

 

[ExcelToDAX]

If it does not repeat every 5 rows, how can you tell which rows to repeat?

It does repeat every 5 times

 

[Cubist]

One option:

Book1
	A	B
1
2	NAME1	NAME1
3	NAME1	Column1
4	NAME1	Test2
5	NAME1	Row3
6	NAME1	Cell4
7	NAME2	NAME2
8	NAME2	Column1
9	NAME2	Test2
10	NAME2	Row3
11	NAME2	Cell4
12	NAME3	NAME3
13	NAME3	Column1
14	NAME3	Test2
15	NAME3	Row3
16	NAME3	Cell4
17	NAME4	NAME4
18	NAME4	Column1
19	NAME4	Test2
20	NAME4	Row3
21	NAME4	Cell4
Sheet1

Cell Formulas
Range	Formula
A2:A21	A2	=INDEX(B2:B21,5*ROUNDUP(SEQUENCE(ROWS(B2:B21))/5,0)-4)
Dynamic array formulas.

 

[Alex Blakenburg]

Given your handle and history, any reason you are not using Power Query to do it ?

No manual M code changes made in the below.
I did use the Heading "Current" in the original data source which may not suit.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 5), type number),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Modulo", "Name", each if [Modulo] = 0 then [Current] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Name", "Current", "Index", "Modulo"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Reordered Columns",{"Name", "Current"})
in
    #"Removed Other Columns"