Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving cells in a column
I have some data that is all in order in a column, like this:
name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 and i like to move the cells to look like this: (Each in it's own cell for sorting) name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 without having to drag every single cell one at a time. I have over 2,500 cells and it would take too much work. My son is charging mee too much to do it.(i should start charging him for rent and food) What options do i have? all help is really apreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving cells in a column
1) Set up a "helper" column.
2) Enter 1 through 3 in the first five rows of the helper column. Copy this pattern down through your dataset. 3) Sort on the helper column. All your 1's will run together (names), all your 2's will run together (street address), then your 3's (cities). These continuous blocks of data are then easily copied/pasted into separate columns and will appear in their natural order so long as you don't define a second criteria by which to perform your initial sort. "CTCrow" wrote: I have some data that is all in order in a column, like this: name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 and i like to move the cells to look like this: (Each in it's own cell for sorting) name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 without having to drag every single cell one at a time. I have over 2,500 cells and it would take too much work. My son is charging mee too much to do it.(i should start charging him for rent and food) What options do i have? all help is really apreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving cells in a column
Correction to 2): Enter a 1-2-3 pattern in the first THREE rows of the helper
column. Copy this pattern through your dataset. "Eric" wrote: 1) Set up a "helper" column. 2) Enter 1 through 3 in the first five rows of the helper column. Copy this pattern down through your dataset. 3) Sort on the helper column. All your 1's will run together (names), all your 2's will run together (street address), then your 3's (cities). These continuous blocks of data are then easily copied/pasted into separate columns and will appear in their natural order so long as you don't define a second criteria by which to perform your initial sort. "CTCrow" wrote: I have some data that is all in order in a column, like this: name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 and i like to move the cells to look like this: (Each in it's own cell for sorting) name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 without having to drag every single cell one at a time. I have over 2,500 cells and it would take too much work. My son is charging mee too much to do it.(i should start charging him for rent and food) What options do i have? all help is really apreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving cells in a column
Hi
Assuming your data is all in column A, in say C1 enter in C1 =INDEX($A:$A,(ROW()*3)-2) in D1 =INDEX($A:$A,(ROW()*3)-1) in E1 =INDEX($A:$A,(ROW()*3)) Copy down until the result in C,D and E equals 0 -- Regards Roger Govier "CTCrow" wrote in message ... I have some data that is all in order in a column, like this: name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 and i like to move the cells to look like this: (Each in it's own cell for sorting) name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 without having to drag every single cell one at a time. I have over 2,500 cells and it would take too much work. My son is charging mee too much to do it.(i should start charging him for rent and food) What options do i have? all help is really apreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving cells in a column
Say that these names and addresses are all in Column A on Sheet1.
*NO* spaces (empty cells) between entries Say you want to start your new data configuration on another sheet. Enter this formula *anywhere* on the new sheet: =INDEX(Sheet1!$A:$A,3*ROWS($1:1)+COLUMNS($A:A)-3) Drag across 2 columns to copy, to a total of 3 columns. Then select those 3 cells, and drag down the 3 cell *selection* as far as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "CTCrow" wrote in message ... I have some data that is all in order in a column, like this: name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 and i like to move the cells to look like this: (Each in it's own cell for sorting) name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 without having to drag every single cell one at a time. I have over 2,500 cells and it would take too much work. My son is charging mee too much to do it.(i should start charging him for rent and food) What options do i have? all help is really apreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving cells in a column
With the data in A1:A2500, put this in B1:
=OFFSET($A$1,COLUMN()-2+3*(ROW()-1),0) And drag across to D1 Then select b1:d1 and drag down until you run out of data (around row 834) This assumes that each group has exactly 3 cells associated with it. CTCrow wrote: I have some data that is all in order in a column, like this: name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 and i like to move the cells to look like this: (Each in it's own cell for sorting) name 1 street 1 city 1 name 2 street 2 city 2 name 3 street 3 city 3 without having to drag every single cell one at a time. I have over 2,500 cells and it would take too much work. My son is charging mee too much to do it.(i should start charging him for rent and food) What options do i have? all help is really apreciated. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arrow Keys Moving Window Frame instead of Moving Between Cells | Excel Discussion (Misc queries) | |||
moving alot of data (10000 cells) from a column to a row | Excel Discussion (Misc queries) | |||
Moving data from multiple cells to one column | Excel Discussion (Misc queries) | |||
moving column/cell data to rows/cells fails | Excel Worksheet Functions | |||
Why is my tab key moving my cursor from column A to column k? | Excel Discussion (Misc queries) |