MS Excel / VBA help thread

oh - I should give that a go ^

Sometimes, before I get my nested IFS right I like to write helper columns for each part of the statement so that if the condition I want = 1. Then I work out what the combinations are that I want. Then I write a nested if. If I'm having one of those days where I can't hink it through.

thanks - works perfectly

what i dislike about this, and hlookups too for that matter, is that it is not obvious to me what this is doing.

Happy to have a look rodders but it won't be till the evening.

Match looks down a list of values and tells you where the thing you're searching for is.

So in the pic, it searches 1, 2, 3 and returns 1 (because 1 happens to be the first item in the array, not because it's the number 1 you're looking for).

Then index returns the nth item in an array.

So you can mimic a normal vlookup with index/match

=VLOOKUP(A1, B:C, 2, FALSE)
=INDEX(C:C, MATCH(A1, B:B))

Xlookup is the way the cool kids are going now.

I think this is another answer of "that isn't easy to do".

In my experience Excel sees the conditional formatting rather than the background colour as the format so functions which use the colour don't work.

Although, if you're using conditional formatting rules to highlight the cells can't you use the same rules in formulas to get the info. I'd guess sumifs or a pivot table or similar would return it.

Cant you just write the formula using that same rules you are using to apply the conditional formatting?

=VLOOKUP(A1, B:C, 2, FALSE)

=INDEX(C:C, MATCH(A1, B:B))

Needs the 0 in the MATCH ;)

=VLOOKUP(A1, B:C, 2, FALSE)
=INDEX(C:C, MATCH(A1, B:B, 0))

I need to generate folder names based on client names. They're already in an Excel spreadsheet that gets updated from our billing system and creating the folders is easy enough. However, it's occurred to me that I need to strip out illegal characters.

I think there's about 10 of them so I don't really want to use a nested substitute function. Is there any other option to replace multiple characters or is down to using a macro?

Can you do it in bi?
Make a list of legal and illegal and then the substitutions that way.

But then why bring into bi when already in excel.

Ignore me.

Macro I think.
Or, to avoid nested then this is a little easier as it's just cut/paste a range.
But it's still a bit crap and it's pretty much nested anyway.

1 Attachment

Cheers. Think I'll probably go macro and incorporate creating the folders into the macro (I currently just paste it into a batch file).

@Chalfie Powerquery probably would do it neatly and just spit it out in a new table in the same spreadsheet. I may try that method if I get some free time as it's bound to cock up in some way.

In r I think it would be
Str_replace %in% listofillegal characters

(Roughly)

Power query is, for whatever reason, M.

I think R comes from S.

It stands for 'Mashup' apparently.

It is case sensitive which was incredibly annoying until I discovered that.

Hey!
Here's a question about xlookup!
I know it can spill over to columns to return all the values in the return array (neat!).
But.
Say i want to search someone's DOB. this is not a unique ID.

if i do this:
=COUNTIF(Table1_1[Patient Date of Birth],B4)

I'll get the number of times that DOB is present.
How do I get this :
=XLOOKUP(B4,Table1_1[Patient Date of Birth],Table1_1[Patient Forename]:Table1_1[Patient Gender],0,0)
to spill to the countif value? can i?

Hold on
i think i can use FILTER.

Because it will take advantage of the spill functionality excel now has:

=FILTER(Table1_1[Patient Forename],Table1_1[Patient Date of Birth]=$B$7,0)

Say you have a file of data in one sharepoint location.
And you can import the data into excel using "Get data".
Normally, I'd put this in a table and go from there. But how can I :
connect and load as a connection
and
then filter
without the data table visual

if that makes sense?
i'm tired innit.

I'm not in front of a computer but selecting Add to data model or similar should do it. You don't have to load to a table

OK. Will look tmrw.
ta