To see less ads Register or Login ----- Daily Fantasy Sports games 18+

Copying and Pasting

A forum to discuss or share knowledge of spreadsheets.
Post Reply
stuboy
Dumbledore
Posts: 5457
Joined: 01 Sep 2010, 12:25
Location: Amsterdam

Copying and Pasting

Post by stuboy »

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

User avatar
Knulpuk
Dumbledore
Posts: 8986
Joined: 05 Dec 2005, 13:22
Location: MTA (Mid Table Anonymity)
FS Record: Ultra ultra consistent (see above)

Re: Copying and Pasting

Post by Knulpuk »

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.

stuboy
Dumbledore
Posts: 5457
Joined: 01 Sep 2010, 12:25
Location: Amsterdam

Re: Copying and Pasting

Post by stuboy »

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.
Thanks Knulpuk. You'll now see how simple I really am..... :shock: 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?

User avatar
Knulpuk
Dumbledore
Posts: 8986
Joined: 05 Dec 2005, 13:22
Location: MTA (Mid Table Anonymity)
FS Record: Ultra ultra consistent (see above)

Re: Copying and Pasting

Post by Knulpuk »

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.

User avatar
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

Re: Copying and Pasting

Post by Surprised »

Rather than sort, which will mix up the data, you can filter the data.

User avatar
eagle224
Grumpy Old Man
Posts: 2427
Joined: 10 Nov 2005, 22:14
Location: England
FS Record: Veteran

Re: Copying and Pasting

Post by eagle224 »

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
Post Reply

Return to “Spreadsheets”