Search results

I have updated the spreadsheet: "= Index" changed to "+Index" since this is still faster in some versions Lookup1 modified with a RtnCol argument, so you can use it to return any column, and an optional Drange argument, with "results2" as the default range. Just a reminder that the times...

Lori, Doing some timing (with Excel 365 and Windows 10): Starting the evaluate formula with a "=" or "+" doesn't seem to make any difference. .Parent.Evaluate seems to work just as well as Worksheets("Sheet1").Evaluate, and avoids the hard-coded sheet name. But yes, the code as written didn't...

File with Evaluate UDF and sample results attached.

Here is code for a UDF using Evaluate: Function LookupEval(pp As Long, ss As String, dd As String, mm As String, RtnCol As Long, Optional DRange As Variant) As Variant If IsMissing(DRange) Then Set DRange = Range("$D$4:$I$7683") ' Or use named range as default With DRange.Columns LookupEval...

Marc - thanks again for your code. I've posted some benchmark tests, comparing subs and functions using Evaluate as an array function, and looping through variant arrays, here: https://newtonexcelbach.com/2018/08/24/using-vba-evaluate-as-an-array-function/

Hi Marc, thanks for the reply, and the macro. I actually use the Evaluate function a lot, and your other example is quite straightforward, but there are a couple of things that were not obvious in this code: - I didn't know that evaluate works as an array function, so you can use it to add or...

Works for me too. I would have read the two columns as a variant array, looped through the second column to copy negative values to column 1, and delete contents, then written it back to the worksheet. The method in the macro is very neat, but I never would have come up with it. Is there a...

I have just posted a blog article with a summary of the different approaches here, and link to updated spreadsheet with Hui's VBA (slightly modified), and Lori's on-sheet formula: VBA vs Power Query vs Python Feel free to comment on the blog with any questions or comments on the Python or...

The download file should just work, once Python and xlwings are installed, but I am happy to answer questions. Why wouldn't here be an appropriate place to discuss it? It's a simple to implement way of extending Excel, using free software, and by the look of it very much easier to implement...

Comparing with Peter Bartholomew's last results, to avoid getting pairs repeated in reverse, the input needs to be sorted in ascending order, so I have added a sort to the Python code. I have also sorted the results, so the results list is now in the same order as found by Peter. Revised files...

Further to Lori's post No: 27, here is complete Python code that can be used direct from Excel as a UDF, via xlwings: import xlwings as xlw import itertools @xlw.func @xlw.arg('num',numbers = int) def listcombs(vals, num=2): return list(set(itertools.combinations(vals, num))) To use: If...

Thanks for the link, it was an excellent rant! When Lotus 123 lost the plot in the early 90's everyone could just move to Excel, but where do we go when MS loses the plot with Excel?

Rej - you aren't doing anything wrong, I had the same problem. You have to right click the file in Windows Explorer, select properties, then click the "unblock" button. It should work then. See here: http://www.microsoft.com/en-us/download/details.aspx?id=40326 Is it possible to link this...