MS Excel / VBA help thread

Something like this in the relevant worksheet (right click on the worksheet tab and select View Code)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
Set KeyCells = Range("C24")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


Range("A1:B15").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("A24:A25"), Unique:=False

End If

End Sub

would filter A1:B15 based on criteria on A24:A25 when C24 is altered

Thanks!
I'm really trying to avoid using a macro as this will go to places where I won't be able to support.

I'm guessing, I'm at home and I'm not able to excel, that this could work if c24 looks at a cell on another sheet? So that you change sheet 1 cell, cell 24 changes, macro runs, and filter updates.

I'll try that when I get to work.

Sorry completely different question now.

When you use VBA to apply an autofilter, and then delete the cells that are visible, do you have to click ok? I'm pretty certain i shouldn't have to, and I'm tired and at home. Is it something like turn screen updates off?

I don't think you do. If you do, then the code to stop it (and restart again after):

Application.DisplayAlerts = false

Application.DisplayAlerts = true

Yeah I thought that would be it. For some reason the bodge job of bits and pieces I've knocked together has started asking for that.
I'll check thru properly tmrw.

Yep, that was missing. I had screen updating turned off.

So..
my code (ha..), looks at a csv file (test file approx 6000 records, >100 fields) and
hacks out the columns i don't need
uses autofilter to get rid of records i don't need
looks in a column replaces "this text" with "that text"
looks in another column replaces "this text" with "that text"

loops through the rows replacing "this text" with "that text" by array matching
loops through the rows looking in a column, if cell contains content of another array, delete row
formats text

creates individual worksheets based on unique names in another column, then saves.

i'm sure this could be faster, it took 42 seconds to process 6000 records.

if i replaced the loop and array matching with autofilter, would it be quicker?
should i be happy with what i have?

I mean, IDEALLY, i'd like to use autofilter on the array i've got in the loop. But you can't do that can you?

this bit:
loops through the rows looking in a column, if cell contains content of another array, delete row

is the bit I'm on about. it says "if the cell doesn't contain any of these things, delete the row"

Would it be possible to say "select everything but these things in the array, then delete the visible cells".

Why not autofilter, copy the result to a different place, delete all the source and replace with the copied data.

I'd been thinking that I could move it.

wait, so I could:
autofilter for one thing, paste to a new sheet, delete the original sheet. (this is the delete everything but the array step)
then
loop through creating new sheets by autofilter? (apply filter for the other thing, paste data to new sheet)

then apply the save separate worksheets loop.

Would that be quicker?

I mean, i'm going to use the one i've got on a regularly sized data set (approx 100 records max) so i'd expect it to be done < 40 seconds.

Are you planning on having multiple sheets output or do you just want one? You can filter on multiple conditions if you only want one output.

multiple sheets. there are 12 teams to split by and create workbooks for.

i guess it's the pay off between 12 separate filter and paste vs loop and paste?
(insert: dog on computer picture)

I suspect it may be quicker but whether or not it's worth it is a different matter.

As part of the filter you can automatically have it copy to a new sheet automatically

Range("A1:B15").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("A24:A25"), CopyToRange:=Range("Sheet3!A1"), Unique:=False

I guess you can have the macro create the new sheets, rename them and then as you loop through copy to a new sheet each time.

It's possible to sql out of text files if you're feeling slightly more ambitious and you're comfortable with using SQL queries to get just the columns out that you need and apply any filters.

If this is something that you are interested in, post and I'll stick up an example.

I'm always feeling ambitious, however: I need something that I can pass to my team and say "here, this will do this".
And then never think about it again.

I've just tested it. It's 18 seconds on a standard file. Finding savings can gtfo.

So apparently, while this filter is great, I've been asked if:
I can provide an average of column a if column b has a y in it.
So far so easy.

But, if there's a no, I need to replace the no value with the previous y.

So, if looking at dates from Jan 2015 to march 2016. There's a 3 month period from Jan to march 2015 I can't use. So I'd like to replace that with the first 3 months previous.

How could I do that? Create an autofilter for a larger period of time, then average if y?
This is for the control limits on the chart.

This is a project that I feel I'm having the goalpost moved on me. The dashboard shows the number of things per month. They want upper and lower control limits based on whether the value should be included. If they say no, they want it replaced with the first previous y.
Sorry, I'm off work and it's bugging me.

Ive done everything but this bit. It's really simple to do manually, you'd just look at the sheet and select the data, but automatically with no VBA and no user input is puzzling me. The idea is that users get this sheet, bang numbers into a table, the one bit of VBA does the table for the graph. People rejoice. Etc and so on.

I've got thoughts about offset and stuff.

So apparently, while this filter is great, I've been asked if:

I can provide an average of column a if column b has a y in it.
So far so easy.

Yep - averageif or an array formula (see attached).

Then I don't fully follow what you're after. If you can explain / illustrate this:

But, if there's a no, I need to replace the no value with the previous y.

So, if looking at dates from Jan 2015 to march 2016. There's a 3 month period from Jan > > to march 2015 I can't use. So I'd like to replace that with the first 3 months previous.

It sounds like the sort of thing we can formula our way out of

1 Attachment

Yeah. Ok. The average if is fine, even average ifs are fine. I'm king of the ifs!

I'm trying to count the number of things and then average them.
We're making a control chart, so you bang in the data in a table. Put a date in one cell, the start of the reporting period, and then say in another cell the number of months you want to go back. This number filters a table on another sheet.

From this table you get the average number of cases and the upper and lower links (* 2.66 for a standard dev). A chart is plotted showing the number of things per month and the average ucl and LCL for the time period in the table.
Now, in the table you originally bang the data in you can say "y" or "n" to include it in the average or the limits.

This is all quite easy.

However, if the person putting the data says "n" to a data point, it should be included in the chart of things per month. But not in the calc for average or ucl/LCL.

Instead the average and the limits should use values from a previous period in time. (I'm not happy with this, but apparently it's ok to say "this is a spike, these can be replaced with what we know to be normal data"). Apparently, if the time period were looking at is Jan 2014 - mar 2015. And if the months Jan - mar 2014 should not be included, we cAn substitute in data from 2013 that can be included. For example the months Oct - Dec.

I guess what I could do is:
Filter a new table with 24 months of data, plot the number of things per month using the time ranges entered on the first sheet.
And then this is where I fall down, if the table is big enough, averaging if will be ok.
There's a lot of confusion here as I'm not sure this is sensible, and what happens if there's a hige run of data missing, or patches of data. Where is this imputed from?

What is annoying is this is at work. And I'm not.

Also, I think if the person I was doing this for would actually let me demo properly rather than calling me on a mobile on a train on his way to the meeting....

I've just wanted to my partner on the train. I might just tell the person to have it my way, as the imputation bizness is flawed and risky. I could just plot average with y and n as separate lines. Also ucl and LCL as. Or as separate charts.

It's supposed to be a no touch spreadsheet for people with no skills or interest in excel and only in what the data is.

I'm not annoyed. Honest.

right ho...
i thought I'd come back to this.

1 Attachment

OK, I've just typed that into an excel :) what do we need?

I think a new column. If there's a yes, then in that new column put the averages of everything in 'monthly cases', only including the rows that have a 'y'.

Is that correct for a start?