MS Excel / VBA help thread

Sorry for the slightly lazy question, but thought it was worth asking before I spend ages working it out for myself only to find out it's too clunky and not worth the hassle.

• xls is used as a questionnaire (very small volume ~10)
  
• 2 sheets.
  
• people need to fill sheet 1 or sheet 2, or both.
  
• ~50% of the content of the sheets is the same.
  
  

Is there are relatively simple IF or similar function to autofill the duplicate data in Sheet 2 from Sheet 2 to reduce people being lazy and not completing both? Even better if there is a way to only do this based on the input into one of the boxes which is a data validation(?) A/B/Both option box.

Or will it be a PITA and just be easier for people to fill/not fill and me work it out after?

Should have thought about it longer!

Just to answer the obvious question of why not = every duplication in Sheet 2 from Sheet 1.

If you're not filling Sheet 1, then Sheet 2 looks a bit shit full of 0's.

EDIT: Looks like you can remove 0 for zero values https://trumpexcel.com/hide-zero-values-excel/

I thought putting multiple links in a single Excel cell was impossible - the cell itself only supported a single hyperlink.

But I've just been sent an example which has two different links on individual words in a text cell, and have been asked to change the URL in one. I can't see any way to view or edit the URLs... any ideas?

[Edit: it's been done with loads of painstakingly tweaked text boxes on top of the cells. Fuck that. I'm going to pretend it's 'no longer supported in 365' or something.]

Assuming the Excel file is in .xlsx, you could rename that to .zip, unzip the file, find the XML for the sheet you want, edit that with a text editor, and then reverse the steps to build the .xlsx back again.

The file you want to edit is in folder xl\worksheets_rels in file sheet1.xml.rels (replace "sheet1" with the name of the sheet).

Is that a response to me, and does it actually work?

I'm not going to use it though, because I never want to be asked to look at this XLSX again.

Is that a response to me, and does it actually work?

Yes and yes, I just tested it.

I'm not going to use it though, because I never want to be asked to look at this XLSX again.

Fair dos. Just saw your edit, I can't blame you!

As the Excel security model is so crap this is also a great way to read data from files protected by a password. I've heard.

I suspect this isn't true. Current MS Office security is pretty decent.
I've lost a password before and found the 'password recovery' tools were not quick or straightforward. If you could just unzip and read then they'd be rightly crucified

I think they had a big bump in the strength of their passworded files a few years ago. I remember a variety of the password cracking tools I had stopped working.

The zipping and unzipping is good for a variety of stuff. You can remove corrupt pivot tables or you can change the settings so that double clicking on a number from a pivot created by power pivot returns more than 1000 rows.

I am trying to calculate the distance between two postcodes in my google sheet, and return the results as driving distance and driving time.

I have found a script and copy/pasted it into script editor, and it runs nicely for the first time.
But it seems to be permanently calling to maps to refresh the data and then starts erroring stating that the service has been invoked too many times.
Can anyone help with drastically reducing the number of calls it makes, or caching and loading the page from cached cell data?

1 Attachment

https://developers.google.com/apps-script/guides/support/best-practices has details of the cache service.

https://stackoverflow.com/questions/30157226/proper-way-to-deal-with-urlfetch-rate-limit-without-random-utilities-sleep

How many calls are you trying to make?

Maybe increase the sleep value on row 11, to 2000 ?

How come sometimes I put a formula in a cell and it just writes the formula in rather than running the process?
Have I got something disabled?

often means the cell format is set to "Text" rather than "General"

1 Attachment

Ah! I knew it was something simple! Thanks!

Has caught me out a few times! Normally when a file has been outputted from another system I find.

Power BI:
I bet you can do this but how?
I have a field, with many different values like
Text1-text2-text3

Where
Text 1 might be supermarket
Text 2 might be Sainsbury
Text 3 might be s

Where - replaces space and special characters.

It's a free text column, but I'm guessing there will be shared commonalities like
Supermarket
Restaurant
Bar
Pub

You can see where this is going.

What I want to do is probably
Switch when supermarket to supermarket
Pub to pub
Bar to pub
Etc

But I don't want to manually write the switch. I'm guessing there would be a way of comparing strings and finding groups and I can see it in my head as
Split at - count number of times text1 appears in field overall etc etc.

Am I making any sense?

BI question: why ,oh why, is power BI changing the colour of a visualisation?
If I reload data and refresh, on some of the visualisations the colours change. As a colour blind dude, this is causing many difficulties.

Ok relatively simple looking question compared to some of the others here, but I feel like I'm missing something completely obvious. Fairly big excel noob atm.

I want to display on a line graph how efficient 2 different sort types are, and one is better than the other. I want swaps as the X axis and then comparisons as the Y axis. I'm trying to make it so there's 2 lines that stop at the same 3 x axis values, and have different Y values.

I can't figure out how to lay it out so that when I select the data that the graph shows properly. It gives me different "series" atm. Anyone have an idea where I'm going wrong?

1 Attachment

Ok, I figured it out, I just took out "swaps" from A1 and then gave the x axis the swaps title. I can't figure out how to make it so there isnt a big gap to the left and right, but I'm happy out anyway.

Below is what I meant, if it wasnt clear

1 Attachment

Does that actually show what you want? It is showing your three x axis points as equally distant rather than as points on an axis from 0 to 203, not sure if it matters or not depending on what it is you are showing

Personally I always do graphs as Pivot Charts. Makes it much easier to rearrange (and I spend most of my time working with pivot tables so that's the way I think).

No it doesnt actually. I've done more thinking about it, and I'm pretty sure I'll have to do 2 separate graphs, one showing bubble sort, one showing insertion sort. They both have to show how many swaps and comparisons it took to organise 3 different sized groups of numbers (that were the same for both) in descending order.

I've no idea if I'm explaining this well at all.

9 numbers in the group
Bubble sort-22 swaps,36 comparisons
Insertion Sort – 22 swaps, 8 comparisons
18 numbers
Bubble sort- 99 swaps, 153 comparisons
Insertion sort -99 swaps, 17
27 numbers
Bubble Sort – 203 swaps, 351 comparisons
Insertion sort- 203 swaps, 26 comparisons

So this is what it looks like atm as best as I can figure.

1 Attachment

If you were doing 2 charts you could do one of swaps and one of comparisons, with a line for IS and a line for BS on each chart. That would make it more comparable than the two charts you have currently.

I can't figure out how to make it so there isnt a big gap to the left and right

Right click on the axis labels at the bottom > Format Axis > Select 'On tick marks' in then Axis position section

What you can do to put it all on one chart is to have it formatted like below, if you merged the three cells with Comps together and did the same for Swaps, then you end up with like a little sub-label on the chart. explaining that badly.

                   IS    BS
Comps    9
Comps    18
Comps    27
Swaps    9
Swaps    18
Swaps    27

That what you're after?
(I bodged the numbers on the bubble sort swaps so that you could see there were 4 lines not 3)

Highlighted all the data, inserted an X-Y Scatter with lines, then right click the graph, Select Data then click 'Switch Row/Column' and it moves the 'Numbers' column over to the X axis, keeping the rest as series

1 Attachment

Right click on the axis labels at the bottom > Format Axis > Select 'On tick marks' in then Axis position section

Perfect thanks. Yeah I wasnt sure how best to show it. I went with what I had above with the two different sorts on the 2 separate graphs. I'll find out what my lecturer says soon enough.

What you can do to put it all on one chart is to have it formatted like below, if you merged the three cells with Comps together and did the same for Swaps, then you end up with like a little sub-label on the chart. explaining that badly.

No you explained that well, I might have used that if I'd seen it in time. Sure I'll know for next time!