Need Formula to push down 5 numbers instead of 1 !!!

[serge]

I have this set up. ( Window Vista, Excel 07 )

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
5		1	15	4	19	5	14	16	3	2	5	17	9	7
6		2	1	15	4	19	5	14	16	3	2	5	17	9
7		3	2	1	15	4	19	5	14	16	3	2	5	17
8		4	3	2	1	15	4	19	5	14	16	3	2	5
9		5	4	3	2	1	15	4	19	5	14	16	3	2
10		6	5	5	3	2	1	15	4	19	19	14	16	3
11		7	6	6	5	3	2	1	15	4	4	19	14	16
12		8	7	7	6	6	3	2	1	15	15	4	19	14
13		9	8	8	7	7	6	3	2	1	1	15	4	19
14		10	9	9	8	8	7	6	6	6	6	1	15	4
15		11	10	10	9	9	8	7	7	7	7	6	1	15
16		12	11	11	10	10	9	8	8	8	8	7	6	1
17		13	12	12	11	11	10	9	9	9	9	8	7	6
18		14	13	13	12	12	11	10	10	10	10	9	8	8
19		15	14	14	13	13	12	11	11	11	11	10	10	10
20		16	16	16	14	14	13	12	12	12	12	11	11	11
21		17	17	17	16	16	16	13	13	13	13	12	12	12
22		18	18	18	17	17	17	17	17	17	17	13	13	13
23		19	19	19	18	18	18	18	18	18	18	18	18	18
24		20	20	20	20	20	20	20	20	20	20	20	20	20
Sheet1

As you can see the numbers in row 5 ( one at the time ) are taken from the previous column in then put on top of the new column.
with this formula :

=IF(C$5="",0,IF(COUNTIF(C$5:C5,B5),B6,B5))

What I need now is , if I have 5 numbers total for example in :
C5:C9 i would need now since those numbers are on top of the column to be removed from below, exactly like the example with the table above with one number ?

Here the example of what I need :

Excel Workbook
	A	B	C	D	E	F	G
1
2
3
4
5		1	2	2	6	1	10
6		2	8	3	7	9	12
7		3	11	5	13	10	14
8		4	14	14	17	12	16
9		5	20	16	19	15	20
10		6	1	8	2	6	1
11		7	3	11	3	7	9
12		8	4	20	5	13	15
13		9	5	1	14	17	6
14		10	6	4	16	19	7
15		11	7	6	8	2	13
16		12	9	7	11	3	17
17		13	10	9	20	5	19
18		14	12	10	1	14	2
19		15	13	12	4	16	3
20		16	15	13	9	8	5
21		17	16	15	10	11	8
22		18	17	17	12	20	11
23		19	18	18	15	4	4
24		20	19	19	18	18	18
Sheet1

Thank you very much for any help.

Serge.

 

[Jerry Sullivan]

Serge,

The array formula below should work. Enter in C10 then copy down and across.

Excel Workbook
	B	C	D	E	F	G
5	1	2	2	6	1	10
6	2	8	3	7	9	12
7	3	11	5	13	10	14
8	4	14	14	17	12	16
9	5	20	16	19	15	20
10	6	1	8	2	6	1
11	7	3	11	3	7	9
12	8	4	20	5	13	15
13	9	5	1	14	17	6
14	10	6	4	16	19	7
15	11	7	6	8	2	13
16	12	9	7	11	3	17
17	13	10	9	20	5	19
18	14	12	10	1	14	2
19	15	13	12	4	16	3
20	16	15	13	9	8	5
21	17	16	15	10	11	8
22	18	17	17	12	20	11
23	19	18	18	15	4	4
24	20	19	19	18	18	18
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly,
Excel will surround with curly braces {}.
Note: Do not try and enter these manually yourself

 

[serge]

JS411,

Your formula work like a charm, you are a genius, I really appreciate the help.
Thank you very much.

I need help with another formula in the same range but more tricky, I will post it later on tonight or tomorrow.

Thanks again.

Serge.