SOLVED

Formular to copy data stored every 9 rows

Copper Contributor

Formular to copy data stored every 9 rows

Hello there,

I'm facing the following issue:
I have 2 worksheets and would like to copy the data from worksheet B into worksheet A.

The problem is, that the relevant data in worksheet B is stored with gaps of 9 rows and i don't know how i can copy this data. Copying by hand is not an option since there are thousands of cells to copy.

It looks like this (x are the respective cells to copy the relevant data from B into):

A:

x x x x....

B:

relevant data

.

.

.

relevant data

.

.

.

relevant data

.....

Is there any way to tell excel to copy the cell of every 9th row (of worksheet B) into adjacent cells in worksheet A? Basically, I need to address and automatically copy cells A1, A10, A19, A28.....and so on from worksheet B into A.

Any help would be very appreciated!

4 Replies
best response confirmed by VWXXX (Copper Contributor)
Solution

Re: Formular to copy data stored every 9 rows

You can apply this formula in worksheet A.

Re: Formular to copy data stored every 9 rows

Thank you so much! That worked! Unfortunately i noticed sometimes the gap is 7 or 8 every now and then. So I fear I still need to edit the formular from time to time. But this definately saves time, thanks again!

Re: Formular to copy data stored every 9 rows

=INDEX('worksheet B'!\$A\$1:\$A\$100,SMALL(IF(NOT(ISBLANK('worksheet B'!\$A\$1:\$A\$100)),ROW('worksheet B'!\$A\$1:\$A\$100)),ROW(A1)))

You are welcome. If you have irregular gaps between the entries you can apply this formula.

Re: Formular to copy data stored every 9 rows

Thank you, the cells in between the relevant data are not blank unfortunately, so this won't work for me.
1 best response

Accepted Solutions
best response confirmed by VWXXX (Copper Contributor)
Solution