transpose delete

[heather]

Hi: I have 3 columns of data; the first column is just a descriptive field
giving the file name, and the data in the 2nd column are all the same with
values next to them: like this
separate house: 200
single story flat: 2
double story flat: 4
caravan: 4
there are 21 rows for each record in column A, and I have 293 groups of 21
rows.

This data is all in one column. I want to transpose each group of 21, so
that column b is the heading, and the numerical data is under each one; eg.

record number separate house flat caravan
a 300 5 4
b 29 18 0
and so on for each of the 293 separate records;

could anyone give me some idea how to do this, with a macro or formula;
thanks.

 

[Max]

Use Data > Text to Columns, delimited with ":" to split the data in col B
into cols B and C so that the numbers are in col C, viz:

separate house 200
single story flat 2
double story flat 4
etc

Then, assuming the above is running in B1:C1 down
select & copy B1:B21, then select E1, do a paste special > transpose/values
to paste the 21 col headers into E1:Y1

Then place in E2:
=OFFSET($C$1,ROWS($1:1)*21-21+COLUMNS($A:A)-1,)
Copy E2 across to Y2, fill down by 293 rows to exhaust the data in col C

 

[heather]

thanks; I did it another long way around (sort, cut, paste), but will
definitely use your method for the next 100 or so that i have to do...

 

[Max]

welcome, heather.