Pull ranges from Table A and give max match count compared to another range

Test this (I didn't check the result)

Please Login or Register  to view this content.

jindon, thank you very very much for replying.

I tested the macro, but the results are a little confusing as for some ranges the result is correct but for others incorrect.

I am attaching another example. I broke down the ranges of all 15 of the row search values and the ranges that will pull. The result should be 2 in the range in Data C (highlighted orange).

In the second sheet, I tried to make more clear the matching idea with a very small example.

Also, I tried looking into the macro to find if there's anywhere I can input how many row search values will be used for the result for a range. What would I change in the macro if I wanted to calculate the 10 row search values, 20 etc?

Again, thanks a million for your help!

My understanding

1) Look at the DataC from the bottom as a list of Max.
2) Look at the DataB(Row search value) from the bottom as the indexes of the rows relative to the DataA from the bottom.
3) If Maxvalue found in each indexed row in referenced row in DataA, count as 1.

The code should do like this.

Hi jindon,

Thank you very much for your reply.

I most likely may have not explained it correctly.

- It is correct that one row search value range, pulls 7 ranges from Data A

However, I would like to use the 15 latest row search values (for receiving the result for any one range in DATA C). So this will pull a set of 7 ranges, 15 times. The 15 latest row search values are relative to the range in Data C.

Then the max match is found in each of the 15 sets of 7 ranges. Then the highest match between the 15 sets of 7 ranges, is the result.


- Also when any one from the 15 latest Row Search Value range pulls one set of 7 ranges from Data A, the counting of rows from the bottom up in Data A always starts at the same row (always relative to the Data C range).

I hope the above will make the attachment make better sense.

Again thank you for your help,
Sans

I was wondering if it is possible to only pull ranges and display them in sets of 7. I am attaching sheet example. The Rows counting going upwards always starts relative to the Row search Value range.

Thank you,
Sans

I still don't really get the logic.

I am attaching another example that is hopefully a little clearer.

Thank you

option

Please Login or Register  to view this content.

Thank you very very much for the macro nilem, it works great! One step closer to what I am trying to achieve.

I have extended your attachment by including the final step to what I am trying to achieve as the end result, in case this is possible to do with a macro, and of course if it is clear in the attachment. If you unserstand what I would like to do further and can help it would be simply amazing.

If you are able to help, displaying the 15 sets of ranges is not necessary as I am only interested in the end result. If not, no worries I will be using your current macro for displaying the 15 sets and processing manually.

Thank you again for your reply. Sans

Hi Sans. Unfortunately, I do not understand your problem. Perhaps it is too difficult for me.
I added conditional formatting to a range AK3:​​AQ400. Maybe this will help you.

try this (see attached file)

nilem this is macro is absolutely AMAZING! Exactly what I wanted! I didn't expect this morning to wake up to such a nice surprise A million thank you!!! Amazing!

One last question, is it possible to also calculate any other ranges that are present in DATA C as well? The Row Search Values and the counting back in Data A will both be always relative to the Data C range that is being checked.

Thank you very much for all your help,
Sans

see attachment
I hope that's all

Hi nilem,

I just can't believe it! Thank you nilem! Its simply amazing!!!

One last question. Would it be possible to do a slight modification to the algo where it determines where the counting backwards should start in Data A? I am attaching the same sheet you attahced with a little note. The tyutyu is simply the best touch possible.

A million thanks!

see attachment

Hi nilem,

thank you very very very much for the attachment!!! I tested 3 ranges in Data C and the results were correct. I was wondering though if there is a reason why you didn't include the tyutyu module as it is incredibly useful. I tried creating it by examining and trying to imitate what you did in the previous attachment but couldn't get the correct ranges to show. Would it be possible to help me with the tyutyu as well?

Thank you very very much for your help, what you are doing with this macro is simply phenomenal!!!

Hello nilem,

I attempted to incorporate the tyutyu module into your latest attachment and have managed to get it working - in a way When running the module, it displays all 15 sets and I receive the max match in the end, but I must have done a mistake somewhere as I can't get it to output the correct ranges in each set.

For the tyutyu I did use your code from the ertert module from the latest attachment. Though I am sure I messed it up somewhere, I keep going over all the modules in your attachment in post #16 where the tyutyu functions correctly to see what you did there, but I can't figure out. Could you please have a look to see where I am going wrong?

Again, thank you very very much for all your help and your patience.

nilem I've been also meaning to ask you about the randomize function. In line,

For itr = 1 To 3000

does the number determine how accurate the results are? For example, if I input 300 and run the macro several times the results may vary and are sometimes not correct. However, if I increase the number to 10000, then the results are the same and correct each time I run the macro (since the amount of times the matching numbers are randomized increases) - at the expense of taking sightly longer to process. Is this assumption correct?

Many many thanks for your help

To use Sub tyutyu(), copy all the data so that the row verified in block C was the last line.

For itr = 1 To 3000

Yes, the more iterations, the better result.

Ok, got it!

Thank you very very much for all your help. What you've done with this macro is amazing! And the frustration you have saved me is simply unbelievable. A million thanks

Hello nilem,

I was wondering if it would be possible to help me once more with this amazing macro. I am looking to change the code slightly only in how the ranges in O:U find the values in Data. Please let me know if this is possible as I have been trying to change the code myself for days now with no success. If you could help me once more it would be a huge help. Thank you very very much for your time.