I'm working with HR data for about 25k employees. This data was spread across 15 different spreadsheets with about 5 tabs per spreadsheet which I've consolidated. The information was labeled only with their employee ID. I currently have about 225k rows of information (some duplicates, some not) for about 25k employees that I plan on consolidating with power query. The names had to be in a specific format (vertically instead of horizontally) so I've ran a formula and I've got exactly what I need. But I need to remove the values in red in order to remove the duplicate error.
Any formulas, chart tricks/hacks, anything to automatically remove the reds?