Repeat values across cells
This article explains how to repeat specific values based on a table, the table contains the items to be repeated and how many times they will be repeated.
The array formula in cell A6 utilizes the table and repeats the values until the condition is met.
There is also a section below that explains how to repeat values in sequence based on corresponding numbers.
Table of Contents
I failed to find the right article in your blog and therefore I want to ask you in newest post. So I have a table similar to this:
A 5
B 2
C 1
D 4
Is it possible with a formula to generate a list like this:
A
A
A
A
A
B
B
C
D
D
D
D
Thank you in advance!
Array formula in cell A6:
How to create an array formula
- Double press with left mouse button on cell A6 so the prompt appears.
- Copy and paste the array formula above to the cell.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- Release all keys.
You can check using the formula bar that you did above steps right, Excel tells you if a cell contains an array formula by surrounding the formula with a beginning and ending curly brackets, like this: {=array_formula}.
Don't enter these characters yourself they show up automatically if you did the above steps correctly.
Explaining array formula in cell A6
I recommend that you use the built-in "Evaluate Formula" feature in Excel to better understand and troubleshoot formulas, it is a great tool that allows you to see each calculation step.
Go to tab "Formulas" on the ribbon, then press with left mouse button on "Evaluate Formula" button. Press with left mouse button on "Evaluate" button to move to next calculation step, press with left mouse button on "Close" button to dismiss the dialog box.
Step 1 - Count previous values
The COUNTIF function counts values in a cell range based on a condition, however, in this case, I use a growing cell reference that expands when you copy the cell and paste to cells below.
This will make the COUNTIF function count based on multiple conditions, this technique is what makes you required to enter the formula as an array formula.
This way the function keeps track of previous values above the current cell so the correct number of values is returned based on the table.
COUNTIF($A$5:A5,$A$1:$A$4)
COUNTIF(range, criteria)
The first argument is $A$5:A5 which is a cell reference, however, it contains two parts. The first part is an absolute cell reference pointing to A5, you can tell that it is absolute by the $ characters.
It means that the cell reference is locked to cell A5, both the column and row number is, locked, and does not change when you copy the cell to cells below. There are exceptions to this like inserting new rows or columns.
The second part is a relative cell reference and this changes when you copy the cell and paste to cells below which is great because the cell reference expands and takes multiple cells into account.
For example, in cell A6 the cell reference is $A$5:A5, however, in cell A7 the cell reference changes to $A$5:A6 and this makes the formula aware of previous returned values above the current cell.
COUNTIF($A$5:A5,$A$1:$A$4)
becomes
COUNTIF("",{"A";"B";"C";"D"})
and returns {0;0;0;0}
In cell A6 there are no previous values above, only cell A5 which is empty.
The COUNTIF function returns an array containing the same number of values as the table and each value in the array represents how many times the values in the table have been displayed.
An array uses commas and semicolons to separate values, commas are between columns and semicolons are between rows.
Step 2 - Compare array values to list
This step checks if the values in the array is equal to the numbers in the table, this works because the values in the array correspond to the numbers in the table.
The equal sign compares the returned values from the COUNTIF function with the values in cell range B1:B4 and returns TRUE or FALSE. Note that the cell reference to B1:B4 is absolute. We don't want it to change when the cell is copied to cells below.
COUNTIF($A$5:A5,$A$1:$A$4)=$B$1:$B$4
becomes
{0;0;0;0}={5;2;1;4}
and returns {FALSE;FALSE;FALSE;FALSE}
Not a single value meets the criteria in the table in cell A6, that is why all values in the array are FALSE.
Step 3 - Match first FALSE value in array
The MATCH function returns the position of a given value in a cell range or array.
MATCH(lookup_value, lookup_array, [match_type])
The first argument is FALSE, we want to know the position of the first value that has not been repeated the number of times specified in the table.
The second argument is the array we created in step 2 and the third argument is 0 (zero) which means we want an exact match.
MATCH(FALSE, COUNTIF($A$5:A5,$A$1:$A$4)=$B$1:$B$4,0)
becomes
MATCH(FALSE, {FALSE;FALSE;FALSE;FALSE},0)
and returns 1.
Step 4 - Return a value of the cell at the intersection of a particular row and column
The INDEX function returns a value from a cell range or array based on a row and column number. The column number is optional if the cell range is only a single column.
INDEX($A$1:$A$4, MATCH(FALSE, COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, 0))
becomes
=INDEX($A$1:$A$4, 1)
and returns A in cell A6.
Repeat values in a predetermined series
This example demonstrates a formula that repeats values in a given sequence and also how many times each value is to be repeated.
Hi Oscar,
Great Job.. Is this possible to repeat the range according to criteria in loop.. like below.Z 5
Y 2
X 1
W 4then Z 5 Times, Y 2 Times.... but in series
Z
Y
X
W
Z
Y
W
Z
W
Z
W
ZRegards,
Deb
Array formula in cell A6:
Explaining formula in cell A6
Step 1 - Count previous values
COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4
becomes
{0;0;0;0}=$B$1:$B$4
becomes
{0;0;0;0}={5;2;1;4}
and returns
{FALSE;FALSE;FALSE;FALSE}.
Step 2 - Replace values to be repeated with their count
IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4))
becomes
IF({FALSE; FALSE; FALSE; FALSE}, "", {0;0;0;0})
and returns
{0;0;0;0}.
Step 3 - Find smallest count number
MIN(IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4)))
becomes
MIN({0;0;0;0})
and returns 0 (zero).
Step 4 - Find relative position
MATCH(MIN(IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4))), IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4)), 0)
becomes
MATCH(0, IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4)), 0)
becomes
MATCH(0, {0;0;0;0}, 0)
and returns 1.
Step 5 - Return value based on position
INDEX($A$1:$A$4, MATCH(MIN(IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4))), IF(COUNTIF($A$5:A5, $A$1:$A$4)=$B$1:$B$4, "", COUNTIF($A$5:A5, $A$1:$A$4)), 0))
becomes
INDEX($A$1:$A$4, 1)
and returns "Z" in cell A6.
Sequence category
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A.  Cell […]
Functions in this article
More than 1300 Excel formulas
Excel categories
17 Responses to “Repeat values across cells”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Again want to thank you Oscar!
You saved me a lot of time and when I saw your formula – simple and clever, I want to have just a part from your knowledge in the field of array formulas and Excel!
I will modify a little bit a formula to preserve of #N/A and make it more flexible:
=IF(ROW()-ROW($A$6)<=SUM($B$1:$B$4)-1;INDEX($A$1:$A$4;MATCH(FALSE; COUNTIF($A$5:A5;$A$1:$A$4)=$B$1:$B$4;0));"")
Even without that the formula is perfect!
Best regards
Todor
P.S. I think that I asked you on the past, but can you recommend me some resources in connection with array formulas?
the countif formula doesn't return true or false in my spreadsheet even after I restarted Excel... :(
I need to generate result from the below table
Col A Col B Col C
Model Model Group Number of times
Oasis-LS1 oasis 0.5
Oasis-LS2 oasis 1
Oasis-LS3 oasis 0.5
Phara-1 phara 1.5
Phara-2 phara 2
to
Col A Col B
Model
Oasis Oasis-LS1 (0.5); Oasis-LS3 (0.5)
Oasis Oasis LS1
Phara-1 Phara-1
Phara-1 Phara-1 (0.5)
Phara-1 Phara-1
Phara-1 Phara-1
I tried using countif and it didn't return true or false.... Can you please help?
Well done. Clever formula.
//Ola
Hi Oscar,
Great Job.. Is this possible to repeat the range according to criteria in loop.. like below.
Z 5
Y 2
X 1
W 4
then Z 5 Times, Y 2 Times.... but in series
Z
Y
X
W
Z
Y
W
Z
W
Z
W
Z
Regards,
Deb
Debraj,
read this:
Repeat the range according to criteria in loop
Hi Oscar,
OOPS brillinat.. just impressed
Thanks for teaching : "there are lots of thing to check ISERROR ("A") .. Only " " is not the limit.. :)"
Regards,
Deb
Hello Oscar,
Thanks for wonderfull website.
I would like to draw attention with respect to "Repeat the range according to criteria in loop". If A1:B4 is having formulae instead of text, output have blank cell in between.
Regards,
S
Shailesh
I would like to draw attention with respect to "Repeat the range according to criteria in loop". If A1:B4 is having formulae instead of text, output have blank cell in between.
It works for me, please check your cell references. I am guessing they reference a blank cell.
super and extremely efficient formula...
Ola and chrisham,
thank you!
hi oscar
thanks again for your formula
how to change 1 value and keep other columns same
eg
col1 col2 col3
item1 a1 b1
item2
item3
item4
output
col1 col2 col3
item1 a1 b1
item2 a1 b1
item3 a1 b1
item4 a1 b1
dont want fill down bcoz lot of items with lots of a's and b's
any macro?
thanks
krishna
Krishna,
When you enter a value in column A, column B and C are automatically filled with values a1 and b1?
hi oscar ,
thanks for giving an option to upload the image.
this is what i wanted
https://postimg.org/image/vgtdw40wj/9b6c7581/
Krishna,
Get the Excel *.xlsm file
Repeat-values-vba.xlsm
[…] Repeat values […]
Hello oscar I tried your code for repeating value (repeat values.xlsx), i am uploading the file i am getting #value! error
[…] Repeat values […]