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

MrsGixxer

New Member
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
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
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
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
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top