GRcade

[img]pasteboard.co/H1e5J5e.png[/img]





https://ibb.co/dBqKuw

Can’t even get the embedded img link to work

So anyway, how many excel whizzes in the house without a spouse. Sexual favours shall be provided for a solution.

I’m trying to write a macro that will automatically update my spreadsheet for week on week growth. To give an example, for the UK the last week's figures were 745, hence why there’s a 6 as the last number - it grew by 6 from last week.

To calculate the growth between sheets I’ve done a simple SUM calculation. I wrote a macro to do that too but realistically just doing one cell and filling down is quicker just now - what I really want is a macro that would take into account changing positions in the league table from country to country. Is that even possible?

Again to give an example, if Poland gained another user they would take over the Netherlands but currently my macro doesn’t realise this so would give incorrect growth figures for these two countries.

I’m hoping it’s not too difficult, just I’d forgotten that macros even existed in Excel until this morning. Any help would be mad appreciated.

I'm not an Excel whizz, but here's how I would structure it.

Data input:
Table 1.
Country, Value

Comparing this week to last week:
Table 2.
Country, This Week Value, Last Week Value, Difference
[Get from Table 1], [Get from Table 1], [Get from previous version of Table 1], [= This Week Value - Last Week Value]

Ranking the countries:
Table 3.
Country, This Week Rank, Last Week Rank, Change
[Get from Table 2], [= RANK(...) on Table 2], [Get from previous version of Table 3], [= This Week Rank - Last Week Rank]

Sorting that table by rank:
Table 4.
Rank, Country, Change
1, [= VLOOKUP(...) on Table 3], [= VLOOKUP(...) on Table 3]

The trick in Table 4 is having a column that looks just like 1 / 2 / 3 / ... so that you can VLOOKUP that rank from the previous table.

Turn it off and on again

Everybody please stop pushing Excel to such extent it breaks and you complain at me that it's broken. strawberry floating excel and strawberry float people who use it with loads of linked data and gooseberry fool.

Tell Karl his brother is dead wrote:I'm not an Excel whizz, but here's how I would structure it.

Data input:
Table 1.
Country, Value

Comparing this week to last week:
Table 2.
Country, This Week Value, Last Week Value, Difference
[Get from Table 1], [Get from Table 1], [Get from previous version of Table 1], [= This Week Value - Last Week Value]

Ranking the countries:
Table 3.
Country, This Week Rank, Last Week Rank, Change
[Get from Table 2], [= RANK(...) on Table 2], [Get from previous version of Table 3], [= This Week Rank - Last Week Rank]

Sorting that table by rank:
Table 4.
Rank, Country, Change
1, [= VLOOKUP(...) on Table 3], [= VLOOKUP(...) on Table 3]

The trick in Table 4 is having a column that looks just like 1 / 2 / 3 / ... so that you can VLOOKUP that rank from the previous table.

Really appreciate this mate, not sure I totally understand the way you've laid it out but feel I'm making a bit of progress thanks to it now

Might come back in a bit with clarifying questions if i get stuck again

Qikzen wrote:Everybody please stop pushing Excel to such extent it breaks and you complain at me that it's broken. strawberry floating excel and strawberry float people who use it with loads of linked data and gooseberry fool.

Excel's the gooseberry fool. Unfortunately i too am gooseberry fool at it. I'm dedicating a few upcoming evenings and weekends to it in the near future after this is done.

Qikzen wrote:Everybody please stop pushing Excel to such extent it breaks and you complain at me that it's broken. strawberry floating excel and strawberry float people who use it with loads of linked data and gooseberry fool.

Wow. I set a low bar for helping, but you limboed right under it

Hexx wrote:

Qikzen wrote:Everybody please stop pushing Excel to such extent it breaks and you complain at me that it's broken. strawberry floating excel and strawberry float people who use it with loads of linked data and gooseberry fool.

Wow. I set a low bar for helping, but you limboed right under it

I strawberry floating hate Excel and people who use it.

YES YOUR SPREADSHEET YOU HAVE 20 WORKSHEETS ON WITH THOUSANDS OF CELLS ON ALL LINKED TO DIFFERENT SPREADSHEETS IS SLOW. strawberry float YOU STOP TELLING ME TO FIX IT I CANT.

Hexx wrote:

Qikzen wrote:Everybody please stop pushing Excel to such extent it breaks and you complain at me that it's broken. strawberry floating excel and strawberry float people who use it with loads of linked data and gooseberry fool.

Wow. I set a low bar for helping, but you limboed right under it

If terrorists worked out how to destroy all excel sheets in the world then we would be sent back to the stone ages.

Quick one:

Can we have a dedicated excel thread? I know there have been a few made over the course of the years. Perhaps it would be useful to consolidate them?

Sorry for not adding much to the thread but I am not able to look at the worksheet as I don't have access to a computer atm.

I'm down with that kezzer, I'll rename the thread.

I managed to do it

With many thanks to Karl, i ended up ditching the idea of macros as I don't think my skill level is there yet, but did use VLookups(which again I'd forgotten existed until mentioned in here) to mangle together a perfectly serviceable workaround. It's on my work laptop but I'll paste it here if anyone is curious - i thought I'd have to combine it with SUM to do the minusing part but actually that wasn't necessary.

Qikzen wrote:YES YOUR SPREADSHEET YOU HAVE 20 WORKSHEETS ON WITH THOUSANDS OF CELLS ON ALL LINKED TO DIFFERENT SPREADSHEETS IS SLOW. strawberry float YOU STOP TELLING ME TO FIX IT I CANT.

One of the monthly reports I used to receive took 1 minute 40 seconds to open on my laptop. Once the laptop gets over the shock of what it is opening, it normally runs fines.

Rocksleddy wrote:With many thanks to Karl, i ended up ditching the idea of macros as I don't think my skill level is there yet, but did use VLookups(which again I'd forgotten existed until mentioned in here) to mangle together a perfectly serviceable workaround. It's on my work laptop but I'll paste it here if anyone is curious - i thought I'd have to combine it with SUM to do the minusing part but actually that wasn't necessary.

There is shitloads you can do with Excel and if you aren't careful, you can lose a lot of time playing with things. I can't think of any spreadsheets I've created and use regularly that I've not added VBA code too.

Death's Head wrote:

Qikzen wrote:YES YOUR SPREADSHEET YOU HAVE 20 WORKSHEETS ON WITH THOUSANDS OF CELLS ON ALL LINKED TO DIFFERENT SPREADSHEETS IS SLOW. strawberry float YOU STOP TELLING ME TO FIX IT I CANT.

One of the monthly reports I used to receive took 1 minute 40 seconds to open on my laptop. Once the laptop gets over the shock of what it is opening, it normally runs fines.

The difference is, you don't tell support to fix that right? You know that it's an issue you can't fix.

Qikzen wrote:

Death's Head wrote:

Qikzen wrote:YES YOUR SPREADSHEET YOU HAVE 20 WORKSHEETS ON WITH THOUSANDS OF CELLS ON ALL LINKED TO DIFFERENT SPREADSHEETS IS SLOW. strawberry float YOU STOP TELLING ME TO FIX IT I CANT.

One of the monthly reports I used to receive took 1 minute 40 seconds to open on my laptop. Once the laptop gets over the shock of what it is opening, it normally runs fines.

The difference is, you don't tell support to fix that right? You know that it's an issue you can't fix.

I rarely tell support anything because they are a bunch of clowns. Most of the time I will fix issues myself.

Death's Head wrote:

Qikzen wrote:

Death's Head wrote:

Qikzen wrote:YES YOUR SPREADSHEET YOU HAVE 20 WORKSHEETS ON WITH THOUSANDS OF CELLS ON ALL LINKED TO DIFFERENT SPREADSHEETS IS SLOW. strawberry float YOU STOP TELLING ME TO FIX IT I CANT.

One of the monthly reports I used to receive took 1 minute 40 seconds to open on my laptop. Once the laptop gets over the shock of what it is opening, it normally runs fines.

The difference is, you don't tell support to fix that right? You know that it's an issue you can't fix.

I rarely tell support anything because they are a bunch of clowns. Most of the time I will fix issues myself.

I don't know who your support are, but it's USUALLY the users who are the clowns. Just saiyan.

Rocksleddy wrote:I'm down with that kezzer, I'll rename the thread.

I managed to do it

With many thanks to Karl, i ended up ditching the idea of macros as I don't think my skill level is there yet, but did use VLookups(which again I'd forgotten existed until mentioned in here) to mangle together a perfectly serviceable workaround. It's on my work laptop but I'll paste it here if anyone is curious - i thought I'd have to combine it with SUM to do the minusing part but actually that wasn't necessary.

I don't have time to look at this until the weekend at the earliest, but look into Index and Match functions which work together to be an even better version of Vkookup. Also, look into sumif/ sumifs, sounds like that might be something to consider.

Thanks i was thinking of looking into index and match as my concern now is that the ss will become unwieldy as it already seems slower and there will be another 50 sheets using this formula before we change to another.

+1 for Index and Match.

My job basically requires me to live in Excel.

Excel.