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

[justinwb]

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

[bebo021999]

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.

[cbatrody]

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.

[justinwb]

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

[cbatrody]

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.

[justinwb]

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

[cbatrody]

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.

[justinwb]

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

[cbatrody]

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

[justinwb]

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 ;-)

[justinwb]

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

[cbatrody]

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.

[justinwb]

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

[cbatrody]

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.