HOW DO I? Copy a column of data to a new worksheet with spaces in between

[MrsGixxer]

I need some help. I am trying to take data in a column and reference it in another spreadsheet with a formula/function. The difference is the new data will need to be in the new spreadsheet with an empty row in between. With the amount of data I have, I need to be able to copy down the formula/function and not have to do any manual manipulation. Sorry if my example is not that great. I've been working on this for far too long and am exhausted.

From here -------------------------To here
A1 B1 C1 D1 E1 F1 ------------------A1
A2 B2 C2 D2 E2 F2 ------------------Blank Cell
A3 B3 C3 D3 E3 F3 ------------------A2
A4 B4 C4 D4 E4 F4 ------------------Blank Cell
A5 B5 C5 D5 E5 F5 ------------------A3
A6 B6 C6 D6 E6 F6 ------------------Blank Cell
A7 B7 C7 D7 E7 F7 ------------------A4

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean? Formula in H1 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A1	B1	C1	D1	E1	F1		A1	B1	C1	D1	E1	F1
2	A2	B2	C2	D2	E2	F2
3	A3	B3	C3	D3	E3	F3		A2	B2	C2	D2	E2	F2
4	A4	B4	C4	D4	E4	F4
5	A5	B5	C5	D5	E5	F5		A3	B3	C3	D3	E3	F3
6	A6	B6	C6	D6	E6	F6
7	A7	B7	C7	D7	E7	F7		A4	B4	C4	D4	E4	F4
8
9								A5	B5	C5	D5	E5	F5
10
11								A6	B6	C6	D6	E6	F6
12
13								A7	B7	C7	D7	E7	F7
Blank Rows

 

[MrsGixxer]

Thank you Peter that is amazing. Exactly what I was looking for. I will work it into my actual spreadsheet and let you know if I have any questions and how it goes.

 

[MrsGixxer]

Hi Peter,

Would you be able to explain to me how this is working? I understand some of it but am missing something because I am having difficulty making the necessary changes to make it work for my data set and new worksheet. Also, the columns I am copying over are coming from different parts of the data set spreadsheet and are not side by side. Sorry for any confusion. Thanks for your assistance.


Renee

 

[Peter_SSs]

Would you be able to explain to me how this is working? I understand some of it but am missing something ...

Take the formula in H4 of my example:
=IF(MOD(ROWS(H$1:H4),2)=0,"",INDEX(A$1:A$7,(ROWS(H$1:H4)-1)/2+1))
The formula counts the rows from H1:H4. That is, 4
Divide by 2 and look at the remainder (that's what MOD does). In this case the remainder when 4 is divided by 2 is 0, so the formula returns "", leaving nothing apparent in H4

Now look at the formula that is in H5
=IF(MOD(ROWS(H$1:H5),2)=0,"",INDEX(A$1:A$7,(ROWS(H$1:H5)-1)/2+1))
This time the remainder is 1 so the blue part of the formula is evaluated. It becomes
INDEX(A$1:A$7,(5-1)/2+1)
INDEX(A$1:A$7,4/2+1)
INDEX(A$1:A$7,3)
That is, the third cell in the range A1:A7 = A3

Also, the columns I am copying over are coming from different parts of the data set spreadsheet and are not side by side.

Then, instead of one formula copied across and down, you may need a separate formula in the top row of each column and only copy them down. Instead of A$1:A$7 you would need to put the range that should be pulling in to each particular column of the results.

 

[MrsGixxer]

Hi Peter!!

Thanks so much for the explanation, very informative. I am working on this today!

Renee