Sometimes we want to repeat a word or a value X number of times in a column or row in order to create a duplicate in Excel. We can do this with the traditional copy and paste function, but it takes a lot of time and manually copying and pasting can lead to errors, such as pasting more times than we want. This post introduces a trick on duplicating cell values and sorting them easily in a column in Excel.
Repeat Cell Value X Times with Excel Formula
We cannot create a formula to achieve our goal with only current providing values. To repeat cell values X times properly in Excel, we need to create a helper column to perform an intermediate calculation to simplify the main formula. Creating a helper column allows us to break down complex calculations into smaller, more manageable steps.
Here are the steps:
Step1: Create a helper column to perform an intermediate calculation. In this example, create a helper column containing the number “1” in the first cell A2, and for cell A3, use a simple formula A2+C2, and A4=A3+C3, etc.
Step2: Create another helper column that sorts the numbers 1 through 11. We end the sort with the number “11” because the last SUM value of the first helper column is “11“.
Step3:. In cell F2, build a formula with the Excel VLOOKUP function to repeat the cell value X times. In F2, enter the formula:
=VLOOKUP(D2,$A$2:$B$5,2,TRUE)
Step4: Drag the handle down to copy the formula. Cell values are repeated correctly with reference to the number of repetitions. Stop copying when it returns a “0“.
Excel VLOOKUP Function Explanation
The Excel VLOOKUP Function Syntax:
=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])
The formula we build in this example is:
=VLOOKUP(D2,$A$2:$B$5,2,TRUE)
In the above formula, the lookup value is “D2”, a number in the second helper column; the lookup range is “$A$2:$B$5”, within which the “first helper column” is used to search for the lookup value, and the “cell value column” is used to return the matching value when there is a mapping. The range_lookup value is “TRUE” to perform an approximate match.
Excel VLOOKUP “approximate match” can return an approximate match when the lookup value is not found in the lookup range. It returns the maximum value of all values less than the lookup value.
In this example, the number in the first helper column records the position of each cell value in the new list that lists the repeating values. For example, for the first cell value “AAA”, the starting position is row “1“, then for the second cell value “BBB”, using the starting position “1” plus the number of repetitions “5“, the final result is “6“, which is calculated by the formula “A2 + C2“. For the third cell value “CCC”, the starting position is calculated by the formula “A3+C3“, with a starting row number of 9. We stop the calculation when an empty cell is encountered, and the position number is “11”.
With the help of the two helper columns, we can use VLOOKUP “approximate match” mode to return the cell value X times through the VLOOKUP.
For example, for the first lookup value “1” (in the second helper column), it can be found in the A2 of the lookup column, so “AAA” in B2 will be correctly returned to cell E2. And for the second lookup value “2”, there is no such number in the lookup range, so the VLOOKUP formula will look for the maximum value less than the number “2“, and the result is “1“, so the value of the mapped cell “AAA” is returned by the VLOOKUP formula.
For the values “1” to “5”, the value returned is “AAA”, so “AAA” is repeated 5 times in the new column. The VLOOKUP formula correctly returns cell value X times according to the rule. The formula will stop when no cell values are returned.