## Repeat values

*Article updated on August 30, 2017*

**Table of Contents**

A 5

B 2

C 1

D 4

Is it possible with formula to generate list like this:

A

A

A

A

A

B

B

C

D

D

D

D

Thank you in advance!

**Answer:**

**Array formula in cell A6:**

**Explaining array formula in cell A6**

**Step 1 - Count previous values**

COUNTIF($A$5:A5,$A$1:$A$4)

becomes

COUNTIF("",{"A";"B";"C";"D"})

and returns {0;0;0;0}

**Step 2 - Compare array values to list**

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}

**Step 3 - Match first FALSE value**

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**

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

I have made an Advanced Excel Course if you want to learn how to construct powerful array formulas.

### Download excel *.xlsx file

### Repeat the range according to criteria in loop

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:**

**Download excel * .xlsx file**

Find the longest/smallest consecutive sequence of a value [VBA]

This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show […]### 15 Responses to “Repeat values”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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,

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