Arranging columns
 

Suppose my excel sheet looks like this:
A
1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1

Now in B1 i want A10A9A8A7A6, i.e. 12345 then in B2 i want A5A4A3A2A1,
i.e.678910.

This is a simplified version of my problem. I have a column of 1000 numbers:
- i want to take the first 5 numbers and arrange them in reverse order,
- keep doing this for all the 1000 numbers in the column.

How can i do this without manually performing the work?

One way:

=INDEX(A:A,COUNTA(A:A)-(ROW()-1)*5) &
INDEX(A:A,COUNTA(A:A)-(ROW()-1)*5-1) &
INDEX(A:A,COUNTA(A:A)-(ROW()-1)*5-2) &
INDEX(A:A,COUNTA(A:A)-(ROW()-1)*5-3) &
INDEX(A:A,COUNTA(A:A)-(ROW()-1)*5-4)

Copy down as far as required.

In article ,
"Himu" wrote:

Suppose my excel sheet looks like this:
A
1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1

Now in B1 i want A10A9A8A7A6, i.e. 12345 then in B2 i want A5A4A3A2A1,
i.e.678910.

This is a simplified version of my problem. I have a column of 1000 numbers:
- i want to take the first 5 numbers and arrange them in reverse order,
- keep doing this for all the 1000 numbers in the column.

How can i do this without manually performing the work?

Could you explain how I can implement this foermula. I tried to paste the
formula you provided me below, but excel gives me error. Can you tell me what
should be inside ROW(??)

thankx

"JE McGimpsey" wrote:

One way:

=INDEX(A:A,COUNTA(A:A)-(ROW()-1)*5) &
INDEX(A:A,COUNTA(A:A)-(ROW()-1)*5-1) &
INDEX(A:A,COUNTA(A:A)-(ROW()-1)*5-2) &
INDEX(A:A,COUNTA(A:A)-(ROW()-1)*5-3) &
INDEX(A:A,COUNTA(A:A)-(ROW()-1)*5-4)

Copy down as far as required.

In article ,
"Himu" wrote:

Suppose my excel sheet looks like this:
A
1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1

Now in B1 i want A10A9A8A7A6, i.e. 12345 then in B2 i want A5A4A3A2A1,
i.e.678910.

This is a simplified version of my problem. I have a column of 1000 numbers:
- i want to take the first 5 numbers and arrange them in reverse order,
- keep doing this for all the 1000 numbers in the column.

How can i do this without manually performing the work?

Nothing goes inside ROW(), although it wouldn't hurt to put a reference
to any cell in the row that the formula resides in. Given your sample
data, the formula works as is.

What error are you getting?


In article ,
"Himu" wrote:

Could you explain how I can implement this foermula. I tried to paste the
formula you provided me below, but excel gives me error. Can you tell me what
should be inside ROW(??)