Forum Discussion
Repeated Measures (CountIF and VLookup)
Hi all,
The data I'm dealing with in Excel has IDs with multiple entries (visit counts), as seen below. Each entry for an ID has been sorted by date (oldest first – not shown) and has been assigned a visit count via the following formula [=COUNTIF($B$2:B2,B2)]. I then assessed each unique ID + visit count by other criteria not shown to determine if they are eligible, also displayed below.
My goal is to be able to filter the data so that if an ID is eligible, all ID entries will appear, not just the ones that have a "1" for F2 – Eligible. As you know, when filtering via "Sort and Filter" for those who are eligible, it will only show the specific ID entries that have Eligible = "1". Thus, for ID10013, only the second visit count would appear and not the first. That means I will need to create a new variable that will = "1" for each ID and visit count if ANY visit count of an ID is eligible, but I am unable to determine the formula/code that will allow this to happen. I have tried some versions of VLookup to no success. Any guidance would be greatly appreciated.
| B2 (ID) | C2 (Visit Count) | F2 (Eligible) |
| 10001 | 1 | 0 |
| 10002 | 1 | 0 |
| 10003 | 1 | 0 |
| 10003 | 2 | 0 |
| 10003 | 3 | 0 |
| 10003 | 4 | 0 |
| 10003 | 5 | 0 |
| 10003 | 6 | 0 |
| 10003 | 7 | 0 |
| 10003 | 8 | 0 |
| 10003 | 9 | 0 |
| 10004 | 1 | 0 |
| 10005 | 1 | 0 |
| 10006 | 1 | 0 |
| 10007 | 1 | 0 |
| 10008 | 1 | 1 |
| 10009 | 1 | 0 |
| 10010 | 1 | 0 |
| 10011 | 1 | 0 |
| 10012 | 1 | 0 |
| 10012 | 2 | 0 |
| 10012 | 3 | 0 |
| 10013 | 1 | 0 |
| 10013 | 2 | 1 |
| 10014 | 1 | 0 |
tdwagner As a variant and in case your Excel version supports the FILTER function:
=IFERROR(VLOOKUP(B2:B26,FILTER(B2:D26,D2:D26=1),3,0),0)
