Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column To Rows
I often get lists of contacts that are generated in one long column. They are
often may be 4 or 5 rows per record. In order to be able to use the data I need to transpose the rows in each record (one column) to new columns. It may include Company Name in Row A1 and contact name in Row B1 and perhaps Phone or Web Site in C1 etc. The records are always the same amount of rows. I have tried copy, transpose, and text to rows and anything else I can think of. Can anyone make a suggestion ?? Thanks in advance Tony Pass |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column To Rows
If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Tony Pass" wrote in message ... I often get lists of contacts that are generated in one long column. They are often may be 4 or 5 rows per record. In order to be able to use the data I need to transpose the rows in each record (one column) to new columns. It may include Company Name in Row A1 and contact name in Row B1 and perhaps Phone or Web Site in C1 etc. The records are always the same amount of rows. I have tried copy, transpose, and text to rows and anything else I can think of. Can anyone make a suggestion ?? Thanks in advance Tony Pass |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column To Rows
If this line is true:
The records are always the same amount of rows .. this OFFSET formula will also achieve the required transformation Source data assumed continuous in A2 down, in groups of 3 lines each In B2: =OFFSET($A$2,ROWS($1:1)*3-3+COLUMNS($A:A)-1,) Copy across by 3 cols to D2, fill down to exhaust Just change the 3 in the expression to suit the number of rows per group, and copy across by the same number of cols before filling down to exhaust the extract. Change the $A$2 to suit where your source data starts Success? celebrate it, hit the YES below -- Max Singapore --- "Tony Pass" wrote: I often get lists of contacts that are generated in one long column. They are often may be 4 or 5 rows per record. In order to be able to use the data I need to transpose the rows in each record (one column) to new columns. It may include Company Name in Row A1 and contact name in Row B1 and perhaps Phone or Web Site in C1 etc. The records are always the same amount of rows. I have tried copy, transpose, and text to rows and anything else I can think of. Can anyone make a suggestion ?? Thanks in advance Tony Pass |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column To Rows
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count all rows in column with data, Except rows 1-5 | Excel Worksheet Functions | |||
lock column size for some rows and resize for other rows. | Excel Discussion (Misc queries) | |||
printing issue - want to repeat two rows and also 5 rows in column | Excel Discussion (Misc queries) | |||
after selecting 50 rows of a column i can't reference the cells in the rows | New Users to Excel | |||
Need rows in Column A removed if they fully or partially match with any Column B row | Excel Discussion (Misc queries) |