I am trying the count the number of unique sensors (column 1) that are present by visit duration (column 2). Here is a small portion of the data:
Sensor ID Implant duration
13113 1
13113 1
13113 1
13144 1
13144 1
13144 2
13144 2
13144 2
13144 2
13144 2
14018 1
12184 2
13052 1
13052 1
12155 2
12155 3
12155 3
13069 2
13069 2
13018 1
13018 1
13019 1
13019 1
13049 1
13054 3
13060 3
13108 2
13108 2
So the count for:
Visit 1
should be 6
(13113, 14018, 13052, 13018, 13019, 13049),
Visit 2
should be 5
(13144, 12184, 12155, 13069, 13108), and
Visit 3
should be 3
(12155, 13054, 13060).
I tried DCOUNTA
but it doesn't return the count for the first occurrence, just the total number of entries with an implant duration of 1, 2, or 3. So for example it returns 13 for Visit 1
, 11 for Visit 2
, and 4 for Visit 3
.
I have a lot of data that needs to be preserved and counted so I don't want to apply a filter or remove duplicates.