Find exact match, and the closest smallest and highest numbers.

[I need excel help]

Hello all,

I am looking to get a macro for the results I have in cols BJ:BO. I have resulted the first 5 x names by filter.

To explain I will go to the name Billy Bunter as he has all expected results for all 6 x cols BJ:BO.

Each name is in ascending order by date, the oldest date to the newest date.
Col AP is counting how many starts each name has had.
Col BG is the number of days since each names last run, so this is looking back at the date of their last run and resulting how many days.
Each name is individual, and the results only refer to that one name.
There are names in here that have had only one start, so col BG will be 0, zero. There will not be any results in cols BJ:BO as there is nothing to refer back to.
The results in col BJ:BO are always referencing back over each name past history only.

Exact finish and exact margin.
Here we see Billy has in cell BG25 the number 11, this is an exact match in cell BG20, so results come from cols AL and AO. 4 and 3.3

Lower finish and lower margin.
This is looking back for the next smallest number in col BG.
Cell BG19 says 45, so looking back there is only one run, cell BG18 says 0, zero. So, results come from cols AL and AO being 15 and 8.6

Higher finish and Higher margin.
This is doing the same thing, except its looking for the closest number that is higher than the number in col BG.
Cell BG24 is 49, so the closest highest number to 49 is 109 in cell BG22. result in cols BN and BO are 5 and 3.5

I think I have explained everything, but I have a record of leaving things out, happy to take all questions and I hope I can get a result.
I have 580,000 rows to process, and I know some macros need time to work, I'm happy to let computer do its thing.

Thanks.

[sudbhavani]

I can help you create a VBA macro for this task. It seems like you want to analyze data in columns BJ to BO for each individual name, considering conditions related to the values in column BG.

Here's a sample VBA macro that you can use as a starting point. Please note that this is a basic example, and you may need to adapt it based on your specific requirements:

Please Login or Register  to view this content.

[I need excel help]

Thanks for your result. I put the code into sheet, see example v2, version, 2.

But nothing is resulting, cols BJ:BO are blank when I run your code. Maybe I'm doing something wrong.
Appreciate time spent writing this.

[sudbhavani]

Dear I need excel help
kindly update this code Here's an updated version of the code, with placeholders for the calculations. Please replace the placeholders with your actual logic:

Please Login or Register  to view this content.

Make sure you replace the placeholder logic with your actual calculations. If you encounter any specific issues or have further questions, feel free to let me know.

[I need excel help]

I'm a bit thick and don't understand replace the placeholder logic.
I can only paste the code into a new module and that's my limit. This is what I normally do to a lack of skills in vba.

I can see massive effort in writing this, please excuse my inability to understand where I should paste above new part of code.

Can you post the whole code so I can copy and paste into a module.

Thanks.

[I need excel help]

Hello, thanks for the code you have written.
Can you or someone post the whole macro to work on the sheet. I do not understand the two posts of code, how do they join, where do they join, vba is way over my head.

I have spent hours trying to get the code to work but no joy.
Thanks.

[AliGW]

Bump for visibility.

[I need excel help]

Bumped for visibility.

Cross post. Which I appear to be failing at.
[https://www.mrexcel.com/board/thread...bers.1250627/]

Thanks, AliGW, I have re-read the rules, and I will follow these exactly.
Thanks.