Find the Nth value and return value

[Yelir]

Hello,

I have an existing method of finding the Nth value and returning the results via a formula, however, my data set has grown considerably and the time to calculate has become inefficient. Can this be rewritten into a macro to process faster?

Please Login or Register  to view this content.

[Fluff13]

How about

Please Login or Register  to view this content.

[Yelir]

Fluff13,

For 15,000 rows it seems to take longer, also I tried to put the {} around the formula and I #CALC! error.

[Fluff13]

Do not use Ctrl Shift enter, you never need to do that with 365.
How many distinct values have you in col A?

[Yelir]

Thank for the advise Fluff13,

Today I have 15818 rows of data containing 255 unique values in column A.

[Fluff13]

255 distinct values shouldn't take that long. Try it on a new sheet without any other formulas.

[Yelir]

Fluff13,

I am getting about a minute to refresh calc, and still getting a #CALC! returning of the values. I have tried the following;
1. Created a new sheet with no other formulas
2. Altered the range from 1,500 to 15,0000 and vice versa
3. Used without {}
4. Not sure if this is an issue or not but I also tried locking the Range for each area

All 4 have not worked for me - Any other thoughts on a different approach?

[Fluff13]

Did you just put the formula in C2 only & let it spill down?

[Yelir]

I did, Not sure why this is not working when I put a larger data set in - Would it matter if the I have cells that contain all numbers as text? I figured as long as there is a matching criteria it doesn't matter which format the values are in.

[Fluff13]

As long as you get the correct results, the numbers as text shouldn't be a problem.
With you formula when it re-calculates how many threads does it show being used on the status bar?