| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert rows to Columns
I have a repetitive text file of contact information and want to place the
data in columns. The file looks like this: company address city phone fax company address a nd etc... I want the data go to respective colmns. Help |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert rows to Columns
The task varies in complexity depending on whether there is a consistent # of
rows for each contact or not. Your example shows 5 rows. Are they all 5? If so, in cell B1 use this formula & copy across to F1, then copy it down as far as needs be: =OFFSET($A$1,(ROW()-1)*5+COLUMN()-2,0) "Ed" wrote: I have a repetitive text file of contact information and want to place the data in columns. The file looks like this: company address city phone fax company address a nd etc... I want the data go to respective colmns. Help |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert rows to Columns
It did not work for me.
The file is set like this: A + XXXXXX. Clifford XXX PO Box 7028 XXX, NC 28241-7028 US Phone: (XXX) XXX-XXXX Fax: (704) XXX-XXXX Email: Web: www.XXXXX.com 2 line row spacers repeat again same 8 rows of new data Hope you can figure this one out. tks "Ed" wrote: I have a repetitive text file of contact information and want to place the data in columns. The file looks like this: company address city phone fax company address a nd etc... I want the data go to respective colmns. Help |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert rows to Columns
OK, assuming your data starts in A1, then use this in the first row in
B1:I1 =OFFSET($A$1,(ROW()-1)*8+COLUMN()-2,0) and this for all subsequent rows =OFFSET($A$1,(ROW()-1)*8+COLUMN(),0) "Ed" wrote: It did not work for me. The file is set like this: A + XXXXXX. Clifford XXX PO Box 7028 XXX, NC 28241-7028 US Phone: (XXX) XXX-XXXX Fax: (704) XXX-XXXX Email: Web: www.XXXXX.com 2 line row spacers repeat again same 8 rows of new data Hope you can figure this one out. tks "Ed" wrote: I have a repetitive text file of contact information and want to place the data in columns. The file looks like this: company address city phone fax company address a nd etc... I want the data go to respective colmns. Help |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert rows to Columns
Well, that doesn't work for rows 3 on.
Kinda goofy, but it appears to work for all the rows: =OFFSET($A$1,(ROW()-1)*8+COLUMN()+2*(ROW()-2),0) "Duke Carey" wrote: OK, assuming your data starts in A1, then use this in the first row in B1:I1 =OFFSET($A$1,(ROW()-1)*8+COLUMN()-2,0) and this for all subsequent rows =OFFSET($A$1,(ROW()-1)*8+COLUMN(),0) "Ed" wrote: It did not work for me. The file is set like this: A + XXXXXX. Clifford XXX PO Box 7028 XXX, NC 28241-7028 US Phone: (XXX) XXX-XXXX Fax: (704) XXX-XXXX Email: Web: www.XXXXX.com 2 line row spacers repeat again same 8 rows of new data Hope you can figure this one out. tks "Ed" wrote: I have a repetitive text file of contact information and want to place the data in columns. The file looks like this: company address city phone fax company address a nd etc... I want the data go to respective colmns. Help |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert rows to Columns
Select B1 and paste this in.
=INDEX($A:$A,(ROWS($1:1)-1)*9+COLUMNS($A:B)-1) Copy across to J1 Copy B1:J1 down until zeros show up. Delete Column J when done. Alternative............... Before taking these steps select Column A and F5SpecialBlanksOK EditDeleteEntire row. Now just copy formula over to column I Gord Dibben MS Excel MVP On Thu, 3 Aug 2006 09:29:02 -0700, Ed wrote: It did not work for me. The file is set like this: A + XXXXXX. Clifford XXX PO Box 7028 XXX, NC 28241-7028 US Phone: (XXX) XXX-XXXX Fax: (704) XXX-XXXX Email: Web: www.XXXXX.com 2 line row spacers repeat again same 8 rows of new data Hope you can figure this one out. tks "Ed" wrote: I have a repetitive text file of contact information and want to place the data in columns. The file looks like this: company address city phone fax company address a nd etc... I want the data go to respective colmns. Help |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert rows to Columns
It just wont work- there has to be way to do this- wish i could figure it out-
it workfor the first 3 lines- rows and then the data just wont align itself in the colums. any other ideas??? .. "Duke Carey" wrote: Well, that doesn't work for rows 3 on. Kinda goofy, but it appears to work for all the rows: =OFFSET($A$1,(ROW()-1)*8+COLUMN()+2*(ROW()-2),0) "Duke Carey" wrote: OK, assuming your data starts in A1, then use this in the first row in B1:I1 =OFFSET($A$1,(ROW()-1)*8+COLUMN()-2,0) and this for all subsequent rows =OFFSET($A$1,(ROW()-1)*8+COLUMN(),0) "Ed" wrote: It did not work for me. The file is set like this: A + XXXXXX. Clifford XXX PO Box 7028 XXX, NC 28241-7028 US Phone: (XXX) XXX-XXXX Fax: (704) XXX-XXXX Email: Web: www.XXXXX.com 2 line row spacers repeat again same 8 rows of new data Hope you can figure this one out. tks "Ed" wrote: I have a repetitive text file of contact information and want to place the data in columns. The file looks like this: company address city phone fax company address a nd etc... I want the data go to respective colmns. Help |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert rows to Columns
Ed
Did you read my post? I will repeat here................ Select B1 and paste this in. =INDEX($A:$A,(ROWS($1:1)-1)*9+COLUMNS($A:B)-1) Copy across to J1 Copy B1:J1 down until zeros show up. Delete Column J when done. Alternative............... Before taking these steps select Column A and F5SpecialBlanksOK EditDeleteEntire row. Now just copy formula over to column I Gord Dibben MS Excel MVP On Thu, 3 Aug 2006 13:35:02 -0700, Ed wrote: It just wont work- there has to be way to do this- wish i could figure it out- it workfor the first 3 lines- rows and then the data just wont align itself in the colums. any other ideas??? . "Duke Carey" wrote: Well, that doesn't work for rows 3 on. Kinda goofy, but it appears to work for all the rows: =OFFSET($A$1,(ROW()-1)*8+COLUMN()+2*(ROW()-2),0) "Duke Carey" wrote: OK, assuming your data starts in A1, then use this in the first row in B1:I1 =OFFSET($A$1,(ROW()-1)*8+COLUMN()-2,0) and this for all subsequent rows =OFFSET($A$1,(ROW()-1)*8+COLUMN(),0) "Ed" wrote: It did not work for me. The file is set like this: A + XXXXXX. Clifford XXX PO Box 7028 XXX, NC 28241-7028 US Phone: (XXX) XXX-XXXX Fax: (704) XXX-XXXX Email: Web: www.XXXXX.com 2 line row spacers repeat again same 8 rows of new data Hope you can figure this one out. tks "Ed" wrote: I have a repetitive text file of contact information and want to place the data in columns. The file looks like this: company address city phone fax company address a nd etc... I want the data go to respective colmns. Help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I convert an entire sheet from columns to rows? | Excel Worksheet Functions | |||
| How can I transpose rows to columns in a large worksheet? | Excel Discussion (Misc queries) | |||
| REQ: Columns to rows or Rows to Columns | Excel Discussion (Misc queries) | |||
| How to keep rows together when sorting columns? | Excel Worksheet Functions | |||
| sumif columns and rows | Excel Worksheet Functions | |||
Linear Mode
