Excel macro for logging changes in a dataset

El Siglo

Alright folks,

I have a a dataset that I'm working on, it's a geological core with a mineralogical code for each time I've sampled the core. So each code is a numerical value between 1 and 6. I'm using Markov Chain analysis to examine the transitions between each sample. The way it's been working so far is that I inspect the core from the bottom up and I count how many times say "1" comes before "2" and "1" comes before "3" etc... This is a one-step (i.e one cell) process. Could a macro be put together where I can say that "how many times does "1" precede "2" by two cells or "1" precede "2" by three cells etc... ? I've no experience of writing macros and have never done it before, would anyone here know of any way that I might put such a macro together for the purposes of this task?

Taltos

Hi - if you are constantly overwriting the same field I am unsure how to best approach this.
Another way is to look at your end objective and work backwards.
e.g. If you want counts of each scenario - then use a data entry sheet - and set up a summary tab to count each valid scenario.
This would mean you would be entering data on multiple cells though...

Hopefully though someone can help you - my macro knowledge is quite basic. There should be a way to do what you want - I just don't have the programming experience to help out here as most of my macros work on static data - not on data that is changing consistently within just the one field.

BostonB

Start recording a macro. Then do what you want to do manually. Then stop the macro and look at the code. Thats the first thing to look at to see what it generates.

To count transitions you'd loop through the data counting, perhaps in an array, then when you meet a count that satisfies the condition (process) write it out either to a adjoining column or to another sheet.

Where is the dataset now?