Repeated Measures (CountIF and VLookup)

%3CLINGO-SUB%20id%3D%22lingo-sub-1937169%22%20slang%3D%22en-US%22%3ERepeated%20Measures%20(CountIF%20and%20VLookup)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1937169%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20I'm%20dealing%20with%20in%20Excel%20has%20IDs%20with%20multiple%20entries%20(visit%20counts)%2C%20as%20seen%20below.%20Each%20entry%20for%20an%20ID%20has%20been%20sorted%20by%20date%20(oldest%20first%20%E2%80%93%20not%20shown)%20and%20has%20been%20assigned%20a%20visit%20count%20via%20the%20following%20formula%20%5B%3DCOUNTIF(%24B%242%3AB2%2CB2)%5D.%20I%20then%20assessed%20each%20unique%20ID%20%2B%20visit%20count%20by%20other%20criteria%20not%20shown%20to%20determine%20if%20they%20are%20eligible%2C%20also%20displayed%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20be%20able%20to%20filter%20the%20data%20so%20that%20if%20an%20ID%20is%20eligible%2C%20all%20ID%20entries%20will%20appear%2C%20not%20just%20the%20ones%20that%20have%20a%20%221%22%20for%20F2%20%E2%80%93%20Eligible.%20As%20you%20know%2C%20when%20filtering%20via%20%22Sort%20and%20Filter%22%20for%20those%20who%20are%20eligible%2C%20it%20will%20only%20show%20the%20specific%20ID%20entries%20that%20have%20Eligible%20%3D%20%221%22.%20Thus%2C%20for%20ID10013%2C%20only%20the%20second%20visit%20count%20would%20appear%20and%20not%20the%20first.%20That%20means%20I%20will%20need%20to%20create%20a%20new%20variable%20that%20will%20%3D%20%221%22%20for%20each%20ID%20and%20visit%20count%20if%20ANY%20visit%20count%20of%20an%20ID%20is%20eligible%2C%20but%20I%20am%20unable%20to%20determine%20the%20formula%2Fcode%20that%20will%20allow%20this%20to%20happen.%20I%20have%20tried%20some%20versions%20of%20VLookup%20to%20no%20success.%20Any%20guidance%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22366px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2218px%22%3E%3CSTRONG%3EB2%20(ID)%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2218px%22%3E%3CSTRONG%3EC2%20(Visit%20Count)%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2218px%22%3E%3CSTRONG%3EF2%20(Eligible)%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10001%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10002%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10003%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10003%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10003%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10003%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10003%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10003%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10003%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10003%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10003%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10004%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10005%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10006%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10007%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10008%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10009%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10010%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10011%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10012%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10012%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10012%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10013%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10013%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22107px%22%20height%3D%2230px%22%3E10014%3C%2FTD%3E%3CTD%20width%3D%22109px%22%20height%3D%2230px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22149px%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1937169%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1937394%22%20slang%3D%22en-US%22%3ERe%3A%20Repeated%20Measures%20(CountIF%20and%20VLookup)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1937394%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F883942%22%20target%3D%22_blank%22%3E%40tdwagner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D--(COUNTIFS(%24B%242%3A%24B%2426%2CB2%2C%24D%242%3A%24D%2426%2C%22%26gt%3B0%22)%26gt%3B0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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)
1000110
1000210
1000310
1000320
1000330
1000340
1000350
1000360
1000370
1000380
1000390
1000410
1000510
1000610
1000710
1000811
1000910
1001010
1001110
1001210
1001220
1001230
1001310
1001321
1001410
2 Replies

@tdwagner 

That could be

=--(COUNTIFS($B$2:$B$26,B2,$D$2:$D$26,">0")>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)