Search results

Hi @KDazed. Can you add into that sample file an example of the raw data that you have to work with? I'm still struggling to work out exactly what your raw data looks like, and so can't advise you how to transform it into your desired end result until I understand a little better what you have...

@KDazed - You still haven't responded to my previous question: What version of Excel are you using? Depending on version, you might be able to use PowerQuery and/or PowerPivot to do this, by constructing a pivot directly from your relational tables.

Why do you say that, Narayan? I think this could be whipped up easily enough using PowerQuery and a PivotTable. Or VBA and a PivotTable.

@KDazed What version of Excel are you using? Depending on version, you might be able to use PowerQuery and/or PowerPivot to do this, by constructing a pivot directly from your relational tables. As @NARAYANK991 says, it will be much easier for us to suggest approaches if you can provide more...

Pen and paper? New PC? Abacus?

@Luke M When iterating through the PivotItems collection, you should first set the PivotTable's .ManualUpdate property to TRUE before your PivotItems loop, and set it back to FALSE again when you are done. Otherwise the PivotTable will refresh after each and every PivotItem change. And that can...

@Deepak I disagree. I can't think of any good reason why MS shouldn't let us see all items. In fact, I think MS have screwed up the code in these search boxes because they take ages to load. @garylundblad I'm working on an add-in that you can use on Tables OR PivotTables OR DV lists that let...

I haven't looked at your file, but I've got a comment on @NARAYANK991's code above. When iterating through the PivotItems collection, you should first set the PivotTable's .ManualUpdate property to TRUE before your PivotItems loop, and set it back to FALSE again when you are done. Otherwise...

It's bad formula choice, for sure. But 'many' formulas <> 'too many' merely based on some arbitrary number of formulas. In other words: You could do this with formulas, and no macros You could do this with a macro, and no formulas You could probably do away with lots of formulas by leveraging...

@vletm Someone likes to have a lot of formulas in sheet ... I don't. Nor do I. But in this case, we still don't know how many formulas - or what size lookup range - we are dealing with. So there may not be a lot. And it all depends on what your definition of 'a lot' is. My definition of 'a...

Davealot: I'm not sure of the wisdom of jumping straight to a Macro solution, when your issue can likely be solved with more efficient use of formulas. For instance, instead of doing 7 VLOOKUPS on each item, you can do one MATCH on each item, and then via an INDEX function reuse the result from...

Thanks Chihiro.

Howdy folks. I need a file cleaned via Rob Bovey's Code Cleaner app, but don't have it at work. In the offchance that someone already has it installed, can you run it on the attached file? No problem if noone has it installed...I can simply do it tonight on my home PC. But I'd like to start...

It's worth noting that the first part of the above formula is just to handle the special case Amaia mentions in regards to the very first IF in there. So the base formula is actually just this: =IF(CEILING(H40,4)-CEILING(H41,4)<3,H40-H41,CEILING(H41,4)-H41)

Thanks Dave...it took quite a while for me to work out, but this is such a great example of the dangers of IF (and the benefits of using something else) that I just couldn't help but spend the time.

Here's the corrected formula: =IF(H40<=4,H40-H42,IF(CEILING(H40,4)-CEILING(H41,4)<3,H40-H41,CEILING(H41,4)-H41)) All you need to do in addition to this is use Data Validation as outlined above to ensure whatever the user enters in cell H41 doesn't exceed cell H40's value, and also that both...

Whoops, that above formula doesn't quite do it, but it is close. You can use one simple formula instead of those 64+ nested IFs. I'm pretty sure I'll have the correct answer shortly.

Okay, looking at the logic in your formula, I suspect that all you actually need is one very simple formula: =CEILING(H21,4)-H21 This would need you to put a data validation condition on H21 with the following condition: =H21<=H20 ...so that the user could never enter something in H21 that...

Amaia: the IF formula is incredibly inefficient in cases like this, even if you had below 64 levels of nesting so that you could actually enter it. What we need you to do is to describe the logic of what you are trying to achieve in words, and we can give you a formula that will be hundreds of...

No idea...it works just fine for me when I try it. So possibly it is to do with regional settings or something. Here's a screenshot showing that the DV dialog box quite happily accepts the name, when I have G8 selected: ... and then another image showing it works just fine when I use it...

Identifying duplicates between columns is very hard with formulas, but I wrote a blogpost a while back with a macro that makes this really easy. See http://dailydoseofexcel.com/archives/2014/01/11/identifying-duplicates-between-multiple-lists/

Tom: Can you post your code that you are using? And can you give us an idea about what kind of data is in the table? A sample spreadsheet would help.

Hui - can you post that workbook here? The above link doesn't seem to be working.

@alphamax Wow! I'll yet to digest this, but I just wanted to say...Wow!

@David Evans Weird: Whenever I open your file and try to switch tabs, Excel crashes. I'd be interested to hear if anyone else has an issue with this. I'm using Excel 365 running Excel 2016 version