combining multiple rows into 1 record

C

ccrydr

I have the following 200 or so records. I'm trying to combine the 4 rows --
A1:D1 then A2:D2 and A3:D3 finally A4-- into 1 row - A1:M1. Here is a sample:


A B
C D
1 Ships Wallace 7945
Fenron St Listville
2 CO 80003-2531 5149277418 50 - 54
3 $60,000 - $69,999 M Confirmed Owner $200,000 -
$249,999
4 12

Every 4 rows a new record begins. What is the most efficient way to do this?
 
M

Max

One way ..

Assume source data in sheet: X, cols A to D, from row 1 down:

1 2 3 4
5 6 7 8
9 10 11 12
13
14 15 16 17
18 19 20 21
22 23 24 25
26
(next set of 13 cells, etc)

In a new sheet,

Put in say, A1:
=OFFSET(INDIRECT("'X'!A"&ROWS($A$1:A1)*4-3),INT((COLUMNS($A$1:A1)-1)/4),MOD(
COLUMNS($A$1:A1)-1,4))

Copy A1 across to M1, fill down until zeros appear,
signalling exhaustion of data extracted from X

For the sample data above,
we'd get it re-arranged in the desired manner, i.e.:

1 2 3 4 5 6 7 8 9 10 11 12 13
14 15 16 17 18 19 20 21 22 23 24 25 26


ccrydr said:
I have the following 200 or so records. I'm trying to combine the 4 rows --
A1:D1 then A2:D2 and A3:D3 finally A4-- into 1 row - A1:M1. Here is a sample:


A B
C D
1 Ships Wallace 7945
Fenron St Listville
2 CO 80003-2531 5149277418 50 - 54
3 $60,000 - $69,999 M Confirmed Owner $200,000 -
$249,999
4 12

Every 4 rows a new record begins. What is the most efficient way to do
Click to expand...
this?
 
Top