standing_bid_list <- read.csv('StandingBidAward9_25_2025 3_14_24 PM.csv')

bypassed <- standing_bid_list %>% mutate(Name = paste(PreferredName, LastName))
                              %>% select(Id, EmpNo, Name, Seniority, Status, AwardDate, AwardPos, FromBypassComment)
                              %>% filter(Status == 'Bypassed' & startsWith(AwardPos, 'ER7 CA'))
                              %>% arrange(AwardDate)

accepted <- standing_bid_list %>% mutate(Name = paste(PreferredName, LastName))
                              %>% select(Id, EmpNo, Name, Seniority, Status, AwardDate, AwardPos, FromBypassComment)
                              %>% filter(Status == 'Accepted' & startsWith(AwardPos, 'ER7 CA'))
                              %>% arrange(AwardDate)

bypassed %>% filter(!EmpNo %in% accepted$EmpNo & Seniority != 'NA')
         %>% select(EmpNo, Seniority, Name) %>% arrange(Seniority)