Is there a way to identify only the new names in a data set appearing on a specfic date on GOOGLE SHEETS?

Crazy8 0
Viewed 33

I have 2 columns on a google sheet that keeps updating with new data everyday:

  1. Date
  2. Usernames

What i want to arrive at? I want date wise new usernames that were not present on the previous date on a new sheet.

Example:

  • I have list of 10 names on 1st sep 2022
  • Another 15 names were added on 2nd sep 2022 to the same list
  • Out of the 15 names on 2nd sep 2022, 10 were present on 1st sep 2022
  • I want the names of these 5 new users on a new sheet against the same date i.e.2nd sep 2022

I have also attached a sample excel sheet for reference. Looking forward to it. https://docs.google.com/spreadsheets/d/1sojDAHKIrTSNskqLCRbLTXFXXaiRQlhLHbWo_XCITnc/edit?usp=sharing

google-sheets-formula google-sheets
3 Answers

try:

=QUERY(Data!B3:C, "select C,count(C) where C is not null group by C pivot B")
player00

For E2 cell use- (See your sheet harun24hr).

=FILTER(C3:C,B3:B<=E2)

For F2 cell use-

=FILTER(FILTER(C3:C,B3:B<=F2),INDEX(COUNTIFS(FILTER(C3:C,B3:B<=E2),FILTER(C3:C,B3:B<=F2)))=0)
Harun24hr0

In sheet Terio

E2

=TRANSPOSE(UNIQUE(B3:B))

E3

=ARRAYFORMULA(FILTER($C$3:$C,$B$3:$B=E$2,NOT(ISNUMBER(MATCH($C$3:$C,D3:D,0)))))

drag to right.

To exclude all previous users before a date in row 2 use this

E3

=ARRAYFORMULA(IF(E2="","", FILTER($C$3:$C,$B$3:$B=E$2,NOT(ISNUMBER(MATCH($C$3:$C,FLATTEN(SPLIT(TEXTJOIN("",TRUE,$D3:D),"")),0))))))

another way

=ARRAYFORMULA(IF(E2="","", FILTER($C$3:$C,$B$3:$B=E$2,NOT(ISNUMBER(MATCH($C$3:$C,FILTER(FLATTEN($D3:D),FLATTEN($D3:D)<>""),0))))))

Bye

Terio0
Related