Need help to check whether it is dual depot

[evilgreenred]

Hi, I need some help regarding the excel format. So I am trying to divide the route into either single or dual depot.

For example, in the excel sheet given, for route 8, there are 3 bus depots that belong to this route, "Ang Mo Kio, Bishan, and Bedok North". So, route 8 belongs to dual depot. For route 18, only 1 bus depot "Bedok north" belongs to this route, so this route will be considered as a single depot. Finally, for route 31, we consider it as a dual depot since "Ang Mo Kio and Bedok North" belong to this depot. 

How should I use an excel formula to group accordingly?

[#2]

@evilgreenred A not very elegant, but rather simple way would be to create a pivot table and count the number of Depots on a Route. One occurrence returns "Single", anything greater than one returns "Dual".

[#3]

@evilgreenred 

Here is a formula solution that should work in all versions of Excel. If you have Microsoft 365, you could use the UNIQUE function to extract the unique route numbers instead.