Solved

Help adding more columns to my pivot table

Posted on 2016-10-21
19
34 Views
Last Modified: 2016-10-24
hi

Ive had a little help in creating a pivot table on my excel workbook but could do with a little more assistance before its completed.

 I will attach my dummy excel file hopefully someone can take a look and help me with the last two columns? the excel file is pretty self explanatory

The columns i require help on are time before break and time after break

between hours of 12:00 and 14:00 on each day there is a break of around 1 hour so 01:00:00. Just like with the first time and the last time on each day i wish to state the time before the break and the time after the break, in the attachment I have the pivot table completed without the last two columns
Dummy.xlsx
0
Comment
Question by:Legolas786
  • 10
  • 8
19 Comments
 
LVL 22

Expert Comment

by:yo_bee
ID: 41854695
what is your duration column for?
What is each time entry for each share group?
I read is as if each entry is either and in or out and the Min is the first in and Max is the Last out.  I hat correct?  
With that I am not sure what you are looking to calculate.   You mention 12 to 14 is a break period for about 1 hr.  I see Share1 has a 12:20 time and a 13:35 time. if that is the case I read as 1:15 or 1.25 hours that the person was on break for 1/1/2016, On 1/2/2016 I see a lot more breaks.  Is that correct.


Also your example table seems to have Before and After examples that do not match the source table data.
I see Share1 on 1/3/2016 have four entries.

There seems to be information missing from your question.
Please expand more if possible on what you are looking  for.
0
 

Author Comment

by:Legolas786
ID: 41854918
hi,

I will do my best to explain

I currently have data in four columns

Column A (Name) will only ever contain two users in this scenario Spare1 and Spare2 Column B (Date) Column C (Time) Column D (Duration)

Currently it looks like this

first
I was looking to use VBA to achieve the following but was told it would be easier with the pivot table which is used in the dummy file

second
So the pivot table contains a record for each spare user, each date, the first time, the last time, the number of times recorded, and each duration more than 00:05:00 - this part is all working as you can see in my dummy text file.  Now i just wish to record the times between 12:00 and 14:00 for each user on each day where there is a break of more than 1 hour so to calculate each time between 12:00 and 14:00 for each day and if it is greater than 00:01:00 then to record these - as per my example below.

result
Hope that makes sense.
0
 
LVL 22

Expert Comment

by:yo_bee
ID: 41855230
It is a bit clearer, but I still do not understand what the duration column is for and it may not be needed for this.  Since it does not seem to come into play in your question I will ignore that column.

If I build my own Pivot Table and see times that are grouped under Spare1 that do not match what your example desire outcome.

  img1
I am not really understand what each record stands for. Do you want to completely drop values that are outside the 12 - 14 window?  I have attached a modified excel file that will show you what I did.  
Copy-of-Dummy.xlsx
0
 

Author Comment

by:Legolas786
ID: 41855251
hi

I'm sorry but this is my fault as i was manually entering the fields in the time before break and time after break they were incorrect.  Please see latest attachment and this should be correct now.

Also, I do need the duration column as that counts each time that is higher than 00:05:00 and displays this in Sum of >5 column.  A quick overview of each columns in the pivot table

Name      = shows the user so either spare1 or spare2
date      = shows each date
Min of time2      =shows the first time of the day
Max of time2      =shows the last time of the day
Count of time      =shows the total numbers of times in each day
Sum of >5      =similar to count of time column but shows total number of times that are higher than 00:05:00
time before break =should show first time during 12:00 to 14:00 windows if break more than 1 hour      
time after break =should show last time during 12:00 to 14:00 windows if break more than 1 hour
Dummy.xlsx
0
 
LVL 22

Expert Comment

by:yo_bee
ID: 41855276
Is this what you are looking for?

Please see my Worksheets Tab REWORKED.  
I added a column to the table that is the source of the data.
=IF(AND(M3>=TIME(12,0,0),M3<=TIME(14,0,0)),M3,"")

Copy-of-Dummy.xlsx
0
 

Author Comment

by:Legolas786
ID: 41855297
hello,

please see my dummy file attached.

If you look at spare2 for date of 05/01/2016 there are three times recorded within the 2 hour period of 12:00 to 14:00.

These are 12:15 12:30 and 13:45, the two times that should be picked in the pivot table are 12:30 and 13:45 as these are the closest times (within the 12:00 and 14:00 period) and over an hour however the pivot table shows the times as 12:15 and 13:45? would it be ok to change the layout of the pivot table slightly? this is in the dummy file.  Many thanks for your help
Dummy4.xlsx
0
 
LVL 22

Expert Comment

by:yo_bee
ID: 41855306
you have way to many variables in play here.
You stated in a previous reply
time before break =should show first time during 12:00 to 14:00 windows if break more than 1 hour      
time after break =should show last time during 12:00 to 14:00 windows if break more than 1 hour
By this statement my formula meets your requirements.  This variable of three entries between 12 & 14 now you are asking something a bit different than your previous reply.
0
 

Author Comment

by:Legolas786
ID: 41855326
ok i assume this is not possible then?
0
 
LVL 22

Expert Comment

by:yo_bee
ID: 41855333
It probably is, but i need to work the formula to build the data to meet your requirements.

I see if I can work on this
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 22

Expert Comment

by:yo_bee
ID: 41855406
I got the formula that works for your data table

IFERROR(IF(AND([@Name]=A4,[@date]=B4,C3>=TIME(12,0,0),C3<=TIME(14,0,0),TEXT((C4-C3),"h")>="1"),C3,""),IF(C3<=TIME(14,0,0),C3,""))

Open in new window


Above is the modified formula for the Breaktime column.
copy_Dummy4.xlsx

Let me know if this works
0
 

Author Comment

by:Legolas786
ID: 41856841
hi Yobee

First i want to apologize for the late reply as I have been away from my computer for the last couple days.

When i was looking at my data I found that the time should be changed from 12:00 to 15:00 rather than 14:00.  So on your formula I made the following changes:-

=IFERROR(IF(AND([@Name]=A3,[@date]=B3,C2>=TIME(12,0,0),C2<=TIME([b]15[/b],0,0),TEXT((C3-C2),"h")>="1"),C2,""),IF(C2<=TIME([b]15[/b],0,0),C2,""))

Open in new window


The result that appear are strange, column F (BreakTime) appears to only display the first time before the 1 hour break and not the later time? any ideas why? many thanks
0
 
LVL 22

Expert Comment

by:yo_bee
ID: 41856868
I just changed it on mine and it seems to work.


Copy-of-copy_Dummy4.xlsx
0
 

Author Comment

by:Legolas786
ID: 41857115
ok thanks I tried your formula but it has the same results, i really dont know why but my on mine its only picking up the first time? i will attach it and hopefully you can spot something that is causing it not to work?
DummyLatest.xlsx
0
 
LVL 22

Accepted Solution

by:
yo_bee earned 500 total points
ID: 41857641
Here is what I needed to do.
We needed a AND(or  statement,

=IFERROR(IF(AND(OR(TEXT((C3-[@time]),"h")>="1",TEXT(([@time]-C1),"h")>="1"),[@time]>=TIME(12,0,0),[@time]<=TIME(15,0,0)),C2,""),"")

Open in new window


copy_Dummy5.xlsx
0
 
LVL 22

Expert Comment

by:yo_bee
ID: 41857658
I was not given enough time to reply, but found a solution (I Think) after the asker asked to have the question deleted
0
 

Author Comment

by:Legolas786
ID: 41857662
Sorry my bad, i thought i would just give up :(
0
 

Author Closing Comment

by:Legolas786
ID: 41857666
thanks buddy that sorted it :)
0
 
LVL 22

Expert Comment

by:yo_bee
ID: 41857684
Never give up ;)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Hiding column macro 10 28
Copy value from a certain cell 5 24
FormulaArray VBA Issue 6 17
excel connection properties parameters grayed out 5 24
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now