How to combine multiple rows of data into a single row (with multiple columns)?

[probeen]

Hello All,

I am a basic user of Excel. I do not know Macros or VBA. I have a task which I hope you will be able to help. The task is given below.
I have an Excel datasheet with 4 columns and 4485 rows.
I want to rearrange this data to get a 69 X 260 matrix i.e. 69 rows & 260 columns.
That means, from the present datasheet, each block of 65 rows should be arranged in a single row in the following way.
Please see the attached sample workbook

In the table below, the red chracters are actual data. The top row & the left-row shows the columns & rows respectively in Excel.
Present data:

A B C D
1 101 102 103 104
2 105 106 107 108. . . . .
. . . . .
65 357 358 359 360
66 361 362 363 364






Required data:

A B C D . . IY IZ
1 101 102 103 104 . . 359 360
2 361 362 363 364 . . 619 620
. . . . . . . . .
. . . . . . . . .
69 6901 6902 6903 6904 . . 7159 7160

Please see the attached sample workbook
I want your expert help in resolving my problem.
Thanks a lot.

[dealer]

a little advise,change your thread title.
and then post a sample w/book.

[BlastRanger]

Hey probeen,
Try this formula in Sheet Required Data (No Macro nor VBA required ):
In A1 type :

=INDEX('Present Data'!$A:$D,ROUNDUP(((ROW()-1)*260+COLUMN())/4,0),IF(RIGHT((COLUMN()+(ROW()-1)*260)/4,2)="25",1,IF(RIGHT((COLUMN()+(ROW()-1)*260)/4,2)=".5",2,IF(RIGHT((COLUMN()+(ROW()-1)*260)/4,2)="75",3,4))))

Then Copy it until it reach the matrix you want (260 Columns and 69 Row)

the Row will not be limited to 69 (you can add up more than 69 row)
This formula, the Column is limited to 260 Columns (if you copy to more than 260 column it will not the correct result at the next row)

If you want to have the column and row to be unlimited, then change all the 260 to COUNTA(1:1) which would count those non blank column of a row.

That solve the matrix problem in your sheet

[probeen]

thank you blastranger for your help.
The problem is solved now.

[BlastRanger]

Hi,
if you would not mind, give me a reputation,
I'm really need that :P :P
as I wreck my brain to think the formula for you (it really hard though)

[FlyNavy2002]

Good morning all...

I have a follow on question to this thread. I am in the same position as the OP, except that I have multiple rows that I would like to bring into one column, so that the text in K3:T3, K4:T4, etc., etc, (up to 200 rows) would be displayed in a single column as read and without further sorting.

Is this possible in Excel2010?

[arlu1201]

FlyNavy2002,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.