Search results

Bear in mind that my approach might become quite slow on very large datasets if you have many, many search terms and/or very long lists of transactions that you want to categorise, because my approach searches every row in the Details column for every single one of the items in the Lookup table...

See attached file. I'll be covering this in more detail in my forthcoming book, Excel for Superheroes & Evil Geniuses

I have a formula that will let you do this...let me drag it out, dust it off, and post it here.

Using PivotTables and Slicers would probably let you do this.

In addition to Narayan's answer, you can also interpolate between the values if you need. Although the formula to do this is a real beast! See http://forum.chandoo.org/threads/formula-challenge-010-two-way-interpolation.11305/

It looks to me like you're introducing those extra spaces needlessly in the first place, and that you don't actually need that CONCATENATE function in there, because those ampersands i.e. & are already doing all the concatenating: =IF(F62="x",Request!B62, "")&IF(F63="x", ", " &...

You can also add a new column to your source data, and filter on that. Note that it's best to turn PivotTable Data Sources into Excel Tables, so that if you add new data, it always shows up in the PivotTable on refresh. See attached. This will be covered in my forthcoming book, Excel for...

Hi Mark. In the book I'm writing - Excel for Superheroes and Evil Geniuses - I'm going to include a VBA routine that lets users set these things up effortlessly. And I have a much better way of doing it now that accepts a Flat File or PivotTable as a data source for dynamic dropdowns, and that...

No Harry...I'm not misunderstanding you. You are not listening. That sample post I provided the link to has lots of things directly relevant to your file, that you could fix yourself easily if only you would read it, and think about it's relevance to your file. You are wrong. They are NOT...

Whoops, I meant to have posted a link, but forgot. http://chandoo.org/wp/2014/03/28/tables-pivottables-and-macros-music-to-your-ears/

How many SUMPRODUCTS do you think you will end up with in your workbook, and how large are the ranges that you're going to be pointing them over. If the answer is 'many' and 'long', then you're probably going to want to rethink your approach. If possible, use PivotTables to accomplish what the...

Harry, further to what Luke said, this would merely address the symptoms, while effectively sweeping the root cause under the carpet. You need to address the fundamental design problems in your file. Again I refer you to the draft blog post I provided a link for at...

Hi Ravi. One of my most frequently used macros is my InstantPivot routine, that does different things depending on the context: If you point it at an existing PivotTable, it adopts the formatting of the source data If you point it at a range, it creates a PivotTable out of it, and turns the...

Ha...I didn't even know the ABs were playing. More of a fan of Microsoft than of Rugby!

@Harry0 This forum - staffed by volunteers - is never going to turn up an optimal solution for a complicated model that someone has already built sub-optimally. Because we don't have time to look over every user's requirements in total and work out how to better structure their data and...

Lesley - I use VBA to do this. Can you code? I can supply a sample file, but it's pretty complicated and I don't have time to amend it to your ends.

Hi Al. Sorry for the delay...got sidetracked. The VBA that I would use to crack this challenge is at http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/ but it's going to require quite a bit of amending to fit your needs. Question: in your sample file you don't have any...

You can also use the code I posted at the Contextures blog some time back. http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/

Dave - make sure you download that file from my dropbox link posted above. It's a sneak peak at some of the stuff to do with efficient formulas. Plenty more where that comes from. That stuff is just the tip of the iceberg.

HarryO: I've had a quick look at your file. There are many, many things you could optimize. I'm working on a draft post at the moment that covers some of them, which I've uploaded to http://1drv.ms/1xAHTDj (I can't seem to upload it here, for some reason.). With the guidelines in that draft...

Well, it's partly written. Or even mostly written. But there's still a few months of editing, polishing and burning of midnight oil to go!

Yup...I'm milking this yet unwritten book as much as I can!

Here's some draft content from my my forthcoming book, Excel for Superheroes and Evil Geniuses which will have an entire section devoted to troubleshooting slow files: And here's some posts I've done over at Chandoo.org that you should read, because they can probably help you remove...

Weird...that time it came through twice.