[Closed] Just learnt a powerful new Excel technique

That's really rather clever; I've not seen the "--" bit before. Well done.

Good work Stoner... I may well pinch bits of that. Ta.

Very neat - but for the original problem, wouldn't SUMIF(...) do what you wanted?

I cant take much credit for it. I had to reverse something posted here
http://www.excelbanter.com/showthread.php?p=384506#post384506

and found I could use the principal in other ways.

Wow ...

SUMIF wont handle "LEFT" in the Range argument.

bit of an excel novice, but wouldn't vlookup do the same thing?

Oh, the nasty world of VBA.

Nice bit of stuff there. There was a VBA role going in Andover @ £650 / day recently...

Bit niche for me, though!

BTW - Im using to group cashflows for 300 rows of data on a dozen sheets that have nicely structured id codes that embed about 4 classifications in the code (i.e. building number, phase number, building floor and sub-plot for example).

This formula lets me make a summary page driven by a handful of drop down boxes that specify the classifications I want to filter by and then aggregates by them or even a combination of filters.

I hate VBA. I really only ever work in functions (which is what that ^ up there is) unless I need to run a macro.

<backs out of thread>

I used to spend hours trying to figure out how to create the right formula only for my mate to create one within 15 mins ... 😡

SUMIF wont handle "LEFT" in the Range argument.

Hadn't realised that - nice one and I'll tuck that away for when it might be useful!

You could create another column with the LEFT bit in it and still use SUMIF - but it's a nice trick to do it in the one cell.

but wouldn't vlookup do the same thing

short answer, no.

You could create another column with the LEFT bit in it and still use SUMIF

Unfortunately I dont "Own" the primary model. Im building a "translator" that overlays. I cant disturb the model itself by including columns, and whilst I could add loads of sheets to do the same thing (and that was going to be my back up) I really wanted to find an efficient single cell solution. And did.

I feel all fuzzy now.

Is confused 😳

The most powerfull excel tool i know is that if you click the bottom right corner of a box and dragg it down it'll copy the data from that cell down. Winner winner chicken dinner 😀

but wouldn't vlookup do the same thing
short answer, no.

That'll do me 😀

=vlookup("Stwer Pisses on Stoner's chips",DataRangeAllposts,1,0)
=#N/A

=ISERROR(Stoner is a God)
=FALSE

*Leaves thread once he realises it wasn't about colour coding a contact list*

I have a few 'Stoner nuggets' stashed away in notepad at work. This is another beauty to add to the list. 😀

Very neat bit of coding.

VBA's great until you have to check it. Much easier to work out what some whizz kid has done wrong if they stick to conventional functions

Stoner nuggets

😯

can I have them back? 😉

Now that's a good day's work, especially after just winning the Spanish motogp!

should'nt it be

=SUMPRODUCT(--(LEFT(A1:A10,LEN($F$1))=$F$1),B1:B10)

?

well duh. 😉
Just indicative code without cluttering it with locks.

neat slick solutions are always gratifying, but it's hard to get excited by excel 😉

erm.............what tyres would I need for this.....? 😳

Nice. Will have to remember that one and have a play when I get back to the office.

[img] [/img]

but it's hard to get excited by excel

Mine certainly gets hard when Im excited by Excel 😉

* reports thread for being the sort of shameless filth that no one should stumble into on a Sunday evening *

That's a great piece of work.

I'll be using that. And checking your other threads.

*notes those posting for when he is in power*

Klunk - Member
neat slick solutions are always gratifying, but it's hard to get excited by excel

Agreed

{Pretends he didn't dance round the kitchen last week after finally getting a graph to display properly}

Stoner, a fellow excexual.

I have a lot of...deep...stuff.

[img] [/img]

[img] [/img]

Hmmmm. I'm picking up extremely high levels of geek

cynic-al - Member
*notes those posting for when he is in power*

*adds cynic-al to the big shared table of individuals that The Spreadsheet Illuminati will keep from "power"*

geek is sexy
[img] [/img]

*notes those posting for when he is in power*

Cynical realises that you cant hope to run the world without the help of Excel geeks.

7 of 9. We have...met, vividly, in my teens.....i hope it wasn't in my twenties.

she and I have "assimilated" on a number of occasions...

I only ever use VBA code now and just do everything in that as it's just as quick once you're fluent and it has better maintainability and documentability than formulas in cells.

shame this [url= http://www.ioccc.org/ ]place [/url]doesn't have a forum, you'd fit right in Stoner.

I think you would befit from learning python or something, impressive little trick using excel stoner but it just screams wrong tool for the job!

d'ja miss the bit when I said it was a module for viewing an existing Excel model with?

But full marks for the traditional STW approach to a thread...want a new steel hardtail? I recommend a Santa Cruz Blur!

😉

Clever. Will have to see if I can use that. Chances are, not. But I still like it.

Powerful might be overstating it, though.

Wot footflaps said.

OK clever cloggses, excel me this:

how do I make excel look at text in 2 adjacent cells and then if both fit a defined pattern, change the contents of one of those cells. I'm going to macro the bugger after that and save myself about 5 hours a month

|manually titting about is OK|stoner's excel macro tips are 20 x better|

becomes

|manually titting about is OK|20 x less menial crap|

but

|working it out for yourself will never happen|stoner's excel macro tips are 20 x better|

stays as it is

Sumproduct function is truly the wonder of Excel, back in the good old days when people wanted to pay me with twiddling about with it I used to use it all the time.

This is the place to learn it: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Bob is pretty much recognised as the world expert when it comes to Sumproduct.

I hate VBA. I really only ever work in functions (which is what that ^ up there is) unless I need to run a macro.

UDFs are so handy sometimes (& powerful!); VBA can make things so much tidier.

cheers for the link Rob, will go and explore.

As for UDF's my problem is my models have to be used (and to a certain extent be understood) by the client and UDF's or reams of VBA behind a sheet just isnt going to cut it. The objective has always been to make the models slim and efficient using all the standard, documented, functions available.

Scaredypants,

how do I make excel look at text in 2 adjacent cells and then if both fit a defined pattern

what rubbish, try this:

1. Open Excel 2000.
2. Go to File, Save as Web Page.
3, Then click Publish.
4. Check the checkbox that says "Add Interactively With".
5. Click Publish.
6. Close Excel.
7. Open IE.
8. Click File, Open, Browse.
9. Go to where you saved it and click open.
10.Now you should be looking at a spreadsheet.
11. Go to all the way to row 2000.
12. Click column A and drag all the way to column ZZ.
13. Hit Tab and it should take you back to A.
14. Hold Tab all the way to column WC. (Press shift Tab in case you move back).
15. When you get there make sure WC is a white box and all the others are blue or green..
16. Hold Ctrl+alt+shift and left click on the "four puzzle pieces" thing (The
MS Excel Logo) on the upper left hand corner..

This is the place to learn it: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Nice link Rob - that explains it all very well

define your pattern and I can have a look at it for you

I'm betting there was a secretary involved somewhere along the line with that, eh scaredy 😉

nope, a supposedly qualified person 🙄 (we're all just muddling through, [u]none[/u] of us does IT and what I'm trying to do wasn't envisaged when they started with this crap)

Stoner, I've said it before and I'll say it again, I can see how you get all the girls.

[img] [/img]

slim and efficient using all the standard, documented, functions available.

Non sequitur

If you're using Sumproduct you've got a client who can't follow what you're doing anyway 🙂 Bits of code can make it all so much smoother and, even, easier for the client to follow & even edit, rather than formulae that rely on helper cells etc. Not least because you can add notes to the code.

There's sooooo much you can do in VBA to make your models slick that you can't use the front end for - go on, do it! You know you want to!

for the client to follow & [u]even edit[/u],

haha! Comedian! 😆

Anyway, this old dog isnt going to be learning any VBA tricks any time soon.

That link is great Rob, dont know why it never turned up on my google searches in the past. bookmarked now.

This is the place to learn it: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

haha! Comedian!

I'm seriously! Some people even appreciate that you view them as potentially competant and start to delve into the mysteries themselves.

Give a man a fish etc.

Anyhoo... try putting "Ozgrid" in your search text - most of the answers are in there somewhere.

Give a man a fish etc.

And give him a rod and he'll #REF for the rest of his life 🙂

Ive found some stuff off Ozgrid, but sometimes it's tricky trying to pick through some of the crappier user posts that clutter up otherwise useful threads.

tricky trying to pick through some of the crappier user posts that clutter up otherwise useful threads

Hmmm, sometimes not knowing too much about spreadsheets could be a good thing as it saves you getting to tied up with complexities (I haven't learn sumproduct yet).

[b]=SUMIFS(B1:B10,A1:A10,"AB*")[/b] works for me (Excel even highlights the parts of the criteria range matched by the wildcard) 😮

^ New in 07 (I'm still stuck in 05 🙂 ) Sumifs does the job nicely, but isn't as flexible as Sumproduct (which can be used for really unexpected things).

The database functions can also be used for such type things, but I never bothered to learn them.

Wow, do you guys know how to party or WHAT!

😉

^

It's either this or arguing with TJ.

Or getting on with what I'm sposed to be doing...

SUMIFS is a handy addition to 2010 and fills some of the functional gaps in SUMIF. But while it works for search AB* etc, I was using it as an illustration for using [u]any[/u] logical test and in any array as a SUM criteria, which SUMIFS cant handle.

Wow, do you guys know how to party or WHAT!

Mine's a diet coke and a taxi home by 9pm please 😉

SUMIFS is a handy addition to 2010

*Ahem*

New in 07

Rob needs to leave the glowing screen alone for a while

All help with knowledge share...

These guys we use quite regularly and encouraged.

[url= http://www.ozgrid.com/VBA/ ]VBA/Excel'ist help [/url]
[url= http://www.mrexcel.com/archive/Formulas/index.html ]Excel'ist help guru[/url]

New in 07

Hey, Ive only just moved up from 2003! Im still suffering culture shock!
🙂

3 pages on Excel!!! it's not often posts reach double figures.

as this is a flaunt your geek week, heres one of [url= http://forums.cgsociety.org/forumdisplay.php?f=98 ]mine[/url]

is the whole forum yours or were you trying to link to a specific post?

is the whole forum yours or were you trying to link to a specific post?

I think he was demonstrating his ability to include links in his text within a forum

Cheers Stoner, just saved me some time and no doubt more in the future with that formula.

good to hear. Didnt realise there might be a backlog of demand for the "Double Unary" (--) ! 😉

Ive just sat down to use the formula in earnest now. Lets hope it holds up ....

I am loving the XmlImport method in VBA at the moment enabling me to pull in loads of data from the web manipulate it and re publish it.

VBA is where its at there is only so much you can do with functions.