Need Help in arranging the set of numbers one after the other

[gokzee]

Hello,

I need a formula to arrange the numbers as shown in the attached excel sheet.

I need to display the output in a different sheet.


will a formula work for this???

many thanks for looking ..

GOkz

[gokzee]

or should i try in the same sheet itself???

[TheNewUnion]

I'm not 100% sure what you are attempting to do here? Is it just a case of moving the lists that are in Rows 1 and 2 into the Output table?

[gokzee]

I have around 400 such rows that has to be made in similar order.

1 2 3 4 5 6 7 8 9 10 (base date)

i want output


1 2 3 4 5

6 7 8 9 10


hope you got it..

Gokz

[TheNewUnion]

OK, so you have e.g. numbers 1-400 in a row and you want to transform them into a table with 5 columns then sufficient rows to get to 400?

Do the numbers change or is this just a one off thing?

[gokzee]

if i have numbers 1-400

my base data will be
a b c d e f g h i j
1st Row -- 1 2 3 4 5 6 7 8 9 10

2nd row -- 11 12 13 14 15 16 17 18 19 20

nth row -- 391 392 393 394 395 396 397 398 399 400


i have to arrange it in 5 columns

a b c d e

1 2 3 4 5

6 7 8 9 10

11 12 13 14 15

16 17 18 19 20

-------------
391 392 393 394 395

396 397 398 399 400

hope u got it..

its a one step process.

Gokz

[TheNewUnion]

If you just need to have 5 columns that have 1-5, 6-10 etc and it only needs to happen once, you could just type numbers 1-5 in A1-E1

Type =A1+5 in A2 and drag it to E2, then just highlight and drag A2 to E2 down until you get to 400, hope that makes sense?

[gokzee]

Thanks

but my actual data is not numbers in order. they are random numbers in hundreds and thousands.

so i can only do this with cell reference.

how should i use cell reference to get A(1+5). is it possible?

Gokz

[:) Sixthsense :)]

In A6 cell of your Post #1 attachment file.

Please Login or Register  to view this content.

Drag it to right upto column-E and down...

[gokzee]

Thanks Sixth Sense and TheNewUnion

a macro is better solution

[:) Sixthsense :)]

You would have saved our time if you mention your requirement in initial thread itself

[Marcol]

Another formula

Please Login or Register  to view this content.

Drag Across 5 columns then down as required.

[:) Sixthsense :)]

@ Marcol,

I am happy, it's not only me You too wasted the time

[gokzee]

Thanks Marcol