+ Reply to Thread
Results 1 to 14 of 14

Running count of cell changes + running count of maches

  1. #1
    Registered User
    Join Date
    08-12-2014
    Location
    Florida
    MS-Off Ver
    2007
    Posts
    7

    Running count of cell changes + running count of maches

    I have a sheet that I need to keep a running count of how many times a cell (C1) is loaded (C4 below) and also how many times that cell matches the first time it was loaded (C5).

    The sheet looks like:

    Initial Load

    A B C
    1 1st Sort = AA From another cell
    2 Next Sort = AA From same Cell C1 loaded from
    3 Match Yes
    4 Count 1
    5 Match Times 0 Do not increment Initial load

    2nd Load

    A B C
    1 1st Sort = AA Must stay the same value as the initial load
    2 Next Sort = 5DAA
    3 Match No
    4 Count 2
    5 Match Times 1

    Nth Load

    1 1st Sort = AA Must stay the same value as the initial load
    2 Next Sort = AA
    3 Match Yes
    4 Count N
    5 Match Times 2

    I am NOT proficient with VBA so if there is an on-sheet formula(s) that will work i greatly appreciated.

    Jim
    Last edited by Jimmy P; 08-12-2014 at 03:07 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: 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

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Running count of cell changes + running count of maches

    (Duplicate post removed)

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,177

    Re: Running count of cell changes + running count of maches

    If you are over-writing all of the cells with each new "reload", then there is no regular formula that will count repeats, or anything else for that matter. You will need VBA for this
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    08-12-2014
    Location
    Florida
    MS-Off Ver
    2007
    Posts
    7

    Re: Running count of cell changes + running count of maches

    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.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Running count of cell changes + running count of maches

    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
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-12-2014
    Location
    Florida
    MS-Off Ver
    2007
    Posts
    7

    Re: Running count of cell changes + running count of maches

    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

  8. #8
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Running count of cell changes + running count of maches

    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!
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-12-2014
    Location
    Florida
    MS-Off Ver
    2007
    Posts
    7

    Re: Running count of cell changes + running count of maches

    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.
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Running count of cell changes + running count of maches

    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!)
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    08-12-2014
    Location
    Florida
    MS-Off Ver
    2007
    Posts
    7

    Re: Running count of cell changes + running count of maches

    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

  12. #12
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Running count of cell changes + running count of maches

    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
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    08-12-2014
    Location
    Florida
    MS-Off Ver
    2007
    Posts
    7

    Re: Running count of cell changes + running count of maches

    Alastair,

    Sorry about the delay in responding to you. Family issues.

    With a few mods, your code works well for us. We are a group of retired (as in OLD) volunteers that are helping a citizen funded wetlands watch group take water samples from Florida Rivers to help control pollutants from entering into the Gulf of Mexico. No pay, but very rewarding. We have 32 folks that collect samples monthly from 17 rivers. The reason for all the sorts is that the each sort is assigned to a different volunteer so that there are no repeats in where samples are taken (location, position (bank, mid-stream, etc.) water depth, time of day, weather condition, etc.). Each person takes their samples according to the sorted samples. Trust me, there is a reason for this, but I'm not smart enough to explain it. Orders from above. We used to do this manually, but it took forever. I got elected to tackle it because I could spell Excel with one 'L'. Probably a bad choice. LOL

    Again, many thanks for all you assistance.

    Cheers.

    Jim and gang
    Last edited by Jimmy P; 08-25-2014 at 09:36 PM.

  14. #14
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Running count of cell changes + running count of maches

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Running Count of Cell Changes
    By jlpurvis2009 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 11-14-2012, 01:25 AM
  2. Help with running 7 day count
    By gregbain2 in forum Excel General
    Replies: 6
    Last Post: 10-25-2010, 07:48 PM
  3. [SOLVED] Running Count
    By David Billigmeier in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 09-06-2005, 08:05 PM
  4. [SOLVED] Running Count
    By David Billigmeier in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 05:05 PM
  5. Running Count
    By briank in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1