Hello all,
I am a biologist, I use Excel often but am new to VBA. Essentially, I
am looking to create a macro which gleans certain data from one
worksheet and refines and deposits it into an adjacent worksheet. If
the answer to my problem is already out there, sorry, I searched and
did not find it--probably I don't know enough on the topic to search
efficiently.
I have data classifying behavior of marked organisms. For a given date,
we may have recorded behavioral data on a unique animal more than once.
On some days I have 6 rows of data for a particular animal (identified
with an alphanumeric code, such as T332), and on some days I have only
one observation of that individual. Some behaviors are more
"important" than others, so if animal #T332 was recorded 6 times on
a given day I only need to keep the one row with the behavior deemed
most useful to our research question. Ultimately, I want one row of
data per animal per day. There are many hundreds of uniquely identified
organisms.
I'm trying to create a macro that will:
1. Search the worksheet, finding occurrences (rows) where the same
animal was observed more than once on the same date. E.g. if the fields
under columns: 'Name', 'Month', 'Day' and 'Year' are =
between 2 or more rows, I want to go to step 2.
2. Consider the group of rows which met the above criteria, the row
having the most "important" behavior (column 'rbi') observed on
that day is retained. Other rows are discarded. rbi order of importance
(high priority to low) is: S, NP, WP, WF, OM, A, U.
3. The "winning" row is sent to an adjacent results worksheet.
When there aren't multiple rows per animal per date, the program
sends the lone entry to the results worksheet, and the program goes on
to search the entire worksheet.
I'm turning to this forum because I haven't found much discussion
or information (that I understand at least) online or in the books
which I could use to do this. Any ideas would be great, if possible,
please include as many translations from code language to English as
you can. Even just help on coding out the first step would be
incredibly helpful-i.e. how to find and select rows that have the
same values under certain columns.
Any Ideas? Thanks for taking the time to think about this!
Bookmarks