Any Excel masters in the house? • Non-Gaming • General Non-gaming • Eurogamer.net

Metalfish 20 Jan 2009 13:19:13 9,191 posts
Seen 1 year ago
Registered 16 years ago

Hey all, I've got a problem.

It being that I don't really know how to use excel. But anyway, my formula mad skillz are somewhat lacking, as are my "actually explaining the problem" skills, so bear with me.

What formula should I use to say "take data from every 'nth' cell down", so that, say, the 5th (in say, A5) piece of data goes here(B1) with the 10th (A10) piece in the next cell down(B2) and so on? Referencing each cell individually for this amount of data would take me all day and there must be a fairly simple way of doing this, right?

Thanks in advance.

catterz 20 Jan 2009 13:29:29 8,763 posts
Seen 8 years ago
Registered 19 years ago

Ok, so you've got column A with all of your values in. You want column B to read =A5, =A10, =A15.

The quickest way I can think of is typing into column B: a5, a10 (into cells B1 and B2). Then highlight both cells and double-click the square in the bottom-right corner of the selection or drag it as far as you need. This will write a15, a20, a25, etc, etc (following the pattern set out in the first two cells) into the rest of the column.

Then highlight column B and press Ctrl+H to bring up the Replace dialog and replace 'a' with '=a'. Click replace all. Job done.

EDIT: After that, delete all of the 0s from the bottom of the column.

Merlinho 20 Jan 2009 13:31:02 5,908 posts
Seen 2 years ago
Registered 16 years ago

That is a pretty ingenious solution.

Metalfish 20 Jan 2009 13:36:07 9,191 posts
Seen 1 year ago
Registered 16 years ago

Thanks a shedload. That's absolutely ideal. It even works in open office.

Nasty 20 Jan 2009 13:44:38 4,840 posts
Seen 8 hours ago
Registered 17 years ago

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\temp\test.xls")

intRow = 5
objExcel.Visible = true
For i = 5 to 200 step 5
objExcel.Cells(i/5, 2).Value = objExcel.Cells(i, 1).Value
Next

paste in to notepad, swap the file name and save as a .vbs

Edit: dammit. catterz's way is much quicker. mine had moar madskillz in it though.

Metalfish 20 Jan 2009 13:53:20 9,191 posts
Seen 1 year ago
Registered 16 years ago

Well, I commend your madskillz nonetheless.

RyanDS 17 May 2018 09:55:58 14,074 posts
Seen 13 hours ago
Registered 13 years ago

Hi All, having a maddening issue with a pivot table that I can't resolve. Anyone got any ideas?

I have a table with week ending dates, 3 years worth. this all looks good and when I filter I see:

02/03/2018
09/03/2018
16/03/2018 etc

In the pivot table though, which uses that column I only see:
>02/02/2015
Jan
Feb
Mar
...

Deleted user 17 May 2018 10:03:55

@RyanDS i had this issue recently.. i think you need to go into the pivot table settngs and select look at data at date level.

I will get exact steps in a minute.

and i have lost the workbook i had the issue o, sorry

Edited by Graxlar_v3 at 10:08:57 17-05-2018

craigy Staff 17 May 2018 10:07:57 9,500 posts
Seen 9 hours ago
Registered 15 years ago

RelaxedMikki 17 May 2018 10:39:13 3,214 posts
Seen 1 year ago
Registered 11 years ago

@RyanDS

Sounds like it has been grouped by months?

Try right clicking in the date column on the pivot table and select 'ungroup'.

RyanDS 17 May 2018 10:55:29 14,074 posts
Seen 13 hours ago
Registered 13 years ago

Cheers guys.

Managed to get around it by simply doing a new column in the source data going =[old column] and even though exact same data etc when I use it as the filter basis in the pivot table it lets me choose normally.

Weird.

THFourteen 17 May 2018 11:07:34 54,987 posts
Seen 18 hours ago
Registered 16 years ago

Must be something to do with the formatting of the cells in the old column then?