Forum Discussion
Copying over data that doesn't "have a pair"
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?
=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,"")) =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"),""))
