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

[sans]

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

[jindon]

Try the attached

Matching Values.xlsm

[sans]

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

[jindon]

You mean like this?

Matching Values.xlsm

[sans]

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

[sans]

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

[jindon]

Can you just upload the file with your CORRECT result?

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

[sans]

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

[sans]

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

[mirassol]

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

[jindon]

sans,

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

[sans]

Hi jindon,

I apologise, there was a slight mistake in the results with the range that had all of the values "12".

Please have another look and let me know if I am not being clear enough.

Thank you,Sans

[sans]

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

[jindon]

Yeah, I did already.

So it needs to compare from the bottom to the top all the way and displaying first number of match count under corresponding header on originated row.

Is this correct?

[jindon]

Try this one

Please Login or Register  to view this content.

Matching Values 5WithCode.xlsm

[sans]

The results of your macro are absolutely correct. Thank you very very much jindon! Amazing!!!

Would it be possible for the macro to select and output the results in a different way? I have multiple sets of data so I will have to create many many macros with data starting in b3 and output results in M, data starting in g3 and output results at P etc. If its possible I could select the range and then the macro can output the results after a specified number of empty columns. Or I can select one cell and the macro processes the whole data block and outputs the results after an empty column accordingly. Or any other way that is a little more flexible.

I hope I am making sense.

Thank you very much for your help,
Sans

[jindon]

try

Please Login or Register  to view this content.

[sans]

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

[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

[sans]

jindon, I am attaching an example where the results for some reason are incorrect. Please let me know if I'm doing something wrong. Thank you, Sans

[jindon]

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

[sans]

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

[jindon]

OOps

Can you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[sans]

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

Thank you, Sans

[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

[jindon]

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.

[sans]

Oops...my bad.

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

[sans]

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

[jindon]

Not tested.

Please Login or Register  to view this content.

[sans]

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

[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

[jindon]

try this

Please Login or Register  to view this content.

[sans]

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

[jindon]

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

Looping within the memory took an hour is ridiculous.

[sans]

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

[sans]

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