Copy 1 cells from 4 Row's in 4 different Columns to 4 Rows of 1 Column

[Legacy 138945]

I’m looking for <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> Code to address the following situation.
I have a table that has an unknown number of rows.
Every 4 rows is another (set of different) record(s).
I need to copy K2 to O2, L3 to O3, M4 to O4 and N5 to O5 into the New Column ‘O’.
That needs to be done until the end of the Table.
Note: these cell contain numeric values with decimals and may be 0.00.
If possible, please code it as simply as possible to allow for easy modifications to change the Range(s).
(Creating the Column ‘O’ in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code is optional; I could do it manually, if necessary)

Thank you in advance for you time and effort.

 

[MarcelBeug]

No need for VBA.

Enter in O2 and copy down:

=OFFSET(K2,,MOD(ROW(O2)-ROW($O$2),4))

 

[Legacy 138945]

Thank you for answering so quickly.


Your solution worked great!


I will be able to copy the original Spreadsheet to another one using the 'values' only option and then load my Table from there with just the 'O' Column containing the numeric values needed.


Thanks again for your expertise!

 

[Legacy 138945]

I looked this up. There is not much information that I could find on using a MOD within a offset.
I'm having trouble understand how exactly it actually works and would like to know.
If you have the time could you please explain how it functions, commas and all.

Thank you for you help.

 

[MarcelBeug]

Sure:

With OFFSET you can refer to a cell (or range) as the relative position against a reference cell as the first argument, in this case K2.
Next argument of OFFSET is the number of rows from the reference cell, in this case none (empty): on row 2 you need data from row 2.
Next argument of OFFSET is the number of columns from the reference cell, in this case dynamic: row 2 --> 0, row 3 --> 1, row 4 --> 2, row 5 --> 3, row 6 --> 0 etc.
(there are 2 more optional arguments for height and width that are not relevant in this case).

The MOD function will take care of creating sequences of 0, 1, 2, 3, 0, 1, 2, 3 etcetera:
In O2: MOD(0, 4) = 0
In O3: MOD(1, 4) = 1
In O4: MOD(2, 4) = 2
In O5: MOD(3, 4) = 3
In O6: MOD(4, 4) = 0
In O7: MOD(5, 4) = 1
In O8: MOD(6, 4) = 2
In O9: MOD(7, 4) = 3

The first argument of MOD is the number of rows from O2, so 0 in O2, 1 in O3, etcetera.
This is entered as ROW(O2)-ROW($O$2) to ensure it will still work when data would be moved.
E.g. if you insert a line above row 2, O2 becomes O3 and this part of the formula will be auto-adjusted to ROW(O3)-ROW($O$3).

Recap:
In cell O2 you get the value from the cell that is MOD(ROW(O2;$O$2) = 0,4) = 0 columns from K2 which is K2 itself.
In cell O3 you get the value from the cell that is MOD(ROW(O3;$O$2) = 1,4) = 1 column from K3 which is L3.
Etcetera.

I guess this should be enough clarification.

Cheers,
Marcel

 

[Legacy 138945]

Thank you so much for that excellent explanation. I was trying to figure it out but was not successful. Now I have a clear understanding of what is going on.

Thanks so much!

 

[MarcelBeug]

You're welcome