Detect String Frequency on dynamic entries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi -

I'm performing a shopping cart analysis (if customers purchase product
A what else do they also purchase?) and have run into a bit of a
snag ...

I need a procedure that will return the most frequently occurring text
string found in a contiguous block of 4784 cells (A1:AT104). There
are over 10,000 possible values that could be in these cells, so
counting the occurrence of ALL of them wouldn't be that efficient ...

Additionally, a significant number of the cells in the target block
could contain the value 'end' or '#N/A' -- these values need to be
ignored.

Ideally, the solution would be scalable (allow users to select larger
or smaller range) and also allow for choosing the Most frequent, 2nd
most frequent, 3rd most frequent, etc values. So, something like:
=UDFname(range,3) would return the 3rd most frequently occurring text
string in the specified range.

Any ideas of how to do such a thing? The only solutions I could find
required either knowing what string value to look for or returning a
list of values (sorted one way or another).

Thanks for looking!

//ray
.

Relevant Pages

  • Re: Detect String Frequency on dynamic entries
    ... string found in a contiguous block of 4784 cells. ... most frequent, 3rd most frequent, etc values. ... Dim cStrings As Collection ... ReDim tempCount ...
    (microsoft.public.excel.programming)
  • Re: Cells compared to Flow-Based Programming
    ... There would be a base rate (perhaps no more frequent than the highest frequency required by any Cell) and then individual Cells or groups of cells or something could decide which subset of the base rate samples to process: Ah, not much is going on, let's wait 10ms before recomputing. ... Then of course we'd have to intertwine the stepping function with cells, so that cells knows when it was called the last time. ...
    (comp.lang.lisp)
  • --really newbie-- most frequent character
    ... I've browsed the API but I am just lost. ... What I intend to do is decrypt a string without a code given? ... there's a most frequent letter in english which is 'e' so i want to ... pick the most frequent letter from the encrypted string and assign it ...
    (comp.lang.java.help)
  • Re: Cells compared to Flow-Based Programming
    ... There would be a base rate (perhaps no more frequent than the highest frequency required by any Cell) and then individual Cells or groups of cells or something could decide which subset of the base rate samples to process: Ah, not much is going on, let's wait 10ms before recomputing. ... ECLM rant: http://video.google.com/videoplay?docid=-1331906677993764413&hl=en ...
    (comp.lang.lisp)
  • Re: Count letter string, e.g. h/EL/p
    ... and the crowning place of kings ... With the string that you are looking for in D1 try: ... of letters "ee". ... I also want to count how frequent a string such as "A, any letter, E" ...
    (microsoft.public.excel.worksheet.functions)