Search results

Ah - I didn't realize there could be multiple overlaps. Thanks Narayan.

So I still don't understand aspects of this challenge. Can someone explain how the figure in H7 relates to the cells in H5:H6 and A7:B7? I understand we're dealing with overlaps, but I don't understand how the figure of 8:15 is derived in this case.

Vivek D: the IFERROR works for me. Are you array entering the formula?

bmwkim: can you read this and then update the thread at the post on the other forum so that people there know you have solved the issue: http://www.excelguru.ca/content.php?184

That doesn't do what you think, Hui...that start date just arbitrarily determines a cutoff point at which anything before will be effectively lumped into an 'Other' group. Carman, there’s no option in that dialog to group by fiscal year…when it comes to years, it’s either Calendar Year or...

Thanks Narayan. Had seen it, had forgotten it. Now it's going in the book.

If you have multiple files then I don't understand how that's going to help you...that creates a connection to one file only....something you could just as easily do without using that external data route. Rather, some options are: Use the code approach outlined here...

@shrivallabha: The problem with most ExtractElement formulas and UDFs - including the formulas and UDFs referenced above - is that they don't offer the user much firepower for trickier problems. For instance, what if you have several different delimiters that you need to use to pinpoint the...

What error are you getting? If "Unable to set the Visible status of the PivotItem class" then you may want to read this and see if anything rings a bell: http://dailydoseofexcel.com/archives/2014/05/21/learning-from-my-errors/

Dave, my man....can you post a sample file?

@Colin Legg: I see now that your formula works by replacing everything to the left of the desired element with a blank string - clever. So to generalise it we'd need to add another part that replaces everything to the right of the desired element with a blank, leaving just the element of...

Hi David. I like your method of filtering a crosstab. In the event that you wanted a non-VBA solution, another approach is to simply unpivot this data from a crosstab to a flat file , so that you can make a PivotTable out of it. THen you can achieve the same thing by filtering on the Course...

Cool!

Hi Colin and Smallman. I'd be interested to know how you would generalize those formulas so that they could work on any element and delimiter. Smallman: I had a crack at yours, and came up with this: =TRIM(MID(SUBSTITUTE(String,Delimiter,REPT("...

Ha! Yeah, I stumbled on this one too at first!

Okay. Quite a few potential options available. But first, can you upload a dummy file with some sample data will be feeding the reports? What I'm trying to ascertain is whether that data is in what's termed a flat file or a crosstab layout. If in a flat file, then we can probably use PivotTables...

It would help if you could upload a sample file.

In that case you'll need some VBA code. Check out http://www.pivot-table.com/2012/10/29/create-worksheet-for-each-pivot-item/

Here's a couple more options, using PivotTables

You can likely also use the INDEX function to do this, or the CHOOSE function. It would help if you would upload a sample file.

Kristen: This sounds like a job for a PivotTable. Are you familiar with Pivots? What version of Excel are you using?

You can actually do this without a macro. Excel can automatically create separate copies of pivots - one for each item in a PivotFilter - and put them in separate tabs? It's called 'Show Report Filter Pages' http://www.pivot-table.com/2012/10/29/create-worksheet-for-each-pivot-item/

Ahh, found them: http://forum.chandoo.org/threads/duration-of-stay-table-solved.11051/ http://chandoo.org/forum/threads/duration-of-stay-v2.15217/#post-94158

Hi Nick. I think I helped you with this some time ago. Do you have the original thread where you posted this?

Use the SUBSTITUTE function. =SUBSTITUTE(A2," ","_")