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

### Category: Sequence

Create number sequences in excel 2007

Create number sequences (Autofill) Example 1 Type "1" in cell B3 + Enter Right click on black dot and drag […]Comments(9) Filed in category: Excel, Sequence

Sequencing and numbering of batches in excel

Question: I have two volumes which keeps on varying in L1 & L2 head. This is the only info i […]Comments(6) Filed in category: Excel, Sequence

Find the longest/smallest 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 […]Comments(5) Filed in category: Excel, Sequence

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 […]Comments(3) Filed in category: Excel, Sequence

Comments(2) Filed in category: Excel, Sequence

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 […]Comments(1) Filed in category: Excel, Sequence

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

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