## 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.

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 […]

### 15 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.

**Contact Oscar**

You can contact me through this contact form

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

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 […]