Need help gettin this macro to run faster, Please!

[estatefinds]

I ran it and i got the run time error messge again I did the Debug and it highlited the
[aK = Application.WorksheetFunction.Transpose(rK.Value)]





Run-time error'13':
type mismatch

I guess th transpose has a limit of over 65000 rows, can this be overcome? it worked without transpose on the 3 matching how can we get it to work with the 4 matches and 5 matches?
Ichecked all my data and nothng is wrong with it but everytime the transpose ised i get this error, im stuck and baffled.
can this be fixed please?

[Paul_Hossler]

Post #30 has version 3

I think I messed up and attached version 2 to the last post -- ver 2 has TRANSPOSE in it, ver 3 does not

Sorry for the confusion

[estatefinds]

No problem! Thank you! I'm running it now, I'll let you know how it did! Thank you very much for your hard work on this! I very much appreciate it!!!

[estatefinds]

Ok I just finished running it it looks like its doing the 3 matching and 4 matching,Which is great!, correction I did a search of 4 matching numbers and they werent highlighted mor were the % matching.




it needs to also besides the 3 matches do the 4 matches and 5 matches


to highlight and 5 matching.
can a code be added to the existing code to finish highlighting the matching 4 and matching 5?
Please?
Thank you sincerely

p.s.
is there a way when the macro for the 3 matches are done it will activate the next macro to run for the 4 matches of the remaining unhighlighted combinations, then when the four matching macro is done it will activate the macro for the remaning of the unhighlighted for the 5 matches?

[estatefinds]

Hello, how are ya? And luck with #44?

[Paul_Hossler]

Sorry - been busy with grandkids and the cold they gave me

I took out some checks that were intended to improved the speed, but seemed to cause problems

Try this with real data

If there's an issue, let me know the number(s)

[p45cal]

I've made some tweaks to Paul's code in the attachment to msg#46, none of which will contribute to speeding things up(!) but might be of interest, perhaps even useful.
What it does is to add a couple more columns of information; you'll see them as plus signs in columns N and D. The idea being for you to select one of those cells and click Trace Precedents in the Formulas tab of the ribbon and it will show you which cells are matching. Double-clicking a blue line will toggle the selection to each end of the line. There are some comments in the code.
See attachment.

[estatefinds]

Ok I ran it and ran into error due to memory so I'm freeing space and will run again and let you know how it worked! Thank you!��

[estatefinds]

No problem, I know how that is! Thank you! I'm running again! I'll let you know! Thank you!��

[p45cal]

What, in words, are you actually wanting to do?

[estatefinds]

Ok so I ran macro and it colored all the 3 matching data of col G of column K then the four matching but it stopped and didn't complete all the 5 matching. I got the run time error '14': out of string space.

[p45cal]

Presumably you ran the code in my attachment to message#47 and it worked?
All the matchings (3,4 & 5) are done in one pass.
If you're trying it on repeating data (there will be if your suggestion of copying down the values is followed in msg#28) there will be thousands of matches; a formula to include all the references will be long - very long. Typically,what's the largest number of matches one cell might you expect? I can limit the length of the formulae to say 3200 characters which will mean not all the matches will be pointed to when you Trace Precedents on the plus sign (all matches will still be coloured), but there'll be room for several hundred. It may be that you only need one column of references to matches rather than 2 (or none)?
I may be wasting my time, because, for example, you may be disinterested in the matches, and only interested in the non-matches, I don't know.
So again, what, in words, are you actually wanting to do?

[estatefinds]

So the data in the K column is all possible combinations of numbers this is a set list and won't change,350,000 of them the data in column G are data of combinations that will match one of the ones in column K. Currently there are almost 8000 of these where one of combination will match the one in column K, eventually all will match as the list in column G gets bigger. What I wanted was to look at first combination in column G and looks for its match in column K and highlight both cause all Five numbers match. We could stick with just 5 matching for now and use the code without the 3 and 4 matching as this doesn't work well as I thought which Paul mentioned earlier. If we can just remove the part of code to that searches for 3 matching and 4 matching, and stick with just 5 matching but keep everything else including the trace precedents, etc.
I appreciate the work everybody has done on this!!!
Thank you sincerely,

Dennis

[p45cal]

So the data in the K column is all possible combinations of numbers this is a set list and won't change,350,000 of them

This could make things a lot easier; all combinations of 5 numbers picked from 1 to 36 (=376992 combinations)? Can they be in order, top to bottom of the list?

[estatefinds]

1to 35. The numbers I have in the column K I allready have in order from top to bottom, the numbers in column G are random as they become avail. The combinations themselves are in order from smallest to greatest left to right in the cell.

[p45cal]

It's taking about 7 seconds per entry in column G on my old desktop, but this is using the brute force method of going through all the 324k cells in column K. If you have, say 70k cells in column G that would take some 5 or 6 days.
I do have some ideas about shortening the process; if you have a sequence like 10-12-14-16-18 in a cell in column G, there's no need to look for matches in any column K cells in the sheet above those beginning 10-. If you're looking for 3 number matches or more, there's no need to look below cells in column K starting with the third largest value which is 14-.
In your msg#15 'additional info' you talk of skipping over already highlighted cells in column K; I'm thinking it might be possible to code for this too.

Is there any point in highlighting cells in column G? There always going to match something (well, 4501 matches to be exact, in the case of 3-,4- and 5-number matches).
There could be 2 reasons I guess that you might want to highlight cells in column G:
1. To show that it has been processed; this could be used so that when you add data to column G, you only process unhighlighted cells when the code runs. This would considerably shorten subsequent runs.
2. I haven't worked this out yet: as more and more values get added to column G, and more and more cells in column K are highlighted, there might come a time when a new value in column G causes no more cells in column K to be highlighted, in which case, would you want to show that by not highlighting the cell in column G (or highlighting it in a different way)?
3. I might ditch the Trace Precedents thing as it's probably not sensble to have 4501 cell references in a formula in one cell, as well as it being nigh on impossible to discern the 4501 resulting blue lines! That should reduce time too.

Could you comment on/answer points 1 and 2 above?

[estatefinds]

ok for number 1. yes on this number 1.to show that is has been processed.

The number 2. actually the numbers in column G that get added will be found in Column K when we use the the search for the 5 matches.
the code for the 3 and 4 matches will be removed completely, but be used for the 5 matches only.

so all the combinations in column K are unique they never repeat. so when the combinations in Column G get added it will match a combination in column K. we can ditch the Trace Precedents. so if we can will go ahead with code for the 5 matches only.
I am still working on the idea of the 3 and 4 in which i will post a new post once i figure exactly how I need it to work.
Thank you!!!

[Paul_Hossler]

I've made some tweaks to Paul's code in the attachment to msg#46, none of which will contribute to speeding things up(!) but might be of interest, perhaps even useful.
What it does is to add a couple more columns of information; you'll see them as plus signs in columns N and D. The idea being for you to select one of those cells and click Trace Precedents in the Formulas tab of the ribbon and it will show you which cells are matching. Double-clicking a blue line will toggle the selection to each end of the line. There are some comments in the code.
See attachment.

This wasn't a 'tweak' -- it was a bug fix -- good catch for you, bad thinking for me

    '    If aG5(G)(4) < aK5(K)(0) Then GoTo NextK    '   largest G < smallest K 'these are text comparisons!
    '    If aG5(G)(0) > aK5(K)(4) Then GoTo NextK    '   smallest G > largest K 'these are text comparisons!

[Paul_Hossler]

From post #15

I have Data in Column G in the form of combinations.
I need the Duplicate Macro to be restuctured to not only highlight the exact matches in column K but also to highlight the the data with at least 3 matching numbers within the combination, and four matching as well.
so for example G1 matches the data in K6 cause all 5 numbers match both would be highlighted.
in G2 with macro restuctured it would highlight it as it would match four of the numbers in K2 the 4-5-8-10

It seems to me that if you have 3 matches then you could highlight the paired cells and not bother to check for 4 or 5 matches

There doesn't seem to be any requirement to differentiate between the number of matches

Am I missing something?

[estatefinds]

just need to focus on the 5 mathes for now

I have to come up with a differenet method of the 3 's and 4's because it doesnt descriminate like i thought it might.
but if we can do for the 5 matches this would be great! thank you!