Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Formular to copy data stored every 9 rows

Copper Contributor

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

@VWXXX 

=INDIRECT(ADDRESS(1+(ROW(A1)-ROW($A$1))*9,1,,,"worksheet B"))

You can apply this formula in worksheet A.

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!

@VWXXX 

=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.

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

@VWXXX 

=INDIRECT(ADDRESS(1+(ROW(A1)-ROW($A$1))*9,1,,,"worksheet B"))

You can apply this formula in worksheet A.

View solution in original post