CODEWITHSUNDEEP
excelexcel-formula
clattenburg cake
clattenburg cake

Reputation: 1222

VBA Repeat Character Or Number in Array

I need to repeat two characters(Y,N) 2^a number of times infinitely along a column in excel. So in the first column each character will repeat 2^0 = 1 and look like this:

Y
N
Y
N

The second column repeats infinitely 2^1 =2 times

Y
Y
N
N
Y
Y
N
N

and so on- I need to do this for 24 columns! Is there a simple way to do this? I think it may have to use an array formula...

Upvotes: 0

Views: 2705

Answers (3)

Luisjg
Luisjg

Reputation: 36

=IF(MOD(TRUNC((ROW()-1)/2^(COLUMN()-1)),2)=0,"Y","N")

Upvotes: 2

Dick Kusleika
Dick Kusleika

Reputation: 33165

In A1

=IF(MOD(TRUNC((ROW()-1)/COLUMN()),2)=0,"Y","N")

fill down and right as needed.

Upvotes: 3

John Bustos
John Bustos

Reputation: 19574

You can accomplish this using the Substitute and Repeat functions nested together.

For example, suppose you had your original string in cell A1 (A1 = "YN"), then in A2 moving down you have your number of times to replace (A2 = 2^0, A3 = 2^1, A4 = 2^2, ... ), your formula could look as follows:

=SUBSTITUTE(SUBSTITUTE($A$1,"N",REPT("N",$A2)),"Y",REPT("Y",$A2))

And then you can just drag that formula down

Hope that makes sense

Upvotes: 1

Related Questions