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

MrsGixxer

New Member
Joined
Jul 20, 2018
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,755
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

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

Excel Workbook
ABCDEFGHIJKLM
1A1B1C1D1E1F1 B1C1D1E1F1
2A2B2C2D2E2F2
3A3B3C3D3E3F3A2B2C2D2E2F2
4A4B4C4D4E4F4
5A5B5C5D5E5F5A3B3C3D3E3F3
6A6B6C6D6E6F6
7A7B7C7D7E7F7A4B4C4D4E4F4
8
9A5B5C5D5E5F5
10
11A6B6C6D6E6F6
12
13A7B7C7D7E7F7
Blank Rows
 

MrsGixxer

New Member
Joined
Jul 20, 2018
Messages
4
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

New Member
Joined
Jul 20, 2018
Messages
4
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,755
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 20, 2018
Messages
4
Hi Peter!!

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

Renee
 

Forum statistics

Threads
1,077,991
Messages
5,337,591
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top