Home

Combine data from multiple rows

%3CLINGO-SUB%20id%3D%22lingo-sub-558257%22%20slang%3D%22en-US%22%3ECombine%20data%20from%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-558257%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20am%20working%20with%20a%20very%20large%20dataset%2C%2094%2C000%20rows.%20Each%20row%20is%20a%20specific%20action%20per%20client%20and%20the%20time%20stamp.%20For%20example%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EClient%20Name%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Location%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Action%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDate%26amp%3BTime%3C%2FP%3E%3CP%3EAA%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BWing%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Change%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B05%2F10%2F2019%200800%3C%2FP%3E%3CP%3EAA%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BWing%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Stop%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2005%2F10%2F2019%200815%3C%2FP%3E%3CP%3EAB%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BWing%20B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Change%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B05%2F10%2F2019%200700%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20want%20to%20do%20is%20summarize%20for%20each%20client%3C%2FP%3E%3CP%3E(1)%20how%20many%20changes%20occurred%20in%20a%20given%20time%20period%26nbsp%3B%3C%2FP%3E%3CP%3E(2)%20How%20many%20clients%20had%20changes%20in%20less%20than%201%20hour%3C%2FP%3E%3CP%3E(3)%20I%20would%20like%20to%20graph%20this%20globally%20and%20per%20location%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20do%20this%20with%20Pivot%20tables%20or%20should%20I%20create%20another%20sheet%20and%20column%20to%20extract%20this%20information%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-558257%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERequests%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-563146%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20data%20from%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-563146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F340018%22%20target%3D%22_blank%22%3E%40Drescue%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20recommend%20you%20split%20out%26nbsp%3B%20your%20date%20and%20time%20into%202%20columns%20and%20then%26nbsp%3B%20a%20Pivot%20Table%26nbsp%3B%20be%20able%20to%20give%20you%20the%20results%20you%20need%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-563199%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20data%20from%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-563199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F340018%22%20target%3D%22_blank%22%3E%40Drescue%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESomething%20along%20the%20lines%20of%20the%20attached%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-563200%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20data%20from%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-563200%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F340018%22%20target%3D%22_blank%22%3E%40Drescue%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESomething%20along%20the%20lines%20of%20the%20attached%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Drescue
Occasional Visitor

Hello, 


I am working with a very large dataset, 94,000 rows. Each row is a specific action per client and the time stamp. For example: 

 

Client Name      Location      Action           Date&Time

AA                     Wing A        Change         05/10/2019 0800

AA                     Wing A        Stop              05/10/2019 0815

AB                     Wing B        Change         05/10/2019 0700

 

What I want to do is summarize for each client

(1) how many changes occurred in a given time period 

(2) How many clients had changes in less than 1 hour

(3) I would like to graph this globally and per location 

 

Is there a way to do this with Pivot tables or should I create another sheet and column to extract this information?

 

Thank you! 

3 Replies

@Drescue 

 

I'd recommend you split out  your date and time into 2 columns and then  a Pivot Table  be able to give you the results you need

@Drescue 

 

Something along the lines of the attached

 

 

@Drescue 

 

Something along the lines of the attached

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies