Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate records from Vertical to Horizontal
I have addresses in an Excel spreadsheet. Here is how they are formatted:
row 1: (blank) row 2: name row 3: address row4: city, state, zip This then repeats about 700 times. Can anyone help me create a formula that copies these 4 rows to a single row in another tab? For example, row1 would go to columnA in the next tab, row2 would go to columnB in the next tab, etc? I'd need it to repeat each time, so I have a tab that has each record in a single row (for printing labels). Please help! I cannot figure out the formula. PM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate records from Vertical to Horizontal
Assuming the source data is in A1 down,
Put in B1: =INDEX($A:$A,ROW(A1)*4-4+COLUMN(A1)) Copy B1 across 4 cols to E1, fill down until zeros appear signalling exhaustion of data from col A If required, freeze the results in cols B to E with an in-place: Copy Paste special check "Values" OK -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "PM" wrote in message ... I have addresses in an Excel spreadsheet. Here is how they are formatted: row 1: (blank) row 2: name row 3: address row4: city, state, zip This then repeats about 700 times. Can anyone help me create a formula that copies these 4 rows to a single row in another tab? For example, row1 would go to columnA in the next tab, row2 would go to columnB in the next tab, etc? I'd need it to repeat each time, so I have a tab that has each record in a single row (for printing labels). Please help! I cannot figure out the formula. PM |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate records from Vertical to Horizontal
Why do you need blanks to go there, I would delete them
select the import column, press f5, select special and blanks, press Ctrl - (or editdelete), select entire row, that will give you the same data without the blanks, then use =OFFSET(Sheet1!$A$1,(ROW(1:1)-1)*3+COLUMN(A:A)-1,0) replace Sheet1 with the sheet name, if you insist of copying over empty cells =OFFSET(Sheet1!$A$1,(ROW(1:1)-1)*4+COLUMN(A:A)-1,0) for the former copy across 3 cells and then copy down as long as needed for the latter copy across 4 cells and then down (use the fill handle to copy across/down) -- Regards, Peo Sjoblom "PM" wrote in message ... I have addresses in an Excel spreadsheet. Here is how they are formatted: row 1: (blank) row 2: name row 3: address row4: city, state, zip This then repeats about 700 times. Can anyone help me create a formula that copies these 4 rows to a single row in another tab? For example, row1 would go to columnA in the next tab, row2 would go to columnB in the next tab, etc? I'd need it to repeat each time, so I have a tab that has each record in a single row (for printing labels). Please help! I cannot figure out the formula. PM |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Separate records from Vertical to Horizontal
In addition to Max's suggestion, you may want to break up the City,State,Zip
cell into 3 distinct cells after you get the 4 rows into the one row format. Select that column and DataText to ColumnsDelimited by comma and Finish. Most labelling programs like Word work better with those in separate columns. Gord Dibben Excel MVP On Fri, 16 Dec 2005 22:26:35 +0800, "Max" wrote: Assuming the source data is in A1 down, Put in B1: =INDEX($A:$A,ROW(A1)*4-4+COLUMN(A1)) Copy B1 across 4 cols to E1, fill down until zeros appear signalling exhaustion of data from col A If required, freeze the results in cols B to E with an in-place: Copy Paste special check "Values" OK -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "PM" wrote in message ... I have addresses in an Excel spreadsheet. Here is how they are formatted: row 1: (blank) row 2: name row 3: address row4: city, state, zip This then repeats about 700 times. Can anyone help me create a formula that copies these 4 rows to a single row in another tab? For example, row1 would go to columnA in the next tab, row2 would go to columnB in the next tab, etc? I'd need it to repeat each time, so I have a tab that has each record in a single row (for printing labels). Please help! I cannot figure out the formula. PM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change my typing from horizontal to vertical | Excel Worksheet Functions | |||
Excel should have vertical and horizontal rulers. | Excel Discussion (Misc queries) | |||
Deleting specific records | Excel Discussion (Misc queries) | |||
Excel - turn a page number in a header from horizontal to vertical | Charts and Charting in Excel | |||
How do I get both horizontal and vertical frozen panes in Excell | Excel Discussion (Misc queries) |