Excel 2002 : Is there any speed fill formula ?

[Mr. Low]

Dear Sir,

Lets consider the following worksheet:

A B C D
1 512 xxxx 512 1
2 512 xxxx 613 2
3 512 xxxx 816 3
4 613 xxxx 925 1
5 613 xxxx 216 2
6 613 xxxx 327 3
7 816 xxxx 428 1
8 816 xxxx 629 2
9 816 xxxx 520 3
10 925 xxxx 821 1
11 925 xxxx 922 2
12 925 xxxx 623 3

.......

2500




I need to fill up column A with similar references obtained from Column C
for 3 times each.

Column D had a running number that repeat itself after a count of 3,

Is there any formula I can input at A1 and copy down to A2500 to get the
straight answer ?



Thanks

Low

 

[Max]

Here's one ..

No need col D, just put in A1:
=OFFSET($C$1,INT((ROW(A1)-1)/3),)
Copy A1 down to A2500

 

[Joel]

If you took the row number of the item in column A you use the formula

In cell A5
=Row(A5)

You know use this as an index into column B

Because you are taking multiple of 3 i row A the formula starts by dividing
by 3. You have to make an adjustment because the data sttarts in row 2.

=Rounddown((Row(A4) - 2)/3)


Your offset into column c would be

=OFFSET($C$2,ROUNDDOWN((ROW(A4)-2)/3,0),0,1,1)

Dear Sir,

Lets consider the following worksheet:

A B C D
1 512 xxxx 512 1
2 512 xxxx 613 2
3 512 xxxx 816 3
4 613 xxxx 925 1
5 613 xxxx 216 2
6 613 xxxx 327 3
7 816 xxxx 428 1
8 816 xxxx 629 2
9 816 xxxx 520 3
10 925 xxxx 821 1
11 925 xxxx 922 2
12 925 xxxx 623 3

 

[Mr. Low]

Hello Max,

Great formula. It works very well.

Many thanks


Low

 

[Mr. Low]

Hello Joel,

I could not get the correct answer using the formula. (i.e. 512 and two of
the of 513 entry was not picked up in column A)

I supposed the formula should be
=OFFSET($C$1,ROUNDDOWN((ROW(A2)-2)/3,0),0,1,1)

This is because the filling of data starts at A1 and C1.

Anyway, please confirm.

Thanks

Low

 

[Max]

You're welcome, Low.

 

[Joel]

Mr Low: I think the problem is your data is starting in Row 1 insteead of row
2. try this instead. the -2 which is now -1 is andjustment to start with a
row offset of zero.


=OFFSET($C$1,ROUNDDOWN((ROW(A2)-1)/3,0),0,1,1)

 

[Mr. Low]

Helo Joel,

I tried =OFFSET($C$1,ROUNDDOWN((ROW(A2)-1)/3,0),0,1,1)
but the first item still short of one entry.

I could only get it correct by using :
=OFFSET($C$1,ROUNDDOWN((ROW(A1)-1)/3,0),0,1,1)

Thank you anyway.

Low