Lapping numbers using arrays and dictionary

[YasserKhalil]

Hello everyone
I have created the following code

Please Login or Register  to view this content.

The worksheet has columns from A to J with numbers .. and there are CODE numbers in M1 to V1
The idea of the code is that to loop the data in columns A to J and if a CODE number found twice in two adjacent rows then: >>
to store the two rows of spot and a row before and a row after
Example:
the CODE number 51 is in two rows 3 and 4 (these rows we can call it spot rows) , so we deal with the row before this spot which is row 2 and the row after this spot which is row 5
So we deal in this case with rows from 2 to 5 >> then store in a dictionary any number except the numbers within the CODE numbers

The code is working fine on the Sample attached as this is Sample but as for the original data which is about 3000 rows, it takes about a minute
Is there a way to improve it ..? or what is in the code that makes the code runs slowly?

[YasserKhalil]

Any help in this topic?

[Akuini]

I don't quite understand what you want.

Example:
the CODE number 51 is in two rows 3 and 4 (these rows we can call it spot rows) , so we deal with the row before this spot which is row 2 and the row after this spot which is row 5
So we deal in this case with rows from 2 to 5 >> then store in a dictionary any number except the numbers within the CODE numbers

Using this example, what is the result?

[YasserKhalil]

The result is the numbers in the spot area + row before + row after >> stored in a dictionary to be counted and finally sorted from largest to smallest.
As for the result, run the macro attached and you will get the result. The maco is working well but as for this small sample. The problem is that it is very slow as for the original data.

[Akuini]

The result is the numbers in the spot area + row before + row after

Using this example, CODE number 51, I need you to show me the result (i.e the numbers) so I can understand what you want.
I tried your file, I clear all CODE number and then I put 51 in M1, run your code and the result is 39 & 2, I don't understand why that is the result.

[Andy Pope]

See if this works for your real data.

Please Login or Register  to view this content.

It assume the real data is just more of the same. That all the values are numerical.
I testing on 20k dataset and the speed reduction is big.

[YasserKhalil]

Thats amazing Andy
The code takes about 4 seconds and very fast. Thank you very much for your great help.
Best and Kind Regards

[sintek]

Hi Yasser...

Wanna try a unorthodox method and give feedback...
I removed your merged cells as I made use of a helper column...

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot Sintek. This is very fast but the results not as the expected
Try Andy's code or my code to see the results

[jindon]

Please Login or Register  to view this content.

[sintek]

My bad...Misunderstood...I assumed there would only be one occurrence of a duplicate number is the data set...

I am assuming that where there are triplicates, both ranges are made use of...

Range.png

Results after all codes...

Results.png

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot for all of you.
Really amazing and very fast solutions
Best and Kind Regards for all of you.