I need some help - treat me with kid gloves as I don't really understand excel.
I have 1500 lines of data.
Each 31 lines of data equate to a different individual and now I want to start comparing information. I'm trying to create a table from the data.
So let's say A1 is the first bit of info
The next bit I need is A32
Then A63
Then A94
(all increasing by 31 cells)
If I try and do =A1+31 - this obviously fails. So how do I create a sequence that I can see every 31st cell under each other?
Hopefully I've made it clear what I need.
Thanks
Copying and Pasting
-
- Dumbledore
- Posts: 5457
- Joined: 01 Sep 2010, 12:25
- Location: Amsterdam
- FPL:
- Knulpuk
- Dumbledore
- Posts: 8986
- Joined: 05 Dec 2005, 13:22
- Location: MTA (Mid Table Anonymity)
- FS Record: Ultra ultra consistent (see above)
- FPL:
Re: Copying and Pasting
The simplest way is to add a column to the left and put an x in that colum on line 1, then line 32, then 63. That should do it.
Now highlight lines 1 to 93 in the new column.
Then drag right the way down to the bottom - Excel should intuitavely guess you want an X in every 31st line and put an X next to each item you want starting with 94 and every 31st line thereafter.
Now sort your data on X.
Bobs your uncle.
Now highlight lines 1 to 93 in the new column.
Then drag right the way down to the bottom - Excel should intuitavely guess you want an X in every 31st line and put an X next to each item you want starting with 94 and every 31st line thereafter.
Now sort your data on X.
Bobs your uncle.
-
- Dumbledore
- Posts: 5457
- Joined: 01 Sep 2010, 12:25
- Location: Amsterdam
- FPL:
Re: Copying and Pasting
Thanks Knulpuk. You'll now see how simple I really am..... I highlighted lines 1 to 93. When you say drag right the way down, isn't that just highlighting again. Do I need to have copied cells 1 to 93 first and then click on cell 94 and drag from there? Then hit paste?Knulpuk wrote:The simplest way is to add a column to the left and put an x in that colum on line 1, then line 32, then 63. That should do it.
Now highlight lines 1 to 93 in the new column.
Then drag right the way down to the bottom - Excel should intuitavely guess you want an X in every 31st line and put an X next to each item you want starting with 94 and every 31st line thereafter.
Now sort your data on X.
Bobs your uncle.
- Knulpuk
- Dumbledore
- Posts: 8986
- Joined: 05 Dec 2005, 13:22
- Location: MTA (Mid Table Anonymity)
- FS Record: Ultra ultra consistent (see above)
- FPL:
Re: Copying and Pasting
Highlight column 1 to 93 so lets say cel a1 to a93 you now have a long oblong going down.
now hovver over bottom right - you will see a cross. Click it and with the button depresees drag the oblong down making it longer and longer - right the way down to line 1500. You should now have 50 new crosses against each 31st line.
Try out your dragging out by doing on a small population.
In a1 of a new page type Monday, a2 Tuesday. Highlight both cells and then try dragging down the bottom left - drag for 7 days and you should see the whole week appear.
now hovver over bottom right - you will see a cross. Click it and with the button depresees drag the oblong down making it longer and longer - right the way down to line 1500. You should now have 50 new crosses against each 31st line.
Try out your dragging out by doing on a small population.
In a1 of a new page type Monday, a2 Tuesday. Highlight both cells and then try dragging down the bottom left - drag for 7 days and you should see the whole week appear.
- Surprised
- FISO Jedi Knight
- Posts: 26528
- Joined: 13 Oct 2005, 18:32
- Location: Home
- FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4
- FPL:
Re: Copying and Pasting
Rather than sort, which will mix up the data, you can filter the data.
- eagle224
- Grumpy Old Man
- Posts: 2427
- Joined: 10 Nov 2005, 22:14
- Location: England
- FS Record: Veteran
- FPL:
Re: Copying and Pasting
Rather than touch the data, on sheet 2 cell A1 put =OFFSET(sheet1!$A$1,(ROW()-1)*31,0). Now copy this to A2, A3... etc until you have your list of data. The (ROW()-1)*31 in the formula selects every 31st row. The starting cell on sheet 2 must match the starting cell on sheet 1 to work correctly.
View Latest: 1 Day View Your posts