Need help to check whether it is dual depot

%3CLINGO-SUB%20id%3D%22lingo-sub-2604477%22%20slang%3D%22en-US%22%3ENeed%20help%20to%20check%20whether%20it%20is%20dual%20depot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2604477%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20need%20some%20help%20regarding%20the%20excel%20format.%20So%20I%20am%20trying%20to%20divide%20the%20route%20into%20either%20single%20or%20dual%20depot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20in%20the%20excel%20sheet%20given%2C%20for%20route%208%2C%20there%20are%203%20bus%20depots%20that%20belong%20to%20this%20route%2C%20%22Ang%20Mo%20Kio%2C%20Bishan%2C%20and%20Bedok%20North%22.%20So%2C%20route%208%20belongs%20to%20dual%20depot.%20For%20route%2018%2C%20only%201%20bus%20depot%20%22Bedok%20north%22%20belongs%20to%20this%20route%2C%20so%20this%20route%20will%20be%20considered%20as%20a%20single%20depot.%20Finally%2C%20for%20route%2031%2C%20we%20consider%20it%20as%20a%20dual%20depot%20since%20%22Ang%20Mo%20Kio%20and%20Bedok%20North%22%20belong%20to%20this%20depot.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20should%20I%20use%20an%20excel%20formula%20to%20group%20accordingly%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2604477%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2604615%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20to%20check%20whether%20it%20is%20dual%20depot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2604615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1119338%22%20target%3D%22_blank%22%3E%40evilgreenred%3C%2FA%3E%26nbsp%3BA%20not%20very%20elegant%2C%20but%20rather%20simple%20way%20would%20be%20to%20create%20a%20pivot%20table%20and%20count%20the%20number%20of%20Depots%20on%20a%20Route.%20One%20occurrence%20returns%20%22Single%22%2C%20anything%20greater%20than%20one%20returns%20%22Dual%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies

@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".

@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.