Counting occurences of a value tied to unique ID's

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

I've hit a wall on summarizing some GIS data. I've tried searching
google and this group, and while I've found many posts detailing how to
count uniquely within a column, the situation I'm facing is a little
more difficult I believe, or perhaps I don't know the correct
terminology to describe it / search for. I've simplified my actual
data/situation for examples sake:

Suppose column A is "ID_Number" and column B is "Comment" (for example
sake "YES", "NO", or "NA"). I need to sum the number of times a
specific comment, say "YES", appears in column B, but only once per
unique ID_Number. Say ID_Number 1452 has 4 comments: "YES". "NO",
"YES", "NA". It should contribute only one "YES" to my count.

I've reached the limits of my knowledge about how to approach this. So
far I can make 1 more columns, column C. Column C contains a "1" if
this is the first time the ID number has shown up (obviously I must
sort my data by column A, ascending), and a 0 if not. I could run a
pivot table at this point using the "Comment" column and column C, and
get a sum of "YES", however this doesn't quite cover the situation.
There could be an ID_Number that appears, for the first time, with a
comment of "NO". Any subsequent "YES" in the comment column for this
ID, will not be recorded.

Simply because of time constraints, I don't want to have to write a
macro (I've had to learn a bit of VB for Office over the last few days,
mostly macro'ing Pivot Table creation and manipulation). Any idea if
this can be done without resorting to a (complex?) macro.

The general flow of the macro would go (I believe)

1. For each cell in comment column
2. If the comment = "YES"
3. Check if the associated ID_Num occurs in some list
4. If not add it to list
5. Finally sum the list

Any thoughts?

.

Relevant Pages

  • Re: Counting occurences of a value tied to unique IDs
    ... > I've hit a wall on summarizing some GIS data. ... I've tried searching ... > this is the first time the ID number has shown up (obviously I must ... > this can be done without resorting to a macro. ...
    (microsoft.public.excel)
  • RE: [Full-Disclosure] AV Naming Convention
    ... IT would be an automated naming based on first time of discovery and reporting, there could be aliases added for the bugger. ... This could be for searching for Mydoom.b Mydoom.c etc. variant rather trying t search for a name like Virus20040908.19:24:31.8843 time stamped variants. ...
    (Full-Disclosure)
  • Re: Multiple sheet search
    ... I saved th macro and closed/reopened the workbook. ... Dim ans As String, rng As Range ... res = MsgBox("Continue Searching?", vbYesNo) ...
    (microsoft.public.excel.programming)
  • Re: Equivalent tools.h++
    ... usenet post, in particular when you are asking for help. ... your first time doing this, I'm letting you know in a nice way. ... Sybren, if you're interested, google reveals: ... searching for what you want. ...
    (comp.os.linux.development.apps)
  • Re: Duplexing on a macro
    ... I tried searching for a similar question before i submitted mine, ... ' Macro recorded 26/06/2007 by jodywilliams ... .PrintFieldCodes = False ... .PrintDrawingObjects = True ...
    (microsoft.public.word.docmanagement)