Copying over data that doesn't "have a pair"

Copper Contributor

I have a sheet 'Site A History' that shows every sign in/out as recorded on a Microsoft Form:


I want to copy over any entries where for a given Name e.g. Apple Banana, in column C there is an "In" but not an "Out". So for the above entries, I would only want to copy over row 2. I.e I want to have a record of those that have signed in but not out.

As this sheet will have all past entries, I believe it would have to be a case of Number In>Number Out.

There is no list of potential Names, as these are entered via Form. 


Is there a way to do this?


2 Replies
best response confirmed by murphy2410 (Copper Contributor)



=UNIQUE(FILTER('Site A History'!$A$2:$B$1000,COUNTIFS('Site A History'!$A$2:$A$1000,'Site A History'!$A$2:$A$1000,'Site A History'!$C$2:$C$1000,"In")>COUNTIFS('Site A History'!$A$2:$A$1000,'Site A History'!$A$2:$A$1000,'Site A History'!$C$2:$C$1000,"Out"),""))

What if you just check if the number of entries is ODD or EVEN?
=LET(n, UNIQUE(data[name]), c, ISODD(COUNTIFS(data[name],n)), FILTER(n,c,""))