+ Reply to Thread
Results 1 to 14 of 14

Index-match or Pivot table 1 name shows everything they did for the day in time order

  1. #1
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    122

    Index-match or Pivot table 1 name shows everything they did for the day in time order

    Good afternoon

    I have looked up a bunch of you tube videos and read up some templates
    I cant seem to find that that guides me along the right path to work out how to return the full list

    I have a sheet of Trucks that get loaded over the day with 2 to 6 drivers
    I like so I can type in a Name and it shows a list of everything they have done for the day in Time order e.g. 06:00 am to 16:00 pm


    The problems I have are there are pre stage cells and also Load cells

    cells D,E,F and Cells G,H,I

    both to list as 1 list in time order
    I tried to use a Pivot table
    I could not get both cells groups to link up right

    I have attached The sheet with on the right what we do now and copy and paste each line in 1 by 1
    for every driver and takes some time to do each one and there can and some times is errors when we miss a line
    the data I need to grab is from cell 2 all the way down to cell 80 odd

    I have deleted a lot of data on the sheet that is not needed

    The sheet I have just updated the results Would be shown on another Sheet e.g Kpi I just placed it all on the 1 sheet so it was easier to mock up the results
    there was a lot of date to sort over

    Any help would be great thanks
    JustinwB
    Attached Files Attached Files
    Last edited by justinwb; 11-06-2017 at 04:12 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,270

    Re: Index-match or Pivot table 1 name shows everything that did for the day in time order

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    Hi,

    Try the following:

    In R9:

    Please Login or Register  to view this content.
    S9:
    Please Login or Register  to view this content.
    In U9:
    Please Login or Register  to view this content.
    In V9:
    Please Login or Register  to view this content.
    All the above array entered, to be confirmed by pressing CTRL+SHIFT+ENTER

    In T9:
    =IFERROR(S9-R9,"")

    See the attached file.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    122

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    Good afternoon Cbatrody

    Thanks for your help
    still learning the you tube videos I was watching never got in to that much info for the index

    I have looked over the code
    and it seems to only be looking in Rows G,H,I is it possible to have other rows also with the index Rows D,E,F which is the pre stage area for our guys
    they do a lot of pre stages and some of our guys that is 70% of there work day
    and having the Truck id changed to say prestage with truck id for D,E,F

    Thanks again for your help

    JustinwB

  5. #5
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    Hi Justin,

    The only way I see to achieve this is to summarize G to I & D to E separately one below the other, then create a new summary to get all of them into one table.

    See the attached file.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    122

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    Thanks Cbayrody

    Thats a bummer thought there be a nice way to pack it all in the 1 table

    I was able to copy the 2 tables then sort them on another page which lists them all in date order

    Kind of works the way I was looking
    is there any way in the 2nd table to changed the Id of the truck to say like Prestage Id other then Truck 1

  7. #7
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    Quote Originally Posted by justinwb View Post
    is there any way in the 2nd table to changed the Id of the truck to say like Prestage Id other then Truck 1
    I do not see "Prestage ID" field in the master table.
    Attached Images Attached Images

  8. #8
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    122

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    Sorry about that cbatrody
    we use the Truck number as a way to track the movements of that Trucks order for each Truck on site which is why some have A after them
    then there are 2 steps for each truck 1 prestage if we have time before the truck is on site and Load time which is once the truck is on site
    which is why I few trucks dont have any thing in the pre stage name part

    the heading in D1 is the prestage row and the G1 is the Loader Name

    Think the easiest way to fix this problem is on the first table I just added a row in front that says Load and the Table below I placed Pre stage in front so once I copy and paste all and then sort it kepts the front rows

    Thanks again for all your help

    JustinwB
    Attached Images Attached Images
    Last edited by justinwb; 11-06-2017 at 08:40 AM.

  9. #9
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    I've made the necessary changes in the attached file.

    As suggested by you,

    Added a new column for both the tables - Load & Pre Stage, brought this column into the final summary.

    In Z9:

    Please Login or Register  to view this content.
    array entered - to be confirmed by pressing CTRL+SHIFT+ENTER
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    122

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    Cbatrody You are the best

    Did not even think of using a IF at the end

    Will work on it over the next few days I have to add it all to our master Dispatch sheet template.xlsxm with all the Kpi's we have set up
    and all the VB scripts going to be a big job 9 sheets so far well worth the time

    Its all the new billing system its doing my head in this will now make it so much easier for doing all there billing reports
    was taking us from 1 to 2 hours a day sorting out all the data for every person we have in dispatch
    at work they now want us to track every min of our forklift drivers for billing
    and if there is a time not seem on the sheet we need to find out why this time was lost and ask what they where doing for the missing time
    Which is now great cause I can select there Time took and it will give me a the total time so if it does not = 7.6 hours
    and we can also now see how long each truck takes to load per person and easily be able to compare each Forklift driver to each other

    Thanks again for all your help

    I Never would of been able to get this far with out your help
    I'm going to head off to bed its 12:07 am here in Australia

    My lovely daughter will be waking up in 5 to 6 hours ;-)

  11. #11
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    122

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    Good Afternoon Cbatrody

    5:30 am this morning daughter was up ;-(

    Just working on the sheet today Mind is wide awake

    Just spent the last few hours redoing the sheet and moved it all over to a new sheet and relining it up

    I have done a few tests It will like if there is a over lap of times it bugs out which is great
    means I need to make closer note of the times on there sheets

    I have attached the new sheet with just the 2 sheets
    I'm not sure why once I got all the Lines right I draged the 7 lines of Code down and works great till
    there a black line and Not sure why it is placing a 0 on all the pallet cells in all the stage, pre stage, and the Summary

    on your sheet you did all the cells are blank I looked over the code

    yours Stage load
    =IFERROR(INDEX(K$1:K$26,MATCH($Q18&$R18&$S18&$U18,G$1:G$26&H$1:H$26&I$1:I$26&A$1:A$26,0)),"")

    Mine stage load

    =IFERROR(INDEX(Date!K$1:K$87,MATCH($K5&$L5&$M5&$O5,Date!G$1:G$87&Date!H$1:H$87&Date!I$1:I$87&Date!A$1:A$87,0)),"")

    I cant see any thing different in the Code that would cause 0 to be placed over a blank Space

    The attachment got a Bunch of Marcos just disable them you dont need them enabled for this Sheet

    any help would be great
    Attached Files Attached Files

  12. #12
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    Please modify the formula in H5 (KPI full list) as following:

    Please Login or Register  to view this content.

    And P5 as below:

    Please Login or Register  to view this content.
    both array entered, to be confirmed by pressing CTRL+SHIFT+ENTER

    See the attached file.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    09-24-2017
    Location
    Vic Australia
    MS-Off Ver
    Office 2013 Work / Office 365 Home
    Posts
    122

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    Thanks Cbatrody

    Going to run it for the next few days and see if there are any other bugs

    its different running it at home with time to check for bugs and testing set cells
    once I run it live at work with 20 to 40 trucks turning up in a 8 hour time frame and tracking everybody in dispatch This will help with all our reporting

    Thanks again Will kept you up today

    JustinwB

  14. #14
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Index-match or Pivot table 1 name shows everything they did for the day in time order

    You are welcome Thanks for the rep Justin!

    If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Index(Match) on a Pivot table duplicating last true value instead of returning blank
    By iracknback in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-21-2016, 02:09 PM
  2. SQL Pivot table shows percentages as date and time
    By mhf89 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-19-2015, 09:09 PM
  3. Replies: 14
    Last Post: 04-09-2015, 12:43 PM
  4. Replies: 2
    Last Post: 12-18-2014, 09:52 AM
  5. Index/Match functions used with a pivot table
    By razkowski in forum Excel General
    Replies: 1
    Last Post: 07-12-2012, 06:22 PM
  6. Index and match from a pivot table
    By XLBob in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2009, 07:44 AM
  7. Index & Match on Pivot Table
    By Nelson in forum Excel General
    Replies: 2
    Last Post: 04-11-2005, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1