transpose delete

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

heather - 20 Dec 2007 04:55 GMT

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.

Reply to this Message

Max - 20 Dec 2007 11:57 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 16 lines]
> could anyone give me some idea how to do this, with a macro or formula;
> thanks.

Reply to this Message

heather - 20 Dec 2007 21:29 GMT

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...

> 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:
[quoted text clipped - 31 lines]
> > could anyone give me some idea how to do this, with a macro or formula;
> > thanks.

Reply to this Message

Max - 20 Dec 2007 23:24 GMT

welcome, heather.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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...

Reply to this Message