Check for matching values and list the lapsed rows under the appropriate heading

Hello,

I am looking to compare a range with its previous ranges, find the count of matching values and give the row difference of the first occurence of the match, under the appropriate heading. Its a little difficult to explain, so I am attaching an example worksheet for your better understanding.

Thank you for your time,
Sans

Try the attached

Matching Values.xlsm

Hello jindon,

Thank you very much for your reply. I just tried the macro and the results the macro outputs are correct . However, checking a range with the previous ranges, I would like to receive only the first found occurence of a match in a cell.

Also, each range should have its own results, as each range is being checked with its previous ranges to find the appropriate matches.

I am attaching an example that I think is a little clearer.


Also the macro below (credits to user nilem), even though it has a different function (it calculates the values differently), it outputs the results in the way I describe in the attachment, and displays only the first occurence found in a cell.

Please Login or Register  to view this content.

I don't know if its possible to combine how the macro displays the results from the above macro, along with your macro for calculating the values.

Thank you for your help, Sans

You mean like this?

Matching Values.xlsm

Witht the latest macro I receive only one value as the result.

If you run the macro above, you will see how the results are displayed, the found matches for each range go under the appropriate headings.

Thank you, Sans

Even though the results are wrong, the way the above macro displays the results is correct. Thanks,Sans

Can you just upload the file with your CORRECT result?

If the result is not correct, it is only confusing.

You mean with the above macro? The above macro works as it should and displays the results correctly for its purpose.

However, I would only like to apply how it displays the results, with what I would like to achieve as I described in the attachment in post #3.

The attachments in posts #1 and #3, show the correct results.

Thank you,Sans

I've made another example sheet. Hopefully this is clearer. Thank you,Sans

How can i open an .xlsm file with my office xp ?
Does anyone know if it is possible ? Can't follow the threads properly.
Maybe this is answered somewhere and i didn't see it, sorry.
Thanks

sans,

I kind of understand last 2 lines i.e -2, -4, -6 and -6, -7, but can you explain the rest?

jindon, if you have downloaded "Copy of Matching Values 5", please re-download,"Copy of Matching Values 5-1.xlsm‎" as I've noticed another small issue. thanks, Sans

Try this one

Please Login or Register  to view this content.

Matching Values 5WithCode.xlsm

try

Please Login or Register  to view this content.

That's perfect jindon! Thank you! For some reason though if I input lots of data, I get incorrect results - I think the results are displayed upside down or something else is wrong. Let me make an example and I'll show you. Many thanks, Sans

jindon, should the macro behave ok with values separated by dash i.e. 1-5-7, or numbers bigger than two digits?
Thanks,Sans

You need to isolate dataRange from the result header with at least one blank column.

Sorry jindon, I did that but I still receive the same results. Thank you, Sans

OOps

Can you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I receive an error, "Object doesn't support this property or method"

Thank you, Sans

jindon would it be also possible to reset or delete the previous results on re-running the macro? If I run the macro for a second time the old results get mixed with the new results. Thank you,Sans

1) I haven't got such error.

2) It is fully depends on where you selected for the output cell in previous run. So seems impossible.

Oops...my bad.

It's amazing as is jindon. Again, thank you very very much for all your help!

jindon,

Would it be also possible to have an additional slightly different version of the macro? Basically the macro would function exactly as now, the only difference being that a range is checked with only its previous 10 ranges, instead of being checked with all of its previous ranges.

Thank you very much,
Sans

Not tested.

Please Login or Register  to view this content.

Excellent! Works great. Thank you jindon!!! Sans

Hi jindon,
Sorry to bother you about this, but I am experiencing a slight issue which affects both versions of the macro. I tried analysing a block of 12 values going down 4500 rows ( i.e. L7:W4507). I left the macro running for a little more than 2 hours but wasn't finished until then, so I had to stop it manually. If this is to be expected with this type of calculation, its ok. I just wanted to ask to make sure.

Thank you,
Sans

try this

Please Login or Register  to view this content.

Thank you jindon! The time now got exactly halved down to 1 hour. Much much better than 2 hours If it could be faster it would be great, if not that is great too

I haven't tested the code for myself, but I believe your excel got problem.

Looping within the memory took an hour is ridiculous.

Thanks! I try googling it to see what I can come up with. Hopefully unistalling and installing it back will solve the problem.

Hi jindon,
I tried to figure this out by testing all of your macros for a while and your last macro is the fastest one. I tried using it in a workbook that has a single sheet and one block of data and it fhinishes in around 30 seconds.

When I tried the same (last macro) in a workbook that has 12 sheets and where each sheet has many blocks of data, it takes one hour. Is it possible that the finish time of a macro is affected by the number of sheets in a workbook? Or by the amount of data in a sheet, even though the macro is processing only one block of data in a sheet?

Thank you, Sans