Home

Using Excel sheet to group times

%3CLINGO-SUB%20id%3D%22lingo-sub-636243%22%20slang%3D%22en-US%22%3EUsing%20Excel%20sheet%20to%20group%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-636243%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20the%20attached%20spreadsheet%20that%20indicates%20the%20travel%20time%20between%202%20different%20zip%20codes.%20Is%20there%20a%20function%2Fmacro%20that%20enables%20me%20to%20create%20different%20groups%20of%20zip%20codes%20that%20are%20for%20example%2030%20minutes%20or%20less%20from%20each%20other%3F%20For%20example%3A%3C%2FP%3E%3CP%3EGroup%20A%3A%20zips%20xxxxx%2C%20xxxxx%2C%20xxxxx%2C%3C%2FP%3E%3CP%3EGroup%20B%3A%20yyyyy%2C%20yyyyy%2C%20yyyyy%2C%20yyyyy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-636243%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-636697%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Excel%20sheet%20to%20group%20times%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-636697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347005%22%20target%3D%22_blank%22%3E%40Julian83%3C%2FA%3E%20if%20you%20change%20your%20data%20layout%20(which%20I%20did%20using%20Get%20%26amp%3B%20transform%20with%20the%20result%20on%20the%20Query%20tab)%20and%20then%20generate%20a%20pivot%20table%20you%20can%20do%20what%20you%20need.%20I%20grouped%20the%20TravelTime%20column%20in%20the%20pivottable%20in%20buckets%20of%2010%20minutes%2C%20this%20can%20easily%20be%20changed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Julian83
Occasional Visitor

I have created the attached spreadsheet that indicates the travel time between 2 different zip codes. Is there a function/macro that enables me to create different groups of zip codes that are for example 30 minutes or less from each other? For example:

Group A: zips xxxxx, xxxxx, xxxxx,

Group B: yyyyy, yyyyy, yyyyy, yyyyy

1 Reply

@Julian83 if you change your data layout (which I did using Get & transform with the result on the Query tab) and then generate a pivot table you can do what you need. I grouped the TravelTime column in the pivottable in buckets of 10 minutes, this can easily be changed.