A
anjogasa
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?
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?