formula to repeat column A text after every 3 rows of columnB?

genetist

Board Regular
Joined
Mar 29, 2013
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
red1
green2
3
red
4
5
6
green
7
8
9
red
10
11
12
green

<tbody>
</tbody>
hi to all
I have data in column A that I want to repeat/insert after every 3 rows in column B. I used REPT function but it is adding text horizontally but not the way I want. I want to insert them like in column B of above table. is there any other formula in excel to do this? any help in this regard will be highly appreciated
Thanks in advance

<tbody>
</tbody>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Unknown
AB
1
2red 
3green
4blue
5red
6
7
8
9green
10
11
12
13blue
14
15
16
17red
18
19
20
21green
22
23
24
25blue
26
Sheet6
Cell Formulas
RangeFormula
B2=IF(MOD(ROWS(B$2:B2),4)=0,INDEX($A$2:$A$16,MOD(COUNTIF(B$1:B1,"<>"),COUNTA($A$2:$A$16))+1),"")
 
Last edited:
Upvote 0
I have data in column A that I want to repeat/insert after every 3 rows in column B.
I am unclear what you are after here. You used the word "insert" above... is there, or could there be, existing data in Column B? If not, then Nishant94 gave you probably what you are looking for. Howere, if so, then a formula cannot be used in Column B (a cell cannot have both data and a formula in them at the same time), so your output would have to go in some other column. Or, if you really meant you wanted to insert the values in Column A in between the existing values in Column B, then you will need to use a macro to do that. So, what is in Column B and where is the output to go?
 
Upvote 0
Dear Rick

Thank you for your reply on my question. Yes I have data in column B and I want to insert my column A text after every 3 rows in column B and no problem if my output is there in any another column.
Thanks in advance

Regards,
 
Upvote 0
Yes I have data in column B and I want to insert my column A text after every 3 rows in column B and no problem if my output is there in any another column.
While I am reasonably sure there is a formula solution, I cannot think of one; however, I do have a macro solution for you. The output for this macro is Column C however if you change the red C1 to B1, the output will overwrite your original data in Column B with the output you want there instead.
Code:
[table="width: 500"]
[tr]
	[td]Sub RepeatTextEveryThreeRows()
  Dim D As Long, T As Long, Data As Variant, Txt As Variant
  Txt = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  Data = Application.Transpose(Range("B1", Cells(Rows.Count, "B").End(xlUp)).Value)
  Do While D < UBound(Data)
    D = D + 1
    If (D Mod 3) = 0 Then
      If IsArray(Txt) Then
        T = (T + 1) Mod UBound(Txt)
        Data(D) = Data(D) & "|" & Txt(IIf(T = 0, UBound(Txt), T), 1)
      Else
        Data(D) = Data(D) & "|" & Txt
      End If
    End If
  Loop
  Data = Split(Join(Data, "|"), "|")
  Range("[B][COLOR="#FF0000"]C1[/COLOR][/B]").Resize(UBound(Data) + 1) = Application.Transpose(Data)
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (RepeatTextEveryThreeRows) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
Dear Rick
Good Afternoon

Thanks lot for your help and your macro code is working like charm. I want to know little bit more, is it if want to add my column A values twice like this
red1
green2
red
red
3
4
green
green
4
5

<tbody>
</tbody>
 
Upvote 0
Thanks lot for your help and your macro code is working like charm. I want to know little bit more, is it if want to add my column A values twice like this
red1
green2
red
red
3
4
green
green
4
5

<tbody>
</tbody>
Here is my code modified to do repeats... as many repeats as you would like, simply change the red highlighted number.
Code:
[table="width: 500"]
[tr]
	[td]Sub RepeatTextEveryThreeRows()
  Dim D As Long, T As Long, Repeats As Long, Data As Variant, Txt As Variant
  Repeats = [B][COLOR="#FF0000"][SIZE=4]2[/SIZE][/COLOR][/B]
  Txt = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  Data = Application.Transpose(Range("B1", Cells(Rows.Count, "B").End(xlUp)).Value)
  Do While D < UBound(Data)
    D = D + 1
    If (D Mod 3) = 0 Then
      If IsArray(Txt) Then
        T = (T + 1) Mod UBound(Txt)
        Data(D) = Data(D) & "|" & Application.Rept(Txt(IIf(T = 0, UBound(Txt), T), 1) & "|", Repeats)
      Else
        Data(D) = Data(D) & "|" & Application.Rept(Txt & "|", Repeats)
      End If
      Data(D) = Left(Data(D), Len(Data(D)) - 1)
    End If
  Loop
  Data = Split(Join(Data, "|"), "|")
  Range("C1").Resize(UBound(Data) + 1) = Application.Transpose(Data)
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Dear Rick
Good Evening

Thanks lot for your help and your code worked like charm
Thanks lot for your help
Genetist
Regards,
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,503
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top