MS Excel / VBA help thread

Nested if FTW :-)

@Emyr
Such as?
(not being snide, I had to provide this information to someone in a spreadsheet so they can build tables elsewhere)

Disclaimer: I have no admin rights on my machine, I work for PHE (poorly funded agency of the Department of Health).

Hah, was mostly indulging in #casualtrolling but my job as a developer is generally made more difficult by people asking me to develop a single step of a spreadsheet/infopath/sharepoint based solution rather than having one app to do the whole workflow.

Ah balls.
I just lost a big old post.

tl;dr

I work in the civil service and interact with a lot of local govt / other civil service agencies.
The technology isn't always there.

I'm giving information to someone who manages the survey tool which runs on .NET. We're building an SQL db. I will have access to this for one part of my job.

Data about people being ill is collected in this tool
http://hpzoneinfo.in-fact.com/
data is extracted (for "analysis") into Access and then charted etc in Excel. (because of familiarity and other people need to work on the same thing and resource).

It's a big step getting an SQL db. There's not much money.
In addition, the new system that's being rolled out/developed (SGSS (which i think is by BAe)) allows us to interrogate data online. However, this is slow. And the reports look rubbish. And you generate the data and then extract it and then have to fuck around with it in Excel.
However, you can connect to this by sql / through access/through excel.
This is slightly subversive as the team that are leading the development of this system want everyone to do it their way and ask them to build queries to build reports. Which would put my, newly joined, team in the line of the "efficiency and savings" gun.

The role I have is making data/information easy to read for people who don't have an interest/time/money/desire to invest in the doing things superfastandwiththelatesttech.
These people are nurses, EHOs, doctors, and other health care workers (who put the data into our system).

I don't want to be here forever, but I love gypsy life, I want to try and develop some skills on the job and use my interest in public health to make progress and hopefully, hopefully, get a job somewhere else.

sigh. that's depressing.

#crywank

Have you encountered Tableau?

One of my colleagues was messing about with it, I thought the uses he had were static enough that using D3.js to render charts would be easier and cheaper, then his funded project ended and I went back to working on my team's main apps.

Tableau ftw. I've been using it for years, it is superb. Sounds like it would be exactly what you need. Once you give it a go, you will never use Excel for visualising again.
I'm now dabbling with Alteryx to sort out and combine awkward datasets in preparation for visualisation in Tableau.
It's fun :)

Have a look at this site, the blog is interesting. Will give you ideas.

Used tableau previously, didn't get too far along as that function stopped in last job.
Will look at again, but problem is patient data and where the analysis gets done.

What's the work around?

We can't put stuff on websites. Website stuff is centralised. No off plan mavericks.

If you can connect to the data by sql, then you can use Tableau to visualise it. Set up some dashboards and then publish them for user to drill in themselves. If you use Tableau Server then the dashboards are web based viewing. I am sure you can distribute self contained Tableau dashboards which are freestanding, and don't require the user to have Tableau installed to view. Just a local html file. Not sure but I think it is possible.

Thanks.
I'll have a look when I get back here in two weeks...

I have a sheet with 10k+ rows, each one is a date stamped visit by a customer (uid) to one of 11 session types, some of which are attended sequentially, but not necessarily so.

e.g.

Customer | Session Type | Date | other fields

I've been asked to calculate the average duration from 1st to 4th of the sequential session types, for those customers who've done this.

Using Pivot I can show Min Date and Max Date per customer for these session types, but can't exclude those who only did 1st but not 4th (or 4th but not 1st as their first happened before this set of data).

This is where I get stuck.

Anything simple I'm overlooking?

Can you clean out the ones you don't need for the analyis and then subtract 4 date from 1 date( or whichever way around) giving you a number?
Have I understood this?

On. U want to exclude those that haven't done 4 or haven't got 1?
Can you use average and subtotal together?

You might need to filter the data in your original sheet.

Wait for an expert to come in here...

I think, if I've understood this, add a column to give a 1 or 0 dependent on whether 4 visits have been made. Then I'd have another column saying if 4 visits column contains a 1 then calculate the difference in time.
I'd then average that.

Not 100% clear on what you are trying to do but could you not use a countif of the uid combined with average to get what you need? Similar to above but possible to do it all in one step.

If you do the column, you can then pivot, and then use the pivot to give you the dataset you need for analysis.
If you need to do that of course.

Obviously. I still use excel lke a granny trying to understand TiVo

I still use excel lke a granny trying to understand TiVo

What's TiVo? ;)

Thanks for the comments I'll have a play today.

I think the biggest problem is that there's no constant range I can refer to. Some customers have been only once, some 100 times across 1 session type, some 30 times across 6+ session types.

http://chandoo.org/wp/2015/08/25/summarize-filtered-values/

Anygood?

(good blog that)

http://chandoo.org/wp/2012/06/07/formula-forensics-023/
Count Filtered Data using Criteria

Cheers will see if I get time to try out stuff tomorrow.

If you're still stuck, email it to me, I'll script it using LinqPad.

Thanks, I didn't get to play today but will see how I get on and shout.

I often highlight duplicate values in cells in excel, I was wondering if there was an easy way to use it to highlight duplicate columns where all date is the same. I could then sort the data by high list and delete the columns. Sadly, there isn't a row with a unique vale for this data set.