for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

101 watchers

GregP Sep 2022 3:58pm, 20 Sep 2022 139,340 posts	0 GregP Anyone going Olympia tomorrow?
icenutter Sep 2022 4:14pm, 20 Sep 2022 578 posts	0 icenutter =if(A1="Olympia","Hi","What?")
Badger Sep 2022 4:18pm, 20 Sep 2022 15,591 posts	0 Badger Garfield I'd be minded to use openpyxl to turn them into a spreadsheet, just read each file in individually and write it into a column cell by cell
Garfield Sep 2022 4:43pm, 20 Sep 2022 16,881 posts	0 Garfield Thanks Badger , I'll try that tomorrow...
Garfield Sep 2022 11:45am, 21 Sep 2022 16,885 posts	0 Garfield Very close...I bypassed the csv bit and went straight to xlsx...not xsl (I've dealt with a lot of xslt in the past, so that naturally comes to my fingers!)
Garfield Sep 2022 2:19pm, 21 Sep 2022 16,886 posts	0 Garfield Thanks again Badger!
Badger Sep 2022 2:32pm, 21 Sep 2022 15,593 posts	0 Badger Welcome! openpyxl is wonderful, I scripted pulling data out of two long text files and generating a pile of graphs that I could then dump straight into PowerPoint for quarterly steering groups. Ten minutes for what had been a few hours work.
Garfield Sep 2022 2:40pm, 21 Sep 2022 16,887 posts	0 Garfield I've taken one report and twisted it around for some of our sales people. The lucky recipient is on holidays until Monday...so she'll have a little surprise in her inbox!
SPR Oct 2022 8:52am, 12 Oct 2022 38,496 posts	0 SPR I have created a formula to find the maximum cost for each client (they will have multiple packages open at a time) every month for over a year. I have the following tabs Package List (PL, with Client, start date, end date, relevant cost. Dates (D), with a column for the dates for each month, so Apr, May, Jun etc. This could have been more elegant I'm sure. Summary (S), with Client, then columns for month. In the Summary month column I have the formula below Max(Sumifs (relevent cost, PL client, @client, start date, less than or equal to INDEX(D_relevant month), end date, greater than or equal to INDEX(D_relevant month))). The above is an array formula and if you took the max off it would return the cost for every day of the month in multiple cells. It works but is slow. Any way to speed it up. Happy to investigate power pivot/ power query if it would speed things up.
3M (aka MarkyMarkMark) Oct 2022 9:03am, 12 Oct 2022 19,324 posts	0 3M (aka MarkyMarkMark) SPR, as a PC Engineer followed by a job as an IT Service Manager, my answers would have been: -Add more RAM (it's plenty cheap enough!) -Check there's noting else running (like Chrome - notorious memory hog!. Other O365 stuff doesn't help much either - Teams and Outlook both cause my laptop to grind a bit slow on occasion.) -Consider upgrading your PC/processor (are you on a local machine or a virtual one? Can you book out a better spec VM?) -Are you using the 64-bit version of Excel which will allow multi threading/processors to work to the full? Nowadays, as a nebulously titled "Consultant" I say things like "Are you sure this is the right functions for this? I'd check out something like Power Pivot and/or Power Query"! (Yep, there's truth in the saying that a Consultant takes your own ideas, puts them in a presentation deck, and sells it back to you. Usually because you're on the right track anyway!)