Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help, please - How to rearrange 1 column of data into 4 columns ?
I've got One column of data that has...
FirstName LastName City ZipCode John Smith Tulsa 89456 Joe Adams Denver 65454 etc... Exactly 4 rows for each person. How can I move the data for a person onto one row, 4 columns? Like... John Smith Tulsa 89456 Joe Adams Denver 65454 Thanks, in advance, people. Mark246 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help, please - How to rearrange 1 column of data into 4 columns ?
if your data is in A1:A8, try this in cell B1
=INDEX($A$1:$A$8,(ROWS(B$1:B1)-1)*4+COLUMNS($B1:B1)) then copy the formula across and down as far as necessary. Adjust range references as needed to reflect where your data actually is. Then copy the cells in columns B:E and Edit/Paste Special Values to hardcode the data (if you wish to delete the original data in column A afterwards). Be sure to back up your file in case of mishaps. "Mark246" wrote: I've got One column of data that has... FirstName LastName City ZipCode John Smith Tulsa 89456 Joe Adams Denver 65454 etc... Exactly 4 rows for each person. How can I move the data for a person onto one row, 4 columns? Like... John Smith Tulsa 89456 Joe Adams Denver 65454 Thanks, in advance, people. Mark246 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help, please - How to rearrange 1 column of data into 4 columns ?
JMB's is an excellent solution, just requires one formula with a mod to it
based on the first column you place it into. If you want an alternative set of formulas, then these could be used. As JMB did, I'll assume your starting in column B and with 1st name in A1: First column to pick up 1st name (B1), formula: =OFFSET(Sheet1!$A$1,(ROW()-1)*4,0) in next column, to pick up last name: =OFFSET(Sheet1!$A$1,(ROW()-1)*4+1,0) in next column, to pick up city: =OFFSET(Sheet1!$A$1,(ROW()-1)*4+2,0) and in 4th/last column to pick up zip code: =OFFSET(Sheet1!$A$1,(ROW()-1)*4+3,0) Then fill those formulas down the sheet as far as needed, and follow JMB's instructions on Edit | Copy followed by Edit | Paste Special w/Values option selected if desired. I've included the sheet name of the source information in my formulas; if these are going on the same sheet as the source data, then the sheet name part ( Sheet1! ) is not required. "Mark246" wrote: I've got One column of data that has... FirstName LastName City ZipCode John Smith Tulsa 89456 Joe Adams Denver 65454 etc... Exactly 4 rows for each person. How can I move the data for a person onto one row, 4 columns? Like... John Smith Tulsa 89456 Joe Adams Denver 65454 Thanks, in advance, people. Mark246 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help, please - How to rearrange 1 column of data into 4 columns ?
Enter this anywhere, then copy across 4 columns, and then down as needed:
=INDEX($A:$A,4*ROWS($1:1)-3+COLUMNS($A:A)-1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Mark246" wrote in message ... I've got One column of data that has... FirstName LastName City ZipCode John Smith Tulsa 89456 Joe Adams Denver 65454 etc... Exactly 4 rows for each person. How can I move the data for a person onto one row, 4 columns? Like... John Smith Tulsa 89456 Joe Adams Denver 65454 Thanks, in advance, people. Mark246 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help, please - How to rearrange 1 column of data into 4 columns ?
Here is another, first time I saw it, it was provided by Ron Coder
In the first of 4 contiguous columns (for this example, assume column G) put this formula: =INDEX($A:$A,(ROWS($1:1)-1)*4+COLUMNS($G:G)) Then fill it right over to column J Then fill the 4 formulas on down the sheet as far as you need to go. Same comments about Edit | Copy | Paste Special, etc. "Mark246" wrote: I've got One column of data that has... FirstName LastName City ZipCode John Smith Tulsa 89456 Joe Adams Denver 65454 etc... Exactly 4 rows for each person. How can I move the data for a person onto one row, 4 columns? Like... John Smith Tulsa 89456 Joe Adams Denver 65454 Thanks, in advance, people. Mark246 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help, please - How to rearrange 1 column of data into 4 columns ?
Bothers me that I didn't combine the 2 minuses!
=INDEX($A:$A,4*ROWS($1:1)+COLUMNS($A:A)-4) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... Enter this anywhere, then copy across 4 columns, and then down as needed: =INDEX($A:$A,4*ROWS($1:1)-3+COLUMNS($A:A)-1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Mark246" wrote in message ... I've got One column of data that has... FirstName LastName City ZipCode John Smith Tulsa 89456 Joe Adams Denver 65454 etc... Exactly 4 rows for each person. How can I move the data for a person onto one row, 4 columns? Like... John Smith Tulsa 89456 Joe Adams Denver 65454 Thanks, in advance, people. Mark246 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help, please - How to rearrange 1 column of data into 4 columns ?
Magnificent !
I don't know how those hieroglyphs actually do the task, But It WORKS ! THANKS Very Much, people. I LOVE this forum. Mark246 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help, please - How to rearrange 1 column of data into 4 columns ?
And we appreciate your feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Mark246" wrote in message ... Magnificent ! I don't know how those hieroglyphs actually do the task, But It WORKS ! THANKS Very Much, people. I LOVE this forum. Mark246 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reoreder/Rearrange columns through Macros | Excel Discussion (Misc queries) | |||
Rearrange numbers from a column in a different order in a second c | Excel Discussion (Misc queries) | |||
Rearrange two columns of data | Excel Discussion (Misc queries) | |||
Rearrange info in rows to columns - HELP! | Excel Discussion (Misc queries) | |||
Rearrange data columns in Pivot Table | Excel Discussion (Misc queries) |