CODEWITHSUNDEEP
excelfunctionexcel-formula
user3466555
user3466555

Reputation: 25

Formula for pattern in excel

I have a question regarding excel.

Given:

Col 1      Col 2
A             A
B             A
C             A
D             A
E             B
F             B
G             B
H             B

I want to copy the values in column 1 in Col 2 so that each value repeats itself 4 times as shown. I have been looking at a formula that will help me do this efficiently without having to copy and paste all the time. Does anyone have any ideas?

Thanks

Upvotes: 0

Views: 78

Answers (2)

Gary's Student
Gary's Student

Reputation: 96771

With data in column A , in B1 enter:

=INDEX(A:A,ROUNDUP(ROW()/4,0),0)

and copy down

EDIT#1:

To start in row #2, in B2 enter:

=INDEX(A:A,ROUNDUP((ROW()+3)/4,0),0)

and copy down

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166615

If your values start in A2, enter this in B2 and fill down

 =OFFSET(A$2,FLOOR((ROW()-ROW(B$2)/4,1),0)

Upvotes: 1

Related Questions