Forum Discussion

PaulyCA's avatar
PaulyCA
Copper Contributor
Feb 18, 2024

Help Identifying Employees in a List

Hi!   So I have a list of about 80K rows.  It contains 80K file transfers that were either uploaded by employees, but many of them are downloaded anonymously.  I am trying to match the anonymous do...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Feb 18, 2024

PaulyCA 

=SORT(UNIQUE(FILTER(uploaded_by_column, downloaded_by_column="Anonymous")))

  • PaulyCA's avatar
    PaulyCA
    Copper Contributor
    Feb 19, 2024

    HansVogelaar  - Thank you for your reply.  We're getting close.  Let's see if we can knock this out with more specifics

     

    Here is an example of what the sheet would look like. 

     

    This shows that FileA was downloaded by Anonymous, but actually uploaded by John Smith (The file name will match every time to whomever downloads the data anonymously, but only show a single line for an upload by a single employee.  That's who I am looking for. 

     

    Same thing applies to File B -- that was downloaded several times by anonymous, but only uploaded by Jane

     

    So the output I am looking at in the 80K records is the list of employees names in Column B who uploaded something, that was downloaded by User "Anonymous"

     

    I know this is really hard to explain -- appreciate the help! 

     

    Column AColumn B
    FileAAnonymous
    FileAAnonymous
    FileAAnonymous
    FileAAnonymous
    FileAJohn Smith
    FileBAnonymous
    FileBAnonymous
    File BJane Smith
      • m_tarler's avatar
        m_tarler
        Bronze Contributor
        Feb 19, 2024

        PaulyCA  alternative single formula

        =LET(Files, A2:A11, Names, B2:B11,
anonFiles,UNIQUE(FILTER(Files,Names="Anonymous","")),
UNIQUE(FILTER(Names, (Names<>"Anonymous")*ISNUMBER(XMATCH(Files,anonFiles)),"")))

Resources