Through countif formula....=COUNTIF($E$7:$E$23,H7:H17), E7 COLUMN RANGE AND H COLUMN CRITERIA...
Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!
It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo
Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!
Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!
Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.
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?
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:
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 Sub3. 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.
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.
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: