You are correct - PowerQuery handles it fast. I did run into a few errors caused by a small number of rows with blank fields in the 'Status' column. I'll need to work with my user to understand what these are. For now, I removed them and ran a test and the PowerQuery works. Thanks for...
It does work better. The original version wouldn't even finish. This last version now does so in a few minutes. Thanks for the solution and the innovative approach.
I'm attaching a "full size" file of dummy data. It took me a while to randomize the data while keeping it coherent. I...
I ran two tests so far and with 200 lines it was 25 seconds. 400 lines was 3:02.
I will run some additional tests and see how it does. Along your same lines of thinking I will see if I can reduce lines from the overall population, maybe excluding some that have a current status that has not...
GraH-Guido, Thanks for your solution. I don't have much in the way of PowerPivot/Query knowledge, so I'm very glad you suggested it. It forces me to figure out what exactly you did to get to the end outcome.
To answer your question, I'm using Excel 2013 but I'm created a process for a...
Vletm, The code looks great, and I think I follow the logic of it. However, if I try to run the code on more than a small amount of data my excel freezes. About 200 lines of data appears to be the limit, although I have about 35,000 lines to go through. Any settings I could change on my local...
Thanks Luke. I had initially approached this as a Countifs problem. Unfortunately, the formula will return a True value for authorizations that dont meet the criteria. For example, this reports a True value for users that Created, Updated, and Signed their own authorizations even though they...
Good afternoon,
I'm at a loss for thinking of a formula or an easy way to flag suspicious travel authorizations. What I want to flag are the travel authorizations that have a Status of Created, Authorization Signed, and Reviewed, all within the same Authorization Number and by the same Acting...
I think I've got it.
=IF(SUMPRODUCT(($C$4:$C$7<=B12)*((ISBLANK($D$4:$D$7)+($D$4:$D$7>=B12)))*($B$4:$B$7>=A12)*($A$4:$A$7<=A12)),"Yes","No")
Thanks for the help.
Thank you Luke, I appreciate the fast response. The formula doesn't check the ending range of the funds center though. I tried using
=IF(SUMPRODUCT(($C$4:$C$7<=B12)*($D$4:$D$7>=B12)*($B$4:$B$7>=A12)*($A$4:$A$7<=A12)),"Yes","No")
but it does not work if the ending fund center is blank. Any...
Hello all,
I have a challenge I've been thinking about lately - how to determine whether a value falls in between a range. The challenge is that the value and range can be alphanumeric. Example: Does A11 fall inbetween A10 and AZZ? (the answer is yes)
The solution can be formula or user...