Skip to main content

How to repeat cell value x times in Excel?

Author: Xiaoyang Last Modified: 2020-04-24

For example, you have a list of values in Column A, and you want to repeat the values specific number of times based on the number times in Column B, as left screenshot shown, how could you achieve this in Excel?

 

Repeat cell values X times with formula and helper column

To repeat the cell values X times, you can insert some helper columns and then apply some simple formula, please do with following steps:

1. Insert a column to the left of column A, and type 1 in cell A2, see screenshot:

2. Then put this formula =A2 + C2 into A3, and drag the fill handle to the cell A6, see screenshot:

3. And then enter 1 in cell D2, and fill the number by dragging the Auto Fill to 12 which is the total number of the times number in column C, see screenshot:

4. Then enter this formula =VLOOKUP(D2,$A$1:$B$6,2) into cell E2 and copy down, you will get the following result:

  • Notes:
  • 1. In this formula, D2 indicates the first cell you are filled with the number sequence, and A1:B6 stands for the range of the first helper column and the original cell values that you need to repeat.
  • 2. After getting the repeated values, you can copy and paste them as values to any other place.

Copy and insert rows X times based on specific number easily

Normally, there is not a good method for copying and inserting rows multiple times except dealing with manually copy and insert. But, with Kutools for Excel's Duplicate Rows / columns based on cell value feature, you can solve this problem with ease. Click to download Kutools for Excel!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Repeat cell values X times with VBA code

If you consider the first method is difficult to understand, here, I can also introduce you VBA code to solve it.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Repeat cell values X times

Sub CopyData()
'Updateby Extendoffice
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
For Each Rng In InputRng.Rows
    xValue = Rng.Range("A1").Value
    xNum = Rng.Range("B1").Value
    OutRng.Resize(xNum, 1).Value = xValue
    Set OutRng = OutRng.Offset(xNum, 0)
Next
End Sub
3. Then press F5 key to run this code, and a prompt box will appear to remind you to select the range which contains the values and repeated times, see screenshot:

4. And click OK, another prompt box will pop out to let you select a cell to put the result, see screenshot:

5. Then click OK, and you will get the result that your selected values have been repeated specified times as you need.


Copy and insert cell values X times with an awesome feature

Sometimes, you may need to copy and insert the cell values x times based on the specific number, Kutools for Excel's Duplicate Rows / Columns based on cell value can help you quickly copy and insert the rows based on the number you specified.

Tips:To apply this Duplicate Rows / Columns based on cell value feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click Kutools > Insert > Duplicate Rows / Columns based on cell value, see screenshot:

2. In the Copy and insert rows & columns dialog box, select Copy and insert rows option in the Type section,  then select the data range you want to duplicate, and then specify the repeat time to copy and insert the rows, see screenshot:

4. Then, click Ok or Apply button, you will get the following result as you need:

Click to Download Kutools for Excel and free trial Now!

Best Office Productivity Tools

ūü§Ė Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions‚Ķ
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (46)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
It's very helpful.Thanks a ton.
This comment was minimized by the moderator on the site
MERCI !!!!!!!!
This comment was minimized by the moderator on the site
Really fantastic thank you
This comment was minimized by the moderator on the site
Gracias por este consejo. Me funcionó genial para una base de 4061 registros que se convirtieron en 146,196 filas. Saludos.
Rated 5 out of 5
This comment was minimized by the moderator on the site
Bonjour, je suis bloquée en VBA. J'ai besoin de faire la chose suivante : Répéter la ligne 2  qui est dans la feuille de calcul BDD  dans la feuille de calcul BDD 2 A partir de A2Autant de fois que c'est noté dans J2 (non fixé)Ensuite passer à la ligne suivante jusqu'à (ou tant que ???) la cellule en J soit vide
Comment j'écris ça ? 
Merci pour votre aide !
This comment was minimized by the moderator on the site
merhaba anlatım uygulama çok güzel fakat benim sorum,
bu uygulama sadece A sütun için uygulanmış örneğin E sütunda bulunan değer kadar A,B,C,D sütunlarını da aynı anda çoğaltmak istersem nasıl bir yol izlemeliyim. şimdiden teşekkürler
This comment was minimized by the moderator on the site
the vlookup one was a bit wonky but the macro worked like a charm! Thank you!
This comment was minimized by the moderator on the site
Great!Thanks a lot
This comment was minimized by the moderator on the site
We can find duplicates value without kutools....
Through countif formula....=COUNTIF($E$7:$E$23,H7:H17), E7 COLUMN RANGE AND H COLUMN CRITERIA...


0091 9004260229
This comment was minimized by the moderator on the site
thank u! the vlookup function worked!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations