HELP: Adding data from Cells B1, B2, B3 etc. into COL F every other12 rows.

[dandipp]

Hello.

I have data in my excel sheet. A has the date/timetsamp. B has the data.

Now, I had to create the date/timestamp to show every 5 seconds so, now i have 12 rows because of the 5second interval.

For example: in E i now have the Date/Time showing every 5 seconds from 10:50:00 to 10:50:55 PM. Then starting again at 10:51:00 PM to 10:51:55 PM showing every 5 seconds.

Now i want the Data from B1, B2, B3, B4 and so on to go into F1 then 12 rows down F13 etc.


Did i make sense?

Thanks.

 

[h2so4]

(e-mail address removed) has brought this to us :

Hello.

I have data in my excel sheet. A has the date/timetsamp. B has the data.

Now, I had to create the date/timestamp to show every 5 seconds so, now i
have 12 rows because of the 5second interval.

For example: in E i now have the Date/Time showing every 5 seconds from
10:50:00 to 10:50:55 PM. Then starting again at 10:51:00 PM to 10:51:55 PM
showing every 5 seconds.

Now i want the Data from B1, B2, B3, B4 and so on to go into F1 then 12 rows
down F13 etc.


Did i make sense?

Thanks.

hello,

with a formula,

put the following formula in cell F1
=INDIRECT("B" & INT((ROW()-1)/12)+1)

then select the cells F1 to F12 and copy them to the cells F13 Fxx till
the end of your new data collection.

with a macro

to insert the macro
alt-F11
then insert module
then paste the following code

to execute the macro
alt-F8, then select the macro copyBtoF

'here starts the macro
Sub copyBtoF()
Dim i As Long
i = 1

While Cells(i, 2) <> ""
Cells((i - 1) * 12 + 1, 6) = Cells(i, 2): '6 for column F, 2 for
column B
i = i + 1
Wend

End Sub

'here ends the macro

 

[dandipp]

Good Morning,

I put the following in F1 =INDIRECT("B" & INT((ROW()-1)/12)+1)

when i copy it down column F it keeps giving me the data in B1.


Any ideas?

Also if i have a header in row 1 and row 2 and the data started on B3 would the code be different?

Thanks.

 

[dandipp]

(e-mail address removed) has brought this to us :








hello,



with a formula,



put the following formula in cell F1

=INDIRECT("B" & INT((ROW()-1)/12)+1)



then select the cells F1 to F12 and copy them to the cells F13 Fxx till

the end of your new data collection.



with a macro



to insert the macro

alt-F11

then insert module

then paste the following code



to execute the macro

alt-F8, then select the macro copyBtoF



'here starts the macro

Sub copyBtoF()

Dim i As Long

i = 1



While Cells(i, 2) <> ""

Cells((i - 1) * 12 + 1, 6) = Cells(i, 2): '6 for column F, 2 for

column B

i = i + 1

Wend



End Sub



'here ends the macro

The macro works but i cannot paste the formula =INDIRECT("B" & INT((ROW()-1)/12)+1) into F1 and copy it down unless there is a special way to paste a formula is there a way to have it start at B3?

 

[dandipp]

ok i have it working.

if i want to edit the macro how can i have it so for the 12 rows its not putting the data in have it put in the data from B1 then the next 12 rows B2 data to be in the next 12 and so on...

So it will enter B1 data into F1-F12 B2 data into F13-24....

 

[h2so4]

Good Morning,

I put the following in F1 =INDIRECT("B" & INT((ROW()-1)/12)+1)

when i copy it down column F it keeps giving me the data in B1.


Any ideas?

Also if i have a header in row 1 and row 2 and the data started on B3 would
the code be different?

Thanks.

indeed the formula assumes that the first row to copy is row 1

when you have your first row to copy in row 3 the formula should be

=INDIRECT("B" & INT((ROW()-3)/12)+3)

You now have the formula in F3 and nothing in F4 to F15. What you want
to do is to repeat this group of cells as many time as needed down to
the column F. To do that, as mentionned in my initial answer just
select F3 to F15, do ctrl-C (to copy) then select F16 to Fxx xx=last
line you want to copy the set of cells and then Ctrl-V (to paste).

 

[h2so4]

h2so4 brought next idea :below the adapted maco, if you want to use a macro.

with a macro

to insert the macro
alt-F11
then insert module
then paste the following code

to execute the macro
alt-F8, then select the macro copyBtoF

'here starts the macro
Sub copyBtoF()
Dim i As Long
fl=3 :' first line with data to copy
i=firstline

While Cells(i, 2) <> ""
Cells((i - fl) * 12 + fl, 6) = Cells(i, 2): '6 for column F, 2 for
column B
i = i + 1
Wend

End Sub

'here ends the macro

 

[h2so4]

h2so4 was thinking very hard :

h2so4 brought next idea :

below the adapted maco, if you want to use a macro.

with a macro

to insert the macro
alt-F11
then insert module
then paste the following code

to execute the macro
alt-F8, then select the macro copyBtoF

'here starts the macro
Sub copyBtoF()
Dim i As Long
fl=3 :' first line with data to copy
i=firstline

While Cells(i, 2) <> ""
Cells((i - fl) * 12 + fl, 6) = Cells(i, 2)
'6 for column F, 2 for column B
i = i + 1
Wend

End Sub

'here ends the macro

 

[dandipp]

Thank You.
ok from the new macro. Is there a way to add the data from F3 into the next 12 rows and then the data from B14 into the next 12 rows and so on?

Thanks again for your help!

 

[dandipp]

h2so4 was thinking very hard :




below the adapted maco, if you want to use a macro.



with a macro



to insert the macro

alt-F11

then insert module

then paste the following code



to execute the macro

alt-F8, then select the macro copyBtoF



'here starts the macro

Sub copyBtoF()

Dim i As Long

fl=3 :' first line with data to copy

i=firstline



While Cells(i, 2) <> ""

Cells((i - fl) * 12 + fl, 6) = Cells(i, 2)

'6 for column F, 2 for column B

i = i + 1

Wend



End Sub



'here ends the macro

Is there a way to edit this macro to enter in the data from B3 into F3:F15 data in B4 into F16:F26 and so on?

 

[h2so4]

Thank You.
ok from the new macro. Is there a way to add the data from F3 into the next
12 rows and then the data from B14 into the next 12 rows and so on?

Thanks again for your help!

sorry I didn't understand your request that way.

just put the formula in F3

=INDIRECT("B" & INT((ROW()-3)/12)+3)
and copy to all the others cells in the coloumn F from row 4

 

[dandipp]

No quick answer to my last question? It is almost 5pm EST. sorry i am not trying to rush anyone. If you can help that would be awesome i am now searching the internet on how to fill in the 11 empty rows which need to be filled in with the data from every 12th row.

F3:F14 should have data from B3. F15:F26 should have data from B4.

I know with the macro you just gave me i have to make a small edit i just cannot find out where.

 

[dandipp]

I am sorry i was replying as you replied. I have over 11,000 lines. thats why the copy and paste method is going to take awhile.

 

[dandipp]

No the macro is what i asked for and is correct when i first requested this answer. I am an idiot and forgot about the 11 Rows without data and i need to paste B3 into F3:F14, B4 into F15:F26 and so forth

 

[h2so4]

(e-mail address removed) has brought this to us :

No quick answer to my last question? It is almost 5pm EST. sorry i am not
trying to rush anyone. If you can help that would be awesome i am now
searching the internet on how to fill in the 11 empty rows which need to be
filled in with the data from every 12th row.

F3:F14 should have data from B3. F15:F26 should have data from B4.

I know with the macro you just gave me i have to make a small edit i just
cannot find out where.

here is the new macro

'here starts the macro
Sub copyBtoF()
Dim i As Long, j As Long
fl = 3: ' first line with data to copy
i = fl

While Cells(i, 2) <> ""
For j = 0 To 11
Cells((i - fl) * 12 + fl + j, 6) = Cells(i, 2)
'6 for column F, 2 for column B
Next j
i = i + 1
Wend

End Sub

 

[dandipp]

You are so awesome!!! Thank You!!!

 

[h2so4]

(e-mail address removed) pretended :

You are so awesome!!! Thank You!!!

just for your info, ...

http://blog.contextures.com/archives/2011/08/31/quickly-copy-excel-formula-down/