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

Excel has a great built-in versatile tool for creating number sequences. In some situations, however, you need to rely on […]

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

Sequencing and numbering of batches

Question: I have two volumes which keeps on varying in L1 & L2 head. This is the only info i […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

This array formula finds the sequence "Axis Chemical Co." (cell E2) and "Southern Railway" (cell E3) in two consecutive cells […]

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]

How to identify two consecutive dates in a list

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter […]

### 15 Responses to “Repeat values”

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

Use the img tag, like this: <img src="Insert pic link here">

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

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