Running count of cell changes + running count of maches

Hi Jim

Welcome to the forum.

The formula in A1 is originally referenced =AA. Then it is no longer this reference as the value may have changed, but you want it to stay the same. I believe that you will not achieve this without VBA.

If you wish to continue on this basis, it will help me understand things if you post a sample of what you want, as I am having difficulty in envisaging what you need.

I am also confused by "1st sort" and "next sort" and I do not understand your "AA" and "5DAA" reference; is the "another cell" in another sheet or another workbook?

As you can see I do not understand much, but as no one else has picked up on this thread, I am willing to give it a go.

Regards
Alastair

(Duplicate post removed)

Alastair,

Any help is appreciated. I am not very good at VBA and have had no luck with worksheet formulas. The worksheet is attached, I think. Just in case it's duplicated below.

Jim

A B C D E
Random Sample
Sequence #
0.830 20B Initial Sample ID 20B
0.499 11C Subsequent Sort 20B
0.724 16C Total Count 200
0.850 34D Match Count 10
0.250 6C % Before Threshold 5.00%
0.474 22B
0.520 10A
0.212 15B
0.514 17A
0.947 26D

This is the spreadsheet in abbreviated form

Column B contains sample ID's for a large set of samples (over 1,500 collected weekly). The Sample ID's consists of a number from 1 to 99 and a one to three alpha character lot number (6C = sample 6 from lot C)

When the sample ID's in column B are first loaded (imported from another spreadsheet) the first sample ID in B2 is loaded into E1 (E1= B2) and E6 (E2 = B2).

The cells in Column A contain the formula Rand() to load random numbers.

I have a Macro triggered by the Sort Button that sorts columns A and B ascending and then locks the original contents of E1 by copying it and then paste special back into E1 as a number

After the sort cell E2 changes to the new value in B2, while E1 still contains the original value from B2.

What I am trying to accomplish is to count the total number of times E2 changes, including the first time it is loaded from B2. So the first time it is loaded from B2, E2 =1, the second time after a sort E2 = 2 and so on.

When E1 = E2 again, D4 changes to 1 to indicate a duplicate has occurred with the first load. After each match E4 increments by +1.

When the count in E4 = 10, I will manually use another Macro triggered by the Reset 1st Sort Button to unlock the value in E1 so the next sort will load the value in B2 into E1 and it re-locks after the next sort.

Cell E5 contains the % of how many unique ID's occur in cell B2 before there are ten matches. We have a max threshold of 5% before the sample used is considered valid for testing.

The values in E3 and E4 are just plug numbers to test the formula in E5.

Hi Jim

The attached sheet works perfectly for my interpretation of your requirements. The only question is; how close is it to your interpretation?

Points to note:

The macro will now sort to the last cell used on the load (or to any rows used in column 1 - so beware)
I could not see the relevance of writing "1" in D4 - so I have not.
Match starts at 1. Should this be 0 ?
I note that although your profile says Excel 2007 you are using an Excel 97-2003 file

Let me know how close I am to your view on life.

Regards
Alastair

Alastair,

You are correct. Match should start at 0 and D1 should not be 0. It was late last evening when I wrote the description and I made these errors.

The code works perfectly except that I need the sort to only cycle once instead of looping until Match count - 10 so I can record the contents of B2 after each sort. Where do I change this?

Your brilliant assistance is very much appreciated.

Jim

Hi Jim

To stop it looping, all you have to do is remove the 2 lines
Do while 1<>2
End Loop

However, I have added the recording of B2 into the macro in v2.

I got a bit confused by "D1 should not be 0" (another late night? ), so do let me know if anything else needs amending.

Regards
Alastair

PS I shall be sending you my carpenter's bill for enlarging the doorways in my house. Thanks for the comments!

Hi, Alastair,

The new code works brilliantly and I love the addition of the three new columns that accumulate the Sort Count, Sample ID and Match Count. In fact, my colleagues are so impressed they have asked to include a new function.

We want to use Column "C" to store the initial load and then converts it into values with "Copy" .. "Paste Special" just like you did for cell E5. Then, after each new sort we want to do a compare of the values in Column C to the new values in Column B and store that count in E5 if the two Columns are an exact match. This will let us know if the entire new sort matched a prior one so we can throw the duplicate results out.

I have made a new Macro (CopyID) that is nearly identical to the one you wrote to do this for cell E1 and inserted it into the VBA code. It is triggered by the button "Reset First ID." It works well.

The problem is I can't get the count in E5 to work. I inserted two lines in Sub "RandomLoadSort". I renamed the Subs because they had names from ones I "borrowed" from a friend who wrote them to do a Solitaire game. That's why the load values look like a card deck. We will change them when it all works by loading the actual samples from another Workbook.

The two lines are

If Range (B2:B53") .. Then
Range ("B5") = Range ("B5") + 1

I also inserted two new lines in Sub "Loaded" to initially set E5 to 0.

When I run the Macro I get a compile error "Loop without Do." If I comment the two lines above out all works well except the count for cell E5 doesn't work.

Also, if I comment out the Do While loop with the two above lines active I get a compile Error "Block If without End If: error.

File is attached. I think you have to go to Advanced Mode and then click on Attachment to see it. Haven't figured out out how to do an in-line attachment yet.

Jim

P.S. I have friends in Crawley Down that I visit occasionally so when I'm in England again you have a standing invitation for a dinner and Pub crawl. Afraid I can't pay for your doors though. Too poor.

Hi Jim

"This will let us know if the entire new sort matched a prior one"

No it won't. What you have written would (if it were written correctly! ) compare a sort sequence with the original - it would not catch (for instance) if sort 3 matches Sort 2.

What are the chances of a sort matching the original? 1 in 8.06582E+67 that's 806582+ (where + represents 61 more digits). Thus in your v2 example the chances of any the sorts matching the first is 833 in 8.06582E+67. Once we get on to matching any of the sorts is well out of my experience (but is a greater chance)
If you really want to compare each sort with all previous sorts, you will need to record each of the 833 sorts and compare each result against each of them. Could take quite some time.

A thought occurs - if you are into such remote possibilities (and the UK Lottery is only 1 in 1.24139E+61) are you really safe in using Rand(). I hear it argued that is not a true random number. However, such arguments go way over my head.

I have sorted out the errors that were causing your macro to crash.

Let me know if you really want the comparison against all previous sorts.

In columns I-K do you really need all the results, or just the matches?

Regards
Alastair

(You obviously know how much my carpenter charges!)

Alastair,

As before, your solution is brilliant.

After we looked at your discussion on the mathematical possibility of an exact sort match we concluded that we were asking for way more than we needed. “1 in 8.06582E+67 that's 806582+ (where + represents 61 more digits)” is a BIGGGG number. I am removing the code that copies Col B to Col C and also the “Load Matches First Load” cells since the test is inappropriate.

The answer to your question about really needing all the sort results is no. Just recording which sorts match in Columns I to K is OK.

Is there an easy way to copy the good sorts to Sheet 2 in successive columns? It would include the sort number in R1 and then the Header and sorts results in Rows 2 to N. That way we don’t have to do a manual copy each time there’s a good sort.

We can’t thank you enough for all of your assistance. Since we can’t help with your carpenter bill (we really are rather poor) we hope a hearty “Thanks” will suffice. We are a group of retired (as in OLD) volunteers that are helping a citizen funded wildlife watch group take water samples from Florida Rivers to fight pollution in the Gulf of Mexico. No pay, but very rewarding.

Again, a very hearty thanks from the colonies.

Jim and gang

Hi Jim

Now that you have the data on Sheet 2, Sheet 1 Columns I-K are redundant. However, I have left them in so that you can see that the macro is doing something.

If there are any other tweaks required, just let me know.

Don't worry about the money (the exchange rate is bad anyway). Your thanks is sufficient.

Ah yes - I almost forgot. What the heck are you planning to use this for?

Regards
Alastair

Hi Jim

Glad it works for you. Perhaps you will mark it as "Solved" (To do so, go to your first post, where you able able to amend the title).

Feel free to PM me should any problem / enhancement opportunity arise

Regards
Alastair