The Excel Thread

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

PostRe: The Excel Thread
by Rocsteady » Thu Jan 04, 2018 9:47 am

I'm struggling with index match simply because it's easy to minus say row 49 from 49 across different sheets but when country takes over another in one sheet, i haven't yet figured how to have it fill down but automatically recognise that the value in row 49 in sheet two has moved to row 48. Shall perservere.

Really should do my actual work too :shifty:

Image
User avatar
Rocsteady
Member
Joined in 2008

PostRe: The Excel Thread
by Rocsteady » Thu Jan 04, 2018 10:45 am

strawberry float it, the vlookup function's good enough.

Anyone completed a good Excel course before? So many online ones that it's difficult to choose. Plus I'm not sure what level to sign up for, I'm not great but concerned that beginner ones would be like, 'here's how you save a file and type in data'.

Image
User avatar
1cmanny1
Member ♥
Joined in 2008
Location: New Zealand

PostRe: The Excel Thread
by 1cmanny1 » Fri Jan 05, 2018 3:18 am

Don't bother too much with an Excel course, just learn as you go. You can find anything by Googling, Excel is very popular.
Maybe just watch a few videos on how to work Lookups etc, Pivot Tables. The basics.

The best place where people love to help is here: https://www.reddit.com/r/excel/
Usually super helpful.

Qikzen wrote:
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.


Nope 100% support people are the clowns. They don't know anything.
For example if they did, why would my Excel crash so much? I just want it to automatically send emails full of pictures, how am I asking too much?

Harry Ellis wrote:+1 for Index and Match.

My job basically requires me to live in Excel.

Excel. :wub:


I find this hard to believe. I have a hate/love relationship with Excel.
Although the hate is mainly due to the crashes/slow downs, which is due to support IT incompetence.

Image
User avatar
BTB
Member
Joined in 2008

PostRe: The Excel Thread
by BTB » Fri Jan 05, 2018 9:37 am

I've started using google sheets a lot more compare to excel, basically the same but the query tool in sheets is very useful, a bit like SQL but I find it a lot easier for trial and error as well as there being a lot more help online with videos/forums etc.

But I do wish they actually taught you all the key excel functions (pivots, lookups etc) at school.

User avatar
KingK
Member
Joined in 2008

PostRe: The Excel Thread
by KingK » Fri Jan 05, 2018 9:47 am

I love Excel. It’s a very powerful piece of software and there’s always more to learn.

I’ve used MrExcel.com quite a lot in the past and can recommend it.

Re courses, skip any basic course as that will be new/open/save/close type stuff. Intermediate is where you should look if you really want to go on one but I find self teaching through trial and error and the occasional YT video or MrExcel thread helps me through.

I’m on iPad so can’t view your spreadsheet but the Index & Match, VLookup tips above are likely to be a good bet.

VB is awesome (I did a course 12 months ago) but never get to use it so have forgotten quite a lot. VB will sort it out for you I’m sure but someone else will need to help you I’m afraid. Sorry.

Good luck. I’m getting back to my spreadsheet now

User avatar
That's not a growth
Member
Joined in 2008

PostRe: The Excel Thread
by That's not a growth » Sun Jan 07, 2018 12:37 pm

Rocksleddy wrote:[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.


Thought I'd give this a look and just trying to get my head around it.

First thing: I can't see a difference between the pictures, did you mean to only show it once but duplicated it by accident?

Secondly: Am I right in thinking this is one of many very similar sheets, one for each week, so each week the figure on the far right changes and basically column 5 is a sum of "previous week total (from previous sheet) + number in column 6"

User avatar
Rocsteady
Member
Joined in 2008

PostRe: The Excel Thread
by Rocsteady » Sun Jan 07, 2018 12:56 pm

It's meant to be the same image I was just struggling to get it to show on the forum in any form.

Yeah there's a new sheet for every week - the client provides us with figures for column 5 so column 6 is the only one we need to deal with, which is the week on week growth. The simplest manual way to do it was to subtract this week's figures from last week's, then boom that's the growth. Was just trying to write a formula that would take into account when countries change rank so i could easily fill down the function and it would update automatically. Don't spend too much time thinking it through though, my Vlookup seems to do the job to an ok standard.

Image
User avatar
That's not a growth
Member
Joined in 2008

PostRe: The Excel Thread
by That's not a growth » Sun Jan 07, 2018 12:57 pm

Does it need to be a new sheet for each week?

User avatar
Rocsteady
Member
Joined in 2008

PostRe: The Excel Thread
by Rocsteady » Sun Jan 07, 2018 12:58 pm

Yeah they like it screenshotted and sent over to them and want to be able to look back at previous weeks on the excel file.

Image
User avatar
Alvin Flummux
Member
Joined in 2008
Contact:

PostRe: The Excel Thread
by Alvin Flummux » Sun Jan 07, 2018 1:37 pm

Is the MOS Excel certification worth getting?

User avatar
That's not a growth
Member
Joined in 2008

PostRe: The Excel Thread
by That's not a growth » Sun Jan 07, 2018 2:19 pm

Rocksleddy wrote:Yeah they like it screenshotted and sent over to them and want to be able to look back at previous weeks on the excel file.


I think I've got something that might work well for you.

https://mega.nz/#!ZklTDLRC!6DlaTwm4tbLq ... -PL0XSwKsE

New data goes in the "RawData" sheet. Then on the "WeekView" sheet you choose the week you want and it put the country in order and tells you their total and how much was added that week.

Have a look and let me know.

User avatar
Rocsteady
Member
Joined in 2008

PostRe: The Excel Thread
by Rocsteady » Sun Jan 07, 2018 4:51 pm

That's not a growth wrote:
Rocksleddy wrote:Yeah they like it screenshotted and sent over to them and want to be able to look back at previous weeks on the excel file.


I think I've got something that might work well for you.

https://mega.nz/#!ZklTDLRC!6DlaTwm4tbLq ... -PL0XSwKsE

New data goes in the "RawData" sheet. Then on the "WeekView" sheet you choose the week you want and it put the country in order and tells you their total and how much was added that week.

Have a look and let me know.

Jesus man that looks like it took a while, i hope it didn't! That's awesome though gonna be studying it for a while to get it nailed down for the real stats tomorrow but even just looking at the functions used is interesting as.

Thanks mate, that's well class.

Image
User avatar
That's not a growth
Member
Joined in 2008

PostRe: The Excel Thread
by That's not a growth » Sun Jan 07, 2018 5:39 pm

No worries. If you have any issues figuring anything out give me a shout.

User avatar
Joer
Member
Joined in 2008

PostRe: The Excel Thread
by Joer » Fri Mar 11, 2022 3:45 pm

Bump!

I have an excel question for something that I can't work out how to Google and was hoping someone here could point me in the right direct.

Sheet A contains our list of jobs - Column B contains a data validation list for our customers on Sheet B
Sheet B contains our admin on our customers

What I need to happen is that if our example cell of B2 contains Customer A from the data validation list, then in cell B6 for invoicing notes, it pulls over the corresponding text relating to Customer A from the admin sheet.

Anyone got any ideas on what I need to be Googling to make that happen or know how to do that themselves?

User avatar
Sprouty
Member
Joined in 2008
AKA: SillySprout

PostRe: The Excel Thread
by Sprouty » Fri Mar 11, 2022 3:50 pm

Joer wrote:Bump!

I have an excel question for something that I can't work out how to Google and was hoping someone here could point me in the right direct.

Sheet A contains our list of jobs - Column B contains a data validation list for our customers on Sheet B
Sheet B contains our admin on our customers

What I need to happen is that if our example cell of B2 contains Customer A from the data validation list, then in cell B6 for invoicing notes, it pulls over the corresponding text relating to Customer A from the admin sheet.

Anyone got any ideas on what I need to be Googling to make that happen or know how to do that themselves?


Sounds like you need an xlookup, which will return a value in any given row/ column where specified value is found alligned in a corresponding line / column.

Older versions of xlookup include vlookup, hlookup and index match, but I would only use those if xlookup is not supported on your outdated version of Excel!

The silly neighbourhood vegetable.
User avatar
Joer
Member
Joined in 2008

PostRe: The Excel Thread
by Joer » Fri Mar 11, 2022 4:08 pm

Thanks, I'll do some Googling now and see if I can work that out!

User avatar
Stugene
Member ♥
Joined in 2011
AKA: Handsome Man Stugene
Location: handsomemantown
Contact:

PostRe: The Excel Thread
by Stugene » Fri Mar 11, 2022 4:16 pm

Joer wrote:Bump!

I have an excel question for something that I can't work out how to Google and was hoping someone here could point me in the right direct.

Sheet A contains our list of jobs - Column B contains a data validation list for our customers on Sheet B
Sheet B contains our admin on our customers

What I need to happen is that if our example cell of B2 contains Customer A from the data validation list, then in cell B6 for invoicing notes, it pulls over the corresponding text relating to Customer A from the admin sheet.

Anyone got any ideas on what I need to be Googling to make that happen or know how to do that themselves?

Without the exact data in front of me I'm going to have to extrapolate with examples.
Here's sheet 1
Image
And sheet 2
Image

We know that the names on sheets 1 and 2 are the same, but in a different order. We want to find the addresses and pull the addresses from sheet 1 into sheet 2.

First we use the MATCH functionality to find out where the names in Sheet2, A2 are in Sheet 1, column A. This will give us a reference we can use later. It looks like this, and this is what it returns
Image
That tells us that "Gary" is in the 10th row of data in our function. But hold on - "Gary" is actually in Row 11 of the sheet. Thats because we didn't include our heading in the lookup array (Sheet1!A2:A11 <- note the A2 not A1). We don't want to include the title, because we aren't looking for it, so what we can we do? Well we know that it's only one row, so we can say that for every result, +1 to it and you get the real row. You can imagine what an 11 looks like so lets move on.
OK so we want to use this for every row on sheet 2, so we need to make that look up array constant. Lets do that by plonking $s in. Then drag it on down to the bottom of the row.
Image

That's the easy bit done. We know what row the data is on, so we can use that in our next formula. This time we will use an INDIRECT. This basically returns the content of a cell, but what it also allows you to use a string to determine where it should pull the data from. This allows you to concatenate data together (using &). Like this:
Image
So the INDIRECT here is going to look for "B"&1, which concatenated together is "B1". Note that whenever you use text in an INDIRECT you need to use quotation marks. It then returns the contents of B1 - Addresses 2.
But we want it to look at Sheet 1, so we say
Image
Now, we can put these two statements together. In the first formula, we used MATCH to get the row - so lets use that row information in our INDIRECT. But lets not just reference the cell with the data, let's combine the whole formula into one.
Image

Hope that helps you out.

Image
Taint
User avatar
Mommy Christmas
Multiball!
Joined in 2009

PostRe: The Excel Thread
by Mommy Christmas » Fri Mar 11, 2022 4:18 pm

Thats better than my description - Use CONCATENATE.

:dread:
User avatar
Stugene
Member ♥
Joined in 2011
AKA: Handsome Man Stugene
Location: handsomemantown
Contact:

PostRe: The Excel Thread
by Stugene » Fri Mar 11, 2022 4:21 pm

Mommy wrote:Thats better than my description - Use CONCATENATE.

You can also save some time using =A1&B1&C1 rather than =CONCATENATE(A1,B1,C1)

Image
Taint
User avatar
Joer
Member
Joined in 2008

PostRe: The Excel Thread
by Joer » Fri Mar 11, 2022 4:36 pm

I feel bad that I was able to do what I need with the XLOOKUP after how much time you spent doing your example.

Thanks everyone for your help.

Another question off the back of this...

Image

I've put the same formula in two cells. The one I was testing to get it working which works, and the other where I actually want it to appear which doesn't. (You can click on the image to enlarge it)

Is there something obvious I'm missing about why this would happen?


Return to “Stuff”

Who is online

Users browsing this forum: Billbones84, Google [Bot], Green Gecko, Grumpy David, kerr9000, poshrule_uk, Robbo-92 and 423 guests