transpose delete
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. |
transpose delete
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "heather" wrote: 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. |
transpose delete
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" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "heather" wrote: 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. |
transpose delete
welcome, heather.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "heather" wrote in message ... 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... |
All times are GMT +1. The time now is 03:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com