## Repeat values

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

**Array formula in cell A6:**

**Download excel * .xlsx file**

### 13 Responses to “Repeat values”

### Leave a Reply

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

<code>your formula</code>

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

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

http://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