using excel to track movement of goods

Hi there

i was just looking for some help when it comes to comparing lists and deleting rows.

i am currently trying to set up a workbook that will track my trucks and trailers when they leave my yard and come back again

every day i want to enter which ones leave and where they are going

then i want to be able to in the morning aswell list which ones came back

then compare the 2 lists and work out which of the trailers are still out and which are back

i need to do this every day, except for the weekends where ill be entering all the data friday arvo, and all the returned trailers monday morning.

My main reason for doing this workbook, is because i think im missing a few trailers. a few dollys. I have a fleet of 100 or so trailers so it becomes a little hard to remember where they all went.

Any help would be greatly appreciated

Cheers

Jase

Excel 2010 has a new (I believe) feature called Delete Duplicates in the Data tab. This may help with your problem. Another feature is to conditional format either Unique or Duplicate data in a selection. This is located on the home tab under Conditional Formatting Rules. See if these would be of help for your task.

Marvin,

I have tried these, but its going to be a much more complicated workbook in the end so i need an automated macro to do the work.

Depending on the destinations aswell, the trailers can take up to a week to return

I dont know really how to explain what i am trying to do, but ill give it a go

I have a sheet i enter all the trailers that are going out for that day, with 2 columns, destination and rego

After i have entered the trailers (its about 10 - 20 a day) I want to click a command button copying all those into another bigger sheet. Once that has been coppied then the first sheet info gets deleted ready for the next day.

Ok

on the sheet its copied on to, it needs to add to the already existing list of trailers out of the yard.

So far i can do this keep adding to it

My problem is the following.

I enter also on another sheet (TRAILERS IN) now that sheet then should delete from the bigger sheet of trailers which ones are back. Does that make sense at all?

I know it probably doesn't and explaining things over forums never come out like how you want them too

but hey ill give it a go

Cheers for the help

Jase

Have a look at the attached workbook as a starter. There are some notes on the Master sheet.

It's based on a Staff Time Tracker that I posted earlier with some slight modifications. It should do roughly what you need.

Apologies in advance if you find any references to staff numbers, etc.

Beware, if you add any columns, you will need to modify the offsets in the code.

Enjoy

Hi Jas1985,
A short example sheet might be what we need to see the problem. I'm thinking that there are features in Excel that could make your job much easier. Setting up your data in a table and filtering or sorting it comes to mind first. Second a DTPicker ot help document when trucks leave or come back. Another possability would be a Validation List with all possible trucks in it. You could then just drop down and pick a truck that was leaving. You could also have a destination drop down if there are standard places. Perhaps a filtered list in a dropdown only showing trucks that are in the yard would be appropriate.

All these possabilities in Excel but we need a better shot looking at some sampel data. Can you trim down your data and go to the advanced and post the file? That would really help.

Hi Jase,

I can't help thinking that you're making things more complicated than they need to be. Keeping everything in one sheet means you don't have to look at different sheets, delete them from one and add to another, etc.

In the sample I have provided, you can filter on the In Time column for blanks to list any trucks which are still out. Clearly you can't book one out on another date if it hasn't come back. And you could filter on Trailer ID to get a list of all the movements of a particular truck; you could also filter on the driver, destination or the customer to get an analysis.

MarvinP has suggested a drop down box of Trailer IDs ... the sample has that. It shouldn't be too difficult to put an indicator next to the trailer when it goes out and remove it when it comes back in. I have to admit, I'm not sure how to use that to filter the Validation List but I'm sure that can be done too.

Regards

I have uploaded where i am at at the moment

please be aware the following,

The only sheets i want visible are

FLEET LIST (2)
TRAILERS IN DAILY
TRAILERS OUT DAILY

others will be hidden

idealy i want the following

i click the input button daily trailers are added to the list

when i get trailers back in i want to put the rego and kilometers in click input and all it needs to do is delete it from the list and record the kmon the fleet list (2) sheet

i know the formatting is a bit poor, but ill get to that later. im only just starting out

cheers

Jase

any ideas guys

The attached workbook has some code for the button on the Trailers Out Daily worksheet, which I have called btnBookOut and captioned "Book Out".

I've added a Named Range for the Fleet List which is used in a Data Validation drop down box.

If you press the button when there's no data, you'll get a warning message.

I'll let you have a go at the button for booking trailers back in :-)

Let me know how you get on.

Regards

Thanks guys, so far i have that info, and i can copy from one sheet to another.

The main problem i have is comparing 2 lists for the same info and deleting values in one list if they duplicate.

I have it set up like this

Input trailers out on a daily basis, copied them to a new sheet and list
input trailers in on a daily basis, copied them to a new list in same sheet

Now here lies the problem

I need to compare the 2 lists see which ones are back and delete the ones in the "Trailer Out" List

Also before i do that, i need to copy all the info of that trailer (When it left where it went and when it came back) to another sheet so i can track how long they are gone for at certain sites

I hope that makes sense, ill upload what i am trying to say

Cheers

Jase

uploaded file

cheers