Hi All,
I have the following formula that returns a specific value
At first it worked great, but unfortunately the data increased over time, now being at 50k rows and counting. Running the formula takes forever to calculate and sometimes it crashes.
Please Login or Register to view this content.
I think that the best way to handle this would be a macro, but I have no ideea how to get started and create one.
Looking for help.
Thnaks in advance,
Paul
Your sample has expected output, but it is not clear what info is being looked up. I'd suggest you upload sample that has not just the result, but all necessary data to perform calculation. Clearly indicating what column should hold the result etc.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Why don't you concatenate H2 and V2 in a helper column, copied down, then you wouldn't need to do it within the formula?
Hope this helps.
Pete
Hi CK76. The logic ibehind the formula is: if the title found in column V is duplicated, then it looks up the Training type found in Column V. If the same title exists for both Session and Event, then it should return only the ID(column W) that belongs to the Event disregarding the Session's ID. The results should be returned in column BC. I added the Expected results in the next column for a better understanding. I hope it is clear.
Hi Pete. They pretty much asked to keep it in one column. By using a helper, I would still need to return the Event ID to the Session and using a formula. This is why I am looking for a VBA solution.Why don't you concatenate H2 and V2 in a helper column
There's a performance penalty for doing things in VBA. If performance is important, I would suggest you do all you can to do it without VBA. A few things I notice:
1) Your function is concatenating column H and column V inside of the function. This means that every copy of the function must repeat this concatenation 50k concatenations times however many copies of this function you need is a significant duplication of effort. I think the first thing I would do would be to add a helper column that performs the concatenation once and save all of that duplicated effort. I know that we despise helper columns (well some of you do, I know some of the more experienced users on here are rather fond of helper columns), but I have seen examples on this forum of a spreadsheet going from "so slow as to be nearly unusable" to computing nearly instantaneously by simply adding a few helper columns to eliminate duplicated effort.
2) Your MATCH() function is using a slow, inefficient, linear lookup (3rd argument=0). Nothing slows a spreadsheet down like an inefficient linear lookup. After creating the helper column in 1, sort the list using that helper column.
I would expect that those two steps should improve the performance of your spreadsheet.
Originally Posted by shg
I figured out that if I use a helper would save me, but I cannot use one due to different work related reasonsthink the first thing I would do would be to add a helper column that performs the concatenation once and save all of that duplicated effort
Ah got you. Try this code then.
Please Login or Register to view this content.
CK76, you sir are amazing. I've searched and tried different ways to do it, but I failed every time. This is a evidence of the necesity to learn new thing. Thank you a lot!
You are welcome and thanks for the rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks