Formula to take cells in A2:A100 and paste them in Column B leaving blank row in between (A2 in B2, A3 in B4, A4 in B6, etc)

[AlexK_]

Excel experts!! I need you help...

I have a long list of names that I want to copy over to another sheet, however, I want the names to be in every other row on the other sheet. Is there a way to do this with a formula, as the list will continue to grow and I want the other sheet to automatically pull in the new names with a blank row in between the names.

Everything I try changes the referenced cell by two since I am skipping a cell, where I want to skip a cell but have the referenced cell only increase by one.

Example dataset in Column A and example of goal in Column B

Thank you for the conversation and assistance in advance.
Alex

Name	Name with blank row in between
Luka Doncic	Luka Doncic
Shai Gilgeous-Alexander
Stephen Curry	Shai Gilgeous-Alexander
Jalen Brunson
Nikola Jokic	Stephen Curry
	Jalen Brunson
	Nikola Jokic

 

[dreid1011]

You could use something like this, but you need the next column to be blank.

Book1
	A	B	C
1	Red		Red
2	Orange
3	Yellow		Orange
4	Green
5	Blue		Yellow
6	Purple
7			Green
8
9			Blue
10
11			Purple
12
Sheet1

Cell Formulas
Range	Formula
C1:C12	C1	=TOCOL(A1:B6)&""
Dynamic array formulas.

 

[jolivanes]

Or put this in the Sheet module of the Sheet where the changes will occur
Change references as required

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
If Target.Column = 1 Then
    With sh2
    .Columns(1).ClearContents
    .Cells(1, 1).Resize(lr).Value = sh1.Cells(1, 1).Resize(lr).Value
        For i = lr To 2 Step -1
            .Cells(i, 1).Resize(1).Insert Shift:=xlDown
        Next i
    End With
End If
End Sub

 

[Coyotex3]

Another possible option based on your data set:

=TOCOL(HSTACK(A2:A6,IF(A2:A6<>"","")))

Book1
	A	B
1	Name	Name With Blank Cell
2	Luka Doncic	Luka Doncic
3	Shai Gilgeous Alexander
4	Stephen Curry	Shai Gilgeous Alexander
5	Jalen Brunson
6	Nikola Jokic	Stephen Curry
7
8		Jalen Brunson
9
10		Nikola Jokic
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=TOCOL(HSTACK(A2:A6,IF(A2:A6<>"","")))
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B
1	Name	Name with blank row in between
2	Luka Doncic	Luka Doncic
3	Shai Gilgeous-Alexander
4	Stephen Curry	Shai Gilgeous-Alexander
5	Jalen Brunson
6	Nikola Jokic	Stephen Curry
7
8		Jalen Brunson
9
10		Nikola Jokic
11
Data

Cell Formulas
Range	Formula
B2:B10	B2	=DROP(TOCOL(EXPAND(TOCOL(A2:A100,1),,2,"")),-1)
Dynamic array formulas.

 

[AlexK_]

Thank you all for the assistance and knowledge.

I went with the first post as it was the most straightforward and my data had blank cells in the column to the right. If it did not have blank cells in the column to the right, I could have used another formula shared above. I was not aware of the TOCOL array formula prior to this and it did exactly what I needed it to do.

Will definitely come back here to obtain and share Excel knowledge.
Alex

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[dreid1011]

You're welcome, and happy to help.