Be Excellent To Each Other

And, you know, party on. Dude.

All times are UTC [ DST ]




Reply to topic  [ 137 posts ]  Go to page Previous  1, 2, 3
Author Message
 Post subject: Re: Excel help
PostPosted: Tue Feb 18, 2020 20:53 
User avatar
Comfortably Dumb

Joined: 30th Mar, 2008
Posts: 12034
Location: Sunny Stoke
Cras wrote:
Also demonstrating serious proficiency with excel usually involves saying "don't use Excel for this"


One of my colleagues was on the phone with someone from Microsoft support today because something we were doing was working and we couldn't see how it was possible and their response was basically 'That shouldn't work :shrug: '

_________________
Consolemad | Under Logic
Curse, the day is long
Realise you don't belong


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 19, 2020 0:43 
User avatar
Can you dig it?

Joined: 5th Apr, 2008
Posts: 4756
Cras wrote:
Also demonstrating serious proficiency with excel usually involves saying "don't use Excel for this"


Yeah, I get what you're saying and it's an amusing way to phrase it - Excel is a tool that isn't suitable for all purposes BUT it is still very capable and for many is a familiar way of working so I do see why a lot of people use it as their first go-to (indeed, I do)

_________________
rumours about the high quality of the butter reached Yerevan


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 19, 2020 8:29 
User avatar

Joined: 30th Mar, 2008
Posts: 16611
Yeah, it can get out of hand, though. Quite a few of the things we've done at work are to replace some insane excel solution that's causing issues, but then there were probably bigger issues that got solved by their ad-hoc solution so I can see both sides.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Feb 19, 2020 9:12 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48830
Location: Cheshire
This isn't the talk of winners, here, lads.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Jul 27, 2020 15:03 
User avatar
UltraMod

Joined: 27th Mar, 2008
Posts: 55718
Location: California
There must be a more efficient way of doing this. Any idea? I'm rubbish at Excel.

Code:
=((I2*C2)+(I3*C3)+(I4*C4)+(I5*C5)+(I6*C6)+(I7*C7)+(I8*C8)+(I9*C9)+(I10*C10)+(I11*C11)+(I12*C12)+(I13*C13)+(I14*C14))/C15

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Jul 27, 2020 15:24 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49237
You could have a column J where J2=I2*C2 then copy that formula all the way down. Then at the bottom of J (which is presumably J15) have the formula sum(J2:J14) then have a cell that is just J15/C15

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Jul 27, 2020 15:24 
User avatar
UltraMod

Joined: 27th Mar, 2008
Posts: 55718
Location: California
Oh yeah, that’s nice. Thanks.

_________________
I am currently under construction.
Thank you for your patience.


Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:21 
User avatar

Joined: 27th Mar, 2008
Posts: 25888
Apologies for this being a dumb-as-rocks question, especially as my entire job used to revolve around Excel and I was pretty damned good with it, but having a baby turned my brain to potato.

I am making a meal planner (again, potato brain) with drop downs that feet in from the secondary sheets.

I want a text field in a cell to only show if another cell is populated by text from one of the options in a drop down. Does that make sense? Maybe a screenshot will explain better than I can.

Attachment:
excel screen.png


So, where it says 'with' or 'and' - I only want to show those if the thing has an accompaniment. So Bagel WITH cheese and ham, but not French Toast WITH <blank> - I just want that to say 'French Toast'.

It's in this kiddy's learning font because I'm making it for a little'un.


You do not have the required permissions to view the files attached to this post.

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:27 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 11142
Location: Devon
I would make a table with the main part of meal in one column and the extras in subsequent column and then use vlookup I think

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:35 
User avatar

Joined: 27th Mar, 2008
Posts: 25888
It’s ok, I’ve got it. Thank you though x

Attachment:
Screen Shot 2020-07-31 at 17.55.17.png


You do not have the required permissions to view the files attached to this post.

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:36 
User avatar
ugvm'er at heart...

Joined: 4th Mar, 2010
Posts: 22368
Assuming I've got what you were trying, i'd do something like this:

Code:
=IF(B5<>"","with","")


in the "with" boxes, and something similar in the "and" boxes. B5 being the cell under French Toast in this example, so if B5 is not empty, it shows with, if it is empty it doesn't show anything.

I think, haven't actually fired up a spreadsheet to test it.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:42 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 11142
Location: Devon
Attachment:
formimi.png


Something like that

I have called the first table "mains" and then in the cell to the right of French Toast, Sanwich Thin and Bolognese I have a formular like this:

=VLOOKUP(M4,mains,2,FALSE)

in the cell below that (where the first and would appear) I have

=VLOOKUP(M4,mains,3,FALSE)

and the one below that (where the 2nd would be appear) I have

=VLOOKUP(M4,mains,4,FALSE)


Bit Clunky, but hopefully you get the idea


You do not have the required permissions to view the files attached to this post.

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:58 
User avatar

Joined: 27th Mar, 2008
Posts: 25888
Trooper wrote:
Assuming I've got what you were trying, i'd do something like this:

Code:
=IF(B5<>"","with","")


in the "with" boxes, and something similar in the "and" boxes. B5 being the cell under French Toast in this example, so if B5 is not empty, it shows with, if it is empty it doesn't show anything.

I think, haven't actually fired up a spreadsheet to test it.


Roundabout:
Code:
=IF(B5="","","with")

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Fri Jul 31, 2020 17:59 
User avatar

Joined: 27th Mar, 2008
Posts: 25888
Malc wrote:
Attachment:
formimi.png


Something like that

I have called the first table "mains" and then in the cell to the right of French Toast, Sanwich Thin and Bolognese I have a formular like this:

=VLOOKUP(M4,mains,2,FALSE)

in the cell below that (where the first and would appear) I have

=VLOOKUP(M4,mains,3,FALSE)

and the one below that (where the 2nd would be appear) I have

=VLOOKUP(M4,mains,4,FALSE)


Bit Clunky, but hopefully you get the idea


I do, but I went with the other way as I'd already 99% set it up that way, but thank you :)

Honestly, I'm pretty sure my mind is permanent fudge now, but it's definitely been worse since lockdown.

_________________
Image


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu May 13, 2021 10:44 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48830
Location: Cheshire
Hello.

I can't work this out.

Column A has product numbers. In columns J,K, and L I want the delivery dates. These sit on a separate worksheet (product number in column F and delivery in date in P. One product can have more than delivery dates as separate orders.

I think I need an index function and array it, is that right?

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu May 13, 2021 11:28 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48830
Location: Cheshire
Oh so close now.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Thu May 13, 2021 11:50 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48830
Location: Cheshire
MaliA wrote:
Hello.

I can't work this out.

Column A has product numbers. In columns J,K, and L I want the delivery dates. These sit on a separate worksheet (product number in column F and delivery in date in P. One product can have more than delivery dates as separate orders.

I think I need an index function and array it, is that right?



Code:
=IFERROR(INDEX(PO!$P$3:$P$33333, SMALL(IF(PO!$F:$F:F=$A6, ROW(PO!P:P)-ROW(PO!$P$3)+1), COLUMN(A5)}},"")


Pleased with that.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 03, 2021 10:16 
User avatar
sneering elitist

Joined: 25th May, 2014
Posts: 4066
Location: Broseley
Hellloooo, I need excel help please!

I have a spreadsheet. In the first sheet are 2000+ products, with the following headings:
Vendor Product code Product name Category Secondary categories Price Quantity Status Shipping freight Free shipping Description Taxes Inventory tracking Zero price action Page title Meta keywords Meta description Search words Options Short description Out of stock actions Min quantity Language Product id Image URL Items in box Box size SEO name Weight Product URL

In the second sheet are 1000ish products (originating from sheet 1 but with the unwanted ones culled).

There are different column headings in sheet 1 and sheet 2, but they are the same products.

I need to put all of the rows from sheet 1 that exist in sheet 2 into sheet 3 - hopefully using the SKU (which is "Product code" in sheet 1, "SKU" in sheet 2).

I thought this - https://stackoverflow.com/questions/563 ... tching-key - would help but honestly it just made me more confused and I'm at the point where I will literally just pay someone to do this for me if I can't figure it out in the next few hours :D

_________________
i make websites


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 03, 2021 10:26 
SupaMod
User avatar
Commander-in-Cheese

Joined: 30th Mar, 2008
Posts: 49237
I'm assuming you've got access to a mySQL db - it might be easiest to just load both sheets into a couple of tables and then just use something "Select * from table1 where product_code in (select SKU from table2)"

_________________
GoddessJasmine wrote:
Drunk, pulled Craster's pork, waiting for brdyime story,reading nuts. Xz


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 03, 2021 10:32 
User avatar
sneering elitist

Joined: 25th May, 2014
Posts: 4066
Location: Broseley
Yeah, that was going to be my backup plan if I couldn't figure it out in excel. (I could have probably done it 10 times over in mysql by now. Stubborness sucks :DD )

_________________
i make websites


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 03, 2021 10:40 
User avatar
Gogmagog

Joined: 30th Mar, 2008
Posts: 48830
Location: Cheshire
I'd add a copy sheet 1 to sheet 3, and add a column at the end doing a VLOOKUP against sheet 2, , filter on #N/A and delete them.

_________________
Mr Chris wrote:
MaliA isn't just the best thing on the internet - he's the best thing ever.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Aug 03, 2021 11:00 
User avatar
sneering elitist

Joined: 25th May, 2014
Posts: 4066
Location: Broseley
That sounds easier than the pivot thing, I'll do that.

_________________
i make websites


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Wed Aug 04, 2021 1:40 
User avatar
Can you dig it?

Joined: 5th Apr, 2008
Posts: 4756
Much like Mali, I was also going to say it looked like VLOOKUP would be the solution to me (I can't do SQL or VBA though, so there may be a better way if you need to do this often).

_________________
rumours about the high quality of the butter reached Yerevan


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Oct 17, 2022 16:16 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38609
How do I make a formula return a blank cell and not just an empty string?


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Oct 17, 2022 16:25 
User avatar

Joined: 31st Mar, 2008
Posts: 8653
DavPaz wrote:
How do I make a formula return a blank cell and not just an empty string?


Not answering your question, but countblank will count "" returned from a formula, so that might help.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Oct 17, 2022 16:26 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38609
Cheers, I'll try it


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Oct 17, 2022 16:28 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38609
Yep, that worked for what I needed. Thanks Excel Guru


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Sep 23, 2024 8:45 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38609
Excel Help please.

I'm tracking my son's football team's results and the coach has asked me to add assists to the stats. I've been able to use the same formula that I use for tracking MOTM for now, where a countif checks a cell for a certain string, but it occurs to me that someone might get more than one assist in a game.

=countif(League!B24:Z24,"*PLAYERNAME*")

Does anyone have a way of counting how many times a certain string appears in a given cell?

Ta.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Sep 23, 2024 8:47 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 11142
Location: Devon
sumif?

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Sep 23, 2024 8:49 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38609
Is it that simple?


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Sep 23, 2024 8:51 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 11142
Location: Devon
I haven't looked, but I am pretty sure I was able to use sum if in a similar situation (where I needed to sum multiple cells, rather than just count them) If that doesn't work, let me know, and I'll try and actually mock up some data and see what's needed

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Sep 23, 2024 8:53 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38609
No, that just returns a zero. It needs to count all occurrences of a string across a range of cells where the string could appear multiple times in each cell, with other strings.

You can see why I'm struggling to wrap my head around it


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Sep 23, 2024 8:57 
User avatar
Isn't that lovely?

Joined: 30th Mar, 2008
Posts: 11142
Location: Devon
DavPaz wrote:
No, that just returns a zero. It needs to count all occurrences of a string across a range of cells where the string could appear multiple times in each cell, with other strings.

You can see why I'm struggling to wrap my head around it


Can you provide sample data? (with the names removed)

_________________
Where's the Kaboom? I was expecting an Earth shattering Kaboom!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Sep 23, 2024 9:20 
User avatar
ugvm'er at heart...

Joined: 4th Mar, 2010
Posts: 22368
=(LEN(B2)-LEN(SUBSTITUTE(B2,"sheila","")))/LEN("shiela")

That'll count for a specific cell, add the cells up for a range of cells I guess


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Sep 23, 2024 18:15 
User avatar
Ticket to Ride World Champion

Joined: 18th Apr, 2008
Posts: 11864
But what if they weren't called Sheila?

_________________
No, it was a giant robot castle!


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Mon Sep 23, 2024 19:10 
User avatar
Unpossible!

Joined: 27th Jun, 2008
Posts: 38609
Thanks gents. I've copied the system I use to track goals.


Top
 Profile  
 
 Post subject: Re: Excel help
PostPosted: Tue Sep 24, 2024 3:21 
User avatar
Can you dig it?

Joined: 5th Apr, 2008
Posts: 4756
Sounds like the data being input isn't all that helpful to begin with, why is there not just a # for goals (be it 0, 1, 2 whatever) and the same for assists.

Pleasant memories of Championship Manager 96/97 come flooding back.....ahhhhhhhh, good times

_________________
rumours about the high quality of the butter reached Yerevan


Top
 Profile  
 
Display posts from previous:  Sort by  
Reply to topic  [ 137 posts ]  Go to page Previous  1, 2, 3

All times are UTC [ DST ]


Who is online

Users browsing this forum: Columbo, PRISM and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search within this thread:
You are using the 'Ted' forum. Bill doesn't really exist any more. Bogus!
Want to help out with the hosting / advertising costs? That's very nice of you.
Are you on a mobile phone? Try http://beex.co.uk/m/
RIP, Owen. RIP, MrC. RIP, Dimmers.

Powered by a very Grim... version of phpBB © 2000, 2002, 2005, 2007 phpBB Group.