I want to copy data from a row (or column) to a new row (or column) but skip
a fixed number of cells between each data point, e.g.
A B C D E F G H I J
Row 1, 10 12 14 10 11 ( five data points in five cells) to be copied to

Row 2, 10 12 14 10 11

In this case data would skip one cell but my need is often for 2,3 or even
four cells.
Any help would be much appreciated.

For copying values in Row 1 to Row 2 and skipping every other value try in A2
and copy across to B2, C2...
=INDIRECT("R1C"&(COLUMN()-1)*2+1,0)

You can change 2 to 3 or 4 to get every third or fourth value.

For columns use for copying values in Col 1 in B1 and copy down
=INDIRECT("R"&(ROW()-1)*2+1&"C1",0)

"hans L" wrote:

I want to copy data from a row (or column) to a new row (or column) but skip
a fixed number of cells between each data point, e.g.
A B C D E F G H I J
Row 1, 10 12 14 10 11 ( five data points in five cells) to be copied to

Row 2, 10 12 14 10 11

In this case data would skip one cell but my need is often for 2,3 or even
four cells.
Any help would be much appreciated.

Thanks for your response. I've tried your suggestion but that does not work.
With the values 10,12,14,10,11 in cells A1,B1,C1,D1,and E1 applying your
INDIRECT function returns 10,14,11,0,0 in cells A2,B2,C2,D2,E2. The result I
am looking for is 10 in A2, 12 in C2, 14 in E2, 10 in G2, etc. The function
needs to leave one (or two) blank cell(s) between the returned values.
I have also tried to apply the function to values in columns but have not
had any results with that either.
Any other suggestions?
Thanks in advance.


"Sheeloo" wrote:

For copying values in Row 1 to Row 2 and skipping every other value try in A2
and copy across to B2, C2...
=INDIRECT("R1C"&(COLUMN()-1)*2+1,0)

You can change 2 to 3 or 4 to get every third or fourth value.

For columns use for copying values in Col 1 in B1 and copy down
=INDIRECT("R"&(ROW()-1)*2+1&"C1",0)

"hans L" wrote:

I want to copy data from a row (or column) to a new row (or column) but skip
a fixed number of cells between each data point, e.g.
A B C D E F G H I J
Row 1, 10 12 14 10 11 ( five data points in five cells) to be copied to

Row 2, 10 12 14 10 11

In this case data would skip one cell but my need is often for 2,3 or even
four cells.
Any help would be much appreciated.

I misunderstood your requirement.

Try
=IF(MOD(COLUMN(),2),INDIRECT("R1C"&(COLUMN()-1)/2+1,0),"")
in A2 and copy across to B2, C2...



"hans L" wrote:

Thanks for your response. I've tried your suggestion but that does not work.
With the values 10,12,14,10,11 in cells A1,B1,C1,D1,and E1 applying your
INDIRECT function returns 10,14,11,0,0 in cells A2,B2,C2,D2,E2. The result I
am looking for is 10 in A2, 12 in C2, 14 in E2, 10 in G2, etc. The function
needs to leave one (or two) blank cell(s) between the returned values.
I have also tried to apply the function to values in columns but have not
had any results with that either.
Any other suggestions?
Thanks in advance.


"Sheeloo" wrote:

For copying values in Row 1 to Row 2 and skipping every other value try in A2
and copy across to B2, C2...
=INDIRECT("R1C"&(COLUMN()-1)*2+1,0)

You can change 2 to 3 or 4 to get every third or fourth value.

For columns use for copying values in Col 1 in B1 and copy down
=INDIRECT("R"&(ROW()-1)*2+1&"C1",0)

"hans L" wrote:

I want to copy data from a row (or column) to a new row (or column) but skip
a fixed number of cells between each data point, e.g.
A B C D E F G H I J
Row 1, 10 12 14 10 11 ( five data points in five cells) to be copied to

Row 2, 10 12 14 10 11

In this case data would skip one cell but my need is often for 2,3 or even
four cells.
Any help would be much appreciated.

Magic! Works like a charm. I also tried to apply the function to skip two
blank cells between values and although the values appear in the intended
cells there are adjacent cells that show #REF! I could just delete that but
there's apparently something wrong with the way I modified the function to
=IF(MOD(COULUMN(),3),INDIRECT("R1C"&(COULUMN()-1/3+1,0),"").
And what would the formula look like when the data is in coulumns rather
than rows?
Thanks very much.

"Sheeloo" wrote:

I misunderstood your requirement.

Try
=IF(MOD(COLUMN(),2),INDIRECT("R1C"&(COLUMN()-1)/2+1,0),"")
in A2 and copy across to B2, C2...



"hans L" wrote:

Thanks for your response. I've tried your suggestion but that does not work.
With the values 10,12,14,10,11 in cells A1,B1,C1,D1,and E1 applying your
INDIRECT function returns 10,14,11,0,0 in cells A2,B2,C2,D2,E2. The result I
am looking for is 10 in A2, 12 in C2, 14 in E2, 10 in G2, etc. The function
needs to leave one (or two) blank cell(s) between the returned values.
I have also tried to apply the function to values in columns but have not
had any results with that either.
Any other suggestions?
Thanks in advance.


"Sheeloo" wrote:

For copying values in Row 1 to Row 2 and skipping every other value try in A2
and copy across to B2, C2...
=INDIRECT("R1C"&(COLUMN()-1)*2+1,0)

You can change 2 to 3 or 4 to get every third or fourth value.

For columns use for copying values in Col 1 in B1 and copy down
=INDIRECT("R"&(ROW()-1)*2+1&"C1",0)

"hans L" wrote:

I want to copy data from a row (or column) to a new row (or column) but skip
a fixed number of cells between each data point, e.g.
A B C D E F G H I J
Row 1, 10 12 14 10 11 ( five data points in five cells) to be copied to

Row 2, 10 12 14 10 11

In this case data would skip one cell but my need is often for 2,3 or even
four cells.
Any help would be much appreciated.

You were close. Try
=IF(MOD(COLUMN(),3)=1,INDIRECT("R1C"&(COLUMN()-1)/3+1,0),"")

For skipping one cell, =1 clause is not required since MOD gives either 1 or
0. For two or more MOD returns 0, 1, 2, ... so you need the clause.

For columns try
=IF(MOD(ROW(),3)=1,INDIRECT("R"&(ROW()-1)/3+1&"C1",0),"")

essentially you are building the address of the cell you want in R1C1 form
["R"&(ROW()-1)/3+1&"C1"] and then wrapping it in INDIRECT to get the value in
that cell. You use the else path of IF to skip the cells.

Pl. mark 'YES' if you got what you were looking for.



"hans L" wrote:

Magic! Works like a charm. I also tried to apply the function to skip two
blank cells between values and although the values appear in the intended
cells there are adjacent cells that show #REF! I could just delete that but
there's apparently something wrong with the way I modified the function to
=IF(MOD(COULUMN(),3),INDIRECT("R1C"&(COULUMN()-1/3+1,0),"").
And what would the formula look like when the data is in coulumns rather
than rows?
Thanks very much.

"Sheeloo" wrote:

I misunderstood your requirement.

Try
=IF(MOD(COLUMN(),2),INDIRECT("R1C"&(COLUMN()-1)/2+1,0),"")
in A2 and copy across to B2, C2...



"hans L" wrote:

Thanks for your response. I've tried your suggestion but that does not work.
With the values 10,12,14,10,11 in cells A1,B1,C1,D1,and E1 applying your
INDIRECT function returns 10,14,11,0,0 in cells A2,B2,C2,D2,E2. The result I
am looking for is 10 in A2, 12 in C2, 14 in E2, 10 in G2, etc. The function
needs to leave one (or two) blank cell(s) between the returned values.
I have also tried to apply the function to values in columns but have not
had any results with that either.
Any other suggestions?
Thanks in advance.


"Sheeloo" wrote:

For copying values in Row 1 to Row 2 and skipping every other value try in A2
and copy across to B2, C2...
=INDIRECT("R1C"&(COLUMN()-1)*2+1,0)

You can change 2 to 3 or 4 to get every third or fourth value.

For columns use for copying values in Col 1 in B1 and copy down
=INDIRECT("R"&(ROW()-1)*2+1&"C1",0)

"hans L" wrote:

I want to copy data from a row (or column) to a new row (or column) but skip
a fixed number of cells between each data point, e.g.
A B C D E F G H I J
Row 1, 10 12 14 10 11 ( five data points in five cells) to be copied to

Row 2, 10 12 14 10 11

In this case data would skip one cell but my need is often for 2,3 or even
four cells.
Any help would be much appreciated.