The Excel Thread

Fed up talking videogames? Why?
User avatar
Rocsteady
Member
Joined in 2008

PostThe Excel Thread
by Rocsteady » Wed Jan 03, 2018 3:10 pm

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

Image

Image

https://ibb.co/dBqKuw

Can’t even get the embedded img link to work :fp:

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.

Last edited by Rocsteady on Wed Jan 03, 2018 6:36 pm, edited 1 time in total.
Image
User avatar
Karl
Daiakuma
Daiakuma
Joined in 2008
Contact:

PostRe: How Many Excel Whizzes in the House (plz god help)
by Karl » Wed Jan 03, 2018 3:48 pm

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.

User avatar
Hexx
Member
Joined in 2008

PostRe: How Many Excel Whizzes in the House (plz god help)
by Hexx » Wed Jan 03, 2018 4:15 pm

Turn it off and on again

User avatar
Qikz
Member ♥
Joined in 2011

PostRe: How Many Excel Whizzes in the House (plz god help)
by Qikz » Wed Jan 03, 2018 4:37 pm

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.

Image
The Watching Artist wrote:I feel so inept next to Stay Dead...
User avatar
Rocsteady
Member
Joined in 2008

PostRe: How Many Excel Whizzes in the House (plz god help)
by Rocsteady » Wed Jan 03, 2018 4:41 pm

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 :) :wub:

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

Image
User avatar
Rocsteady
Member
Joined in 2008

PostRe: How Many Excel Whizzes in the House (plz god help)
by Rocsteady » Wed Jan 03, 2018 4:42 pm

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.

Image
User avatar
Hexx
Member
Joined in 2008

PostRe: How Many Excel Whizzes in the House (plz god help)
by Hexx » Wed Jan 03, 2018 4:45 pm

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

User avatar
Qikz
Member ♥
Joined in 2011

PostRe: How Many Excel Whizzes in the House (plz god help)
by Qikz » Wed Jan 03, 2018 4:49 pm

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.

Image
The Watching Artist wrote:I feel so inept next to Stay Dead...
User avatar
Rocsteady
Member
Joined in 2008

PostRe: How Many Excel Whizzes in the House (plz god help)
by Rocsteady » Wed Jan 03, 2018 5:02 pm

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

:lol:

Image
User avatar
Kezzer
Member
Joined in 2012

PostRe: How Many Excel Whizzes in the House (plz god help)
by Kezzer » Wed Jan 03, 2018 5:14 pm

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. :oops:

Image
Image
Image
http://i.imgur.com/0CQJqX4.png

Check out the Digital Combat Simulator thread for some hardcore aerial combat! | Mumble | PCGT V | The Photography Thread |
User avatar
Rocsteady
Member
Joined in 2008

PostRe: How Many Excel Whizzes in the House (plz god help)
by Rocsteady » Wed Jan 03, 2018 6:34 pm

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

I managed to do it :D

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.

Image
User avatar
Death's Head
Member
Joined in 2009

PostRe: How Many Excel Whizzes in the House (plz god help)
by Death's Head » Wed Jan 03, 2018 7:29 pm

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.

Yes?
User avatar
Death's Head
Member
Joined in 2009

PostRe: How Many Excel Whizzes in the House (plz god help)
by Death's Head » Wed Jan 03, 2018 7:31 pm

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.

Yes?
User avatar
Qikz
Member ♥
Joined in 2011

PostRe: How Many Excel Whizzes in the House (plz god help)
by Qikz » Wed Jan 03, 2018 7:53 pm

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.

Image
The Watching Artist wrote:I feel so inept next to Stay Dead...
User avatar
Death's Head
Member
Joined in 2009

PostRe: RE: Re: How Many Excel Whizzes in the House (plz god help)
by Death's Head » Wed Jan 03, 2018 8:32 pm

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.

Yes?
User avatar
Kezzer
Member
Joined in 2012

PostRe: The Excel Thread
by Kezzer » Wed Jan 03, 2018 8:51 pm

:lol:

Image
Image
Image
http://i.imgur.com/0CQJqX4.png

Check out the Digital Combat Simulator thread for some hardcore aerial combat! | Mumble | PCGT V | The Photography Thread |
User avatar
Qikz
Member ♥
Joined in 2011

PostRe: RE: Re: How Many Excel Whizzes in the House (plz god help)
by Qikz » Wed Jan 03, 2018 8:55 pm

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.

Image
The Watching Artist wrote:I feel so inept next to Stay Dead...
User avatar
That's not a growth
Member
Joined in 2008

PostRe: How Many Excel Whizzes in the House (plz god help)
by That's not a growth » Thu Jan 04, 2018 7:28 am

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

I managed to do it :D

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.

User avatar
Rocsteady
Member
Joined in 2008

PostRe: The Excel Thread
by Rocsteady » Thu Jan 04, 2018 8:44 am

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.

Image
User avatar
Drumstick
Member ♥
Joined in 2008
AKA: Vampbuster

PostRe: The Excel Thread
by Drumstick » Thu Jan 04, 2018 8:56 am

+1 for Index and Match.

My job basically requires me to live in Excel.

Excel. :wub:

One man should not have this much power in this game. Luckily I'm not an ordinary man.
Image Image
"economically unviable"
-Oblomov Boblomov

Return to “Stuff”

Who is online

Users browsing this forum: Albear, Bing [Bot], Bunni, Cuttooth, Dowbocop, Google [Bot], Heskimo, lex-man, NickSCFC, PuppetBoy, Slayerx, Somebody Else's Problem, souljahsstory, Squinty, That's not a growth, Yahoo [Bot] and 35 guests