MS Excel / VBA help thread

Thanks for the reply. I appreciate it. I'll go through it properly this evening.

As a starting point I was just trying to replicate one of the formulas in the guide I posted.

My understanding from other videos is the OFFSET is to deal with the scenario where you have labeled headings so the formula can't SUM the first two as the first field it's adding is no longer has a cell name (like B1).

The teams must be in the same order.

I guess you can do that (and I've added a formula in to do it) but generally you'd be easier to just select the first number cell as the start of your range.

I don't think you landed on a particularly good page of examples.

Thanks a lot for that 👍

I think you're right and your IF formula is a much better approach that I can reproduce.

.

Say if someone extracted some data for financial years. And you have an id.
How do you say : for each id keep the row with the max fy?

I think you’d need to asssign the max fy by a lookup or switch?

I’m trying to dedupe a data set where a person can be at a place from a date.

If i combine id and the place name I’ll have a unique id. Which will let me do the “for this person and place”, how do I keep the row with the max fy?
Is my idea sensible?

It’s complicated by the fact there is a label for a full episode of stay in the place identifier. If I pivot the dataset, I can extract these easily. It’s the other recorsay I can’t get to.

R sql excel im not fussed here.
I can do a data table example later. (When I’m not cooking tea)

Off the top of my head in Excel, a couple of helper columns.

One a maxif which pulls out the max fy for a given id. One which is an if where the max fy equals the fy. Get rid of those where they don't match.

It's also possible to do this in powerquery but when I did similar in the past it took me a bit of experimenting to get the maxif working. It's the same principle though and a neater way, particularly if your data may change.

Ok! Id been doing this! Id deduped the id first and then used this to maxif the dates.

Then the maxif = the fy of the date, yeah! That should work.

I’ll try it.
Thanks

I think this did work. It seems to give the same number of rows as the more complicated fuck around I was trying. I call that datavalidation, right?

Lots of nice new text and array manipulation formulas finally hitting mainstream Excel.
This lot https://techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/ba-p/3186066

I'm sure I've done this many times before but googling is just bringing up excessively complicated scenarios. I want to run a batch file from Excel VBA, no need to pass any arguments to it or anything. What should I be doing? Cheers

shell

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/shell-function

' Specifying 1 as the second argument opens the application in 
' normal size and gives it the focus.
Dim RetVal
RetVal = Shell("C:\WINDOWS\NOTEPAD.EXE", 1)    ' Open Notepad.

Cheers, that's what I'm trying but can't get it to run a batch file

Sub Editbatch()

Status = Shell("notepad c:\Users\aggi\Desktop\Createfolders.bat ", 1)

End Sub

Opens notepad and loads the batch as expected.

But neither

runs the batch. Just get this error. It works fine if I double click it.

Hi I need some excel help if possible. I have a list of post codes and names and want to put them on a map to show who deals with which post code. Problem is there are lots of gaps

How I fill in the gaps so that the map looks complete?

I can’t for example put Edward SE1. Because some else could be covering SE2.

I’m unsure what you want here

Is it a point on a polygon map of post codes?

Do you want to say "there is no one covering this postcode" as a category?

Sorry. I’m shit at explaining myself.

So I have a list of post codes and names assigned to those post codes and need to illustrate this on a map.

I’ve done that but there are gaps. Either I’m missing some post codes or I’ve done something wrong in excel.

You should be able to test whether you are missing post codes in your data, by finding out the postcode for a gap on the map, then looking for it in your data.

To help further, we'd probably need to see your data in this case.

Ok.
First things first.
How big is the data thing?
Because there are a lot of postcodes in the UK and that file is huge, from memory.

I think an easy thing to do would be
List 1 : all the postcodes in the area you are looking at
List 2: all the postcodes in your map
List 3: all the names and their postcodes

First:
Are all the codes in L1 in L2?
Are all the codes in L2 in L1?

http://chandoo.org/wp/2010/06/17/compare-2-lists-in-excel/
https://chandoo.org/wp/compare-lists-excel-tip/

But yeah, give us a quick table or something and I'm sure someone will sort you out.

It’s weird because I’ve checked an actual post code map and they’re all there. But there’s gaps

My mind has gone blank here and I just can't remember how to do this. In Power BI how do I get the Legend labels next to each bubble rather than, as they are at the moment, all in a column. I know I could do it but I cannot remember how and google isn't helping.

1 Attachment

If we look here:
https://fingertips.phe.org.uk/search/life%20expectancy#page/1/gid/1/ati/402/iid/650/age/1/sex/1/cat/-1/ctp/-1/yrr/5/cid/4/tbm/1

you should see a load of indicators for life expectancy (woo!), spine charts are ace. These are great - but how the fuck can i replicate them in excel (it's a series of different bar charts with formatting and shite isn;t it?) ? Has anyone got a template?

It's a stacked bar chart of interquartile ranges with markers of higher percentages if that helps.

a series of different bar charts with formatting and shite

I'd say yes - two barcharts and a scatter graph, superimposed with transparency and hidden axes.

Ain't nobody got templates for that stuff.

Do it in R instead. trolllol

I get given an xls extract with a list of names, dates worked and the project they're working on.

The different days people work, and people changes mean where the info appears moves up/down. However the columns are always the same.

It appears vertically(ish) and I need it in a horizontal format. I can do this reasonably easily with a pivot table.

BUT first I have to:

1. Copy the Project name into the empty cells below to
   
2. Copy the Individual's name into the empty cells below
   
3. Change the hours worked to from value to 0.5 or 1 using an IF formula
   
   

This is fairly time consuming and seems like it could be automated, right?

Rule 1. If cell has a value (ie a name) and cell below is empty then copy and paste value.
Apply to column B
Apply to column C

Rule 2. in column G apply IF formula

Should I be looking at VBA? Will it work?

Cheers.

EG - rows A-G is what I get, I onwards is what I ultimately want:
https://docs.google.com/spreadsheets/d/1S8I1DwLBInGWNBN9yLSOf9h2rxqyhwjlNJlbmeChKag/edit?usp=sharing

2 Attachments

Have you used power query before - are you using excel?
Power query can take the data and unpivot it for you.