[Misc] Excel help

[clapham_gull]

Some of the ridiculous formulas they had on spreadsheets at my place, total CPU burners. People would open them and sit there whilst calculations were updating and all their cores would max out for 15 mins

Ive changed the whole place to pretty much run on views I write on a sql server on a warehouse I designed up for BI/olap. Well Holap really. Excel is mostly a front end now for a bit of power BI

I wish I had that power, but SQL (or whatever) with Excel front end with power bi definitely the way forward.

I don't do formulas any more - because (as you know with power bi) you don't have too....

For individual projects, I usually like installing SQL Express on client machines and let them get on with it. I concentrate on writing code to get the data in (usually c#) , because I get presented with spreadsheets that are impossible to import as is - even when automatically created - because they been have written as "reports".

If you need any help importing spreadsheets into a DB, just ask because I've seen it all (all versions of Excel) and a have a "patent" way of doing it.

 

[Acker79]

Nothing worse than a vanity "look how clever I am" formula that no-one else can decipher.

I dunno... genocide, wild wealth inequality, starving children in supposed first world countries, being a palace supporter...

I have zero tolerance to spreadsheet vanity as it puts off others from learning.

It is reassuring to know I'm not as far behind as I thought, but I don't know that I was thinking vanity. I tend to assume excel has reached a point where there's pretty much a formula for anything anyone would want to do with a spreadsheet, but I don't often need to do much beyond text to columns, =proper and =vlookup, so assumed someone would come in with a simple one step solution. Seems I may have overestimated excel, since the only one step formula suggested so far looks like it might also come in one of those collect-a-volume-monthly sets you sometimes see advertised on TV.

I'd agree with you that if you're writing essentially a paragraph long formula it just seems too over the top - too much scope for typos and errors that are either not easily spotted, or take so long to iron out you're spending more time than the simple two simple step option (as well as the show-offy nature of it).

Most of the other formulas I've grown familiar with tend to be things I've googled over the years while using excel in nerdy casual ways (tracking my cinema visits/movie watching, the football stats I used to collate before changing jobs etc.). Not much more complex than If/Iferror, stdev

I have today learned concatenate, (though I think that word has as many letters as I used ampersands, so I don't know how much shorter it is!) and textjoin. Might be so long before I would need either I will have forgotten they exist.

 

[clapham_gull]

I dunno... genocide, wild wealth inequality, starving children in supposed first world countries, being a palace supporter...



It is reassuring to know I'm not as far behind as I thought, but I don't know that I was thinking vanity. I tend to assume excel has reached a point where there's pretty much a formula for anything anyone would want to do with a spreadsheet, but I don't often need to do much beyond text to columns, =proper and =vlookup, so assumed someone would come in with a simple one step solution. Seems I may have overestimated excel, since the only one step formula suggested so far looks like it might also come in one of those collect-a-volume-monthly sets you sometimes see advertised on TV.

I'd agree with you that if you're writing essentially a paragraph long formula it just seems too over the top - too much scope for typos and errors that are either not easily spotted, or take so long to iron out you're spending more time than the simple two simple step option (as well as the show-offy nature of it).

Most of the other formulas I've grown familiar with tend to be things I've googled over the years while using excel in nerdy casual ways (tracking my cinema visits/movie watching, the football stats I used to collate before changing jobs etc.). Not much more complex than If/Iferror, stdev

I have today learned concatenate, (though I think that word has as many letters as I used ampersands, so I don't know how much shorter it is!) and textjoin. Might be so long before I would need either I will have forgotten they exist.

Have a look at Power Bi (built into Excel) it can connect to almost anything, but simply you put your "data" in one sheet(s) and connect and manipulate it.

You don't need to write formulas for 99% percent of Excel tasks.

Take for your examples "Vlookup" or "Proper". You'll never use them again.

Want to an upper case a column ? Just right click and upper case it. Want to add two columns up - Just select them and click add.

IT professionals tend to hate Excel, but Power BI changes the dynamic. It's the best thing that ever happened to spreadsheets,

 

[clapham_gull]

I dunno... genocide, wild wealth inequality, starving children in supposed first world countries, being a palace supporter...



It is reassuring to know I'm not as far behind as I thought, but I don't know that I was thinking vanity. I tend to assume excel has reached a point where there's pretty much a formula for anything anyone would want to do with a spreadsheet, but I don't often need to do much beyond text to columns, =proper and =vlookup, so assumed someone would come in with a simple one step solution. Seems I may have overestimated excel, since the only one step formula suggested so far looks like it might also come in one of those collect-a-volume-monthly sets you sometimes see advertised on TV.

I'd agree with you that if you're writing essentially a paragraph long formula it just seems too over the top - too much scope for typos and errors that are either not easily spotted, or take so long to iron out you're spending more time than the simple two simple step option (as well as the show-offy nature of it).

Most of the other formulas I've grown familiar with tend to be things I've googled over the years while using excel in nerdy casual ways (tracking my cinema visits/movie watching, the football stats I used to collate before changing jobs etc.). Not much more complex than If/Iferror, stdev

I have today learned concatenate, (though I think that word has as many letters as I used ampersands, so I don't know how much shorter it is!) and textjoin. Might be so long before I would need either I will have forgotten they exist.

Concatenate is the same as A1& B1 etc...but I think (technically) supports more characters. VLookup is very common, but technically INDEX and MATCH run quicker because MS have optimized them.

However on the VLOOKUP front, when you use joins in Power Bi, vlookup will be dead to you. It doesn't even involve a formula....

You just select the two "sheets", tell it what columns to match and you have total control. None of that "key needs to be the far left column" nonsense.

You can even bring back everything in list 2 that you can't look up. Haven't used VLOOKUP since power bi was available. I always go for "easier" not what is new.

Power Bi is easier.

 

[Juan Albion]

Damn, I thought you wanted incel advice.

 

[maffew]

I wish I had that power, but SQL (or whatever) with Excel front end with power bi definitely the way forward.

I don't do formulas any more - because (as you know with power bi) you don't have too....

For individual projects, I usually like installing SQL Express on client machines and let them get on with it. I concentrate on writing code to get the data in (usually c#) , because I get presented with spreadsheets that are impossible to import as is - even when automatically created - because they been have written as "reports".

If you need any help importing spreadsheets into a DB, just ask because I've seen it all (all versions of Excel) and a have a "patent" way of doing it.

Yeah feel your pain, Ive been using DTS since SQL 7 and SSIS since 2005 for getting spreadsheets or whatever into dbs, mostly for contact centres, etl is my main focus with some qliksense or ssrs historically if I had to do front end, Intrigued on your patent way too. Ive just about got everyone at our place now getting their data into rows not columns Ive managed to always do most things without .net or c#, im.not really a programmer, I dont really class most sql as programming.
Sql express you can hack quite well to work like a server if needed for free, script over some msdb stuff to do database mail and use windows scheduler instead of sql agent

 

[clapham_gull]

Yeah feel your pain, Ive been using DTS since SQL 7 and SSIS since 2005 for getting spreadsheets or whatever into dbs, mostly for contact centres, etl is my main focus with some qliksense or ssrs historically if I had to do front end, Intrigued on your patent way too.
Sql express you can hack quite well to work like a server if needed for free, script over some msdb stuff to do database mail and use windows scheduler instead of sql agent

The trick is to import is as "cells".

So instead of trying to glean the meaning from the sheet, just create a table like this.

Workbook,
Sheet,
Row ,
Cell,
Value,
Data_type


You then do all the manipulation in SQL. Excel is notorious at presenting incorrect datatypes to DTS because it samples the sheet to determine them.

I use libreoffice automation to convert ANY Excel workbook (in any version) to an odd format that stores data in a single XML file (.fods). Having tried many methods (and even spoken to Microsoft) LibreOffice is best of class at converting spreadsheets to a common format, whether that be CSV or PDF.

You can script libreoffice to do batch conversions in the background. It's not a macro, just switches on the executable. It can deal with files a folder at a time.

I do it in windows, bit of a hassle because you have to call it so it thinks it's Linux, but it can be done. Probably easier with Linux, but I never got round to it.

I found a windows port of Bash that works, but others don't.

You then convert that XML into a simpler format using XLST transformations and import into the database as above.

One of the really nice things about the FODS format is that is stores two values for each cell. What the underlying value is and what it "looks" like after formatting is applied.

I also import the background colours of cells in case they have meaning. Even those bloody text boxes that idiots lay over sheets.

Sounds horrendous, but I got it all automated and I had to run it over hundred of thousands of legacy spreadsheets.

Don't work in IT by the way, do this in my spare time to help the "techies" out.

 

[maffew]

The trick is to import is as "cells".

So instead of trying to glean the meaning from the sheet, just create a table like this.

Workbook,
Sheet,
Row ,
Cell,
Value,
Data_type


You then do all the manipulation in SQL. Excel is notorious at presenting incorrect datatypes to DTS because it samples the sheet to determine them.

I use libreoffice automation to convert ANY Excel workbook (in any version) to an odd format that stores data in a single XML file (.fods). Having tried many methods (and even spoken to Microsoft) LibreOffice is best of class at converting spreadsheets to a common format, whether that be CSV or PDF.

You can script libreoffice to do batch conversions in the background. It's not a macro, just switches on the executable. It can deal with files a folder at a time.

I do it in windows, bit of a hassle because you have to call it so it thinks it's Linux, but it can be done. Probably easier with Linux, but I never got round to it.

I found a windows port of Bash that works, but others don't.

You then convert that XML into a simpler format using XLST transformations and import into the database as above.

One of the really nice things about the FODS format is that is stores two values for each cell. What the underlying value is and what it "looks" like after formatting is applied.

I also import the background colours of cells in case they have meaning. Even those bloody text boxes that idiots lay over sheets.

Sounds horrendous, but I got it all automated and I had to run it over hundred of thousands of legacy spreadsheets.

Don't work in IT by the way, do this in my spare time to help the "techies" out.

THAT is an approach I have never considered or seen. In my line Ive not needed I dont think, but its very very clever. Legacy DTS was far better at getting data types right rhan Ssis. It cheated a bit by assuming long variable data types. SSIS i change the sample data size to some 1000s instead of 200 in the vain hope it will get it right some times