Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
How to Delete a Range in Closed Workbook (to Replace Delete Query) | Excel Discussion (Misc queries) | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
How do I transpose Comma Separated Data in each cell and delete t. | Excel Discussion (Misc queries) |