Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : Is there any speed fill formula ?
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 -- A36B58K641 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : Is there any speed fill formula ?
Here's one ..
No need col D, just put in A1: =OFFSET($C$1,INT((ROW(A1)-1)/3),) Copy A1 down to A2500 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mr. Low" wrote: 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 -- A36B58K641 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : Is there any speed fill formula ?
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) "Mr. Low" wrote: 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 -- A36B58K641 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : Is there any speed fill formula ?
Hello Max,
Great formula. It works very well. Many thanks Low -- A36B58K641 "Max" wrote: Here's one .. No need col D, just put in A1: =OFFSET($C$1,INT((ROW(A1)-1)/3),) Copy A1 down to A2500 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mr. Low" wrote: 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 -- A36B58K641 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : Is there any speed fill formula ?
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 -- A36B58K641 "Joel" wrote: 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) "Mr. Low" wrote: 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 -- A36B58K641 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : Is there any speed fill formula ?
You're welcome, Low.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mr. Low" wrote in message ... Hello Max, Great formula. It works very well. Many thanks Low |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : Is there any speed fill formula ?
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" wrote: 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 -- A36B58K641 "Joel" wrote: 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) "Mr. Low" wrote: 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 -- A36B58K641 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : Is there any speed fill formula ?
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 -- A36B58K641 "Joel" wrote: 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" wrote: 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 -- A36B58K641 "Joel" wrote: 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) "Mr. Low" wrote: 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 -- A36B58K641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet speed with array formula | Excel Discussion (Misc queries) | |||
Speed fill function for MS Ecel 2007 (Beta) | Excel Worksheet Functions | |||
Speed up calculation of a worksheet that contains formula | Excel Worksheet Functions | |||
cell fill colors in excel 2002 sp3 | Excel Worksheet Functions | |||
How do I create custom fill colors (for cells) in Excel 2002? | Excel Discussion (Misc queries) |