### Author Topic: Excel Copy/Paste Question  (Read 1116 times)

0 Members and 1 Guest are viewing this topic.

#### bostonman

• Super Contributor
• Posts: 2021
• Country:
##### Excel Copy/Paste Question
« on: April 06, 2020, 04:15:11 pm »
I'm in need of copying something to every third line in Excel. So far adding spaces is easy because I have a macro that adds X spaces between rows (I can edit the macro to change the number).

Currently column A has a part number in every third row (starting at A1, next is in A4, etc..) and column B has the same (but starting in B2, next is B5, etc..)

I need to make A2 equal to B2, copy that formula () all the way down, and not delete what is in A4, A7, etc...

Can someone give me an idea on how to do this?

#### RoGeorge

• Super Contributor
• Posts: 6617
• Country:
##### Re: Excel Copy/Paste Question
« Reply #1 on: April 06, 2020, 04:23:49 pm »
Make an intermediary column with an if formula in every cell.  If the line number is of form 1+3n, then you copy from next row of column B, else you copy from the same row of column A.
« Last Edit: April 06, 2020, 04:26:33 pm by RoGeorge »

#### bostonman

• Super Contributor
• Posts: 2021
• Country:
##### Re: Excel Copy/Paste Question
« Reply #2 on: April 07, 2020, 02:11:50 am »
What is the N in 1+3N?

#### CatalinaWOW

• Super Contributor
• Posts: 5403
• Country:
##### Re: Excel Copy/Paste Question
« Reply #3 on: April 07, 2020, 05:23:45 am »
What is the N in 1+3N?

Depending on your Excel foo the line number, or a reference to yet another hidden column which has index numbers (set up as 1 in line one, and 1+ cell above for the rest).

#### RoGeorge

• Super Contributor
• Posts: 6617
• Country:
##### Re: Excel Copy/Paste Question
« Reply #4 on: April 07, 2020, 06:37:06 am »
Multiple of 3 but 1 line shifted.  Here is an example:

Code: [Select]
old part number1084 2000 new part number old part number1084old part number1085 2001 new part number old part number1085old part number1086 2002 new part number 2003 new part numberold part number1087 2003 new part number old part number1087old part number1088 2004 new part number old part number1088old part number1089 2005 new part number 2006 new part numberold part number1090 2006 new part number old part number1090old part number1091 2007 new part number old part number1091old part number1092 2008 new part number 2009 new part numberold part number1093 2009 new part number old part number1093old part number1094 2010 new part number old part number1094
To produce the 3rd column, write the following formula in the first cell of the 3rd column:
Code: [Select]
=IF(MOD(ROW(),3)=0,B2,A1)press enter, then double click on the lower right corner of the cell to autocomplete the formula to the next cells of the same column, or alternatively drag the formula down manually.  Just an example, the formula can be written in many ways, adjust it to your needs.  row() returns the row number of a cell, and mod(a, b) is the modulo function, it returns the remainder of a divided by b.  My list separator is comma ',', if yours is something else, e.g. the semicolon character ';' , then in the above formula replace all comma characters with a semicolon character.

If the results must be back in the column A, either make a new copy, or copy the result from the 3rd column and paste it "As Values" over the first column, your choice.
« Last Edit: April 07, 2020, 06:44:12 am by RoGeorge »

#### bostonman

• Super Contributor
• Posts: 2021
• Country:
##### Re: Excel Copy/Paste Question
« Reply #5 on: April 07, 2020, 01:52:38 pm »
I need spaces between stuff.

So I need:

number 1
alt number 1
space
space
number 2
alt number 2
etc....

#### bostonman

• Super Contributor
• Posts: 2021
• Country:
##### Re: Excel Copy/Paste Question
« Reply #6 on: April 07, 2020, 03:35:01 pm »
Never mind, I got my answer... it was actually easier than I was making it out to be.

Thanks.

#### rrinker

• Super Contributor
• Posts: 2046
• Country:
##### Re: Excel Copy/Paste Question
« Reply #7 on: April 07, 2020, 03:53:36 pm »
New versions of Excel are amazing with repetitive tasks - if you do a few, it makes very educated guess as to what you intend and suggests automatically implementing that same pattern over the rest of the range.

Smf