Excel Copy/Paste Question

[bostonman]

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]

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.

[bostonman]

What is the N in 1+3N?

[CatalinaWOW]

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]

Multiple of 3 but 1 line shifted.  Here is an example:

Code: [Select]

old part number1084 2000 new part number old part number1084
old part number1085 2001 new part number old part number1085
old part number1086 2002 new part number 2003 new part number
old part number1087 2003 new part number old part number1087
old part number1088 2004 new part number old part number1088
old part number1089 2005 new part number 2006 new part number
old part number1090 2006 new part number old part number1090
old part number1091 2007 new part number old part number1091
old part number1092 2008 new part number 2009 new part number
old part number1093 2009 new part number old part number1093
old part number1094 2010 new part number old part number1094

To produce the 3^rd column, write the following formula in the first cell of the 3^rd 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.

[bostonman]

I need spaces between stuff.

So I need:

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

[bostonman]

Never mind, I got my answer... it was actually easier than I was making it out to be.


Thanks.

[rrinker]

 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.