Inventory Management

Dear All,

I am not the best hand @ Excel, so would like your help on this.

The situation is something like this....

I deal in timber. I receive logs in my log park. When ever I receive a log in my log park, each log is given a unique number and its details liek the diameter, length, speies, volume etc. are noted down. All these dataare then entered into Excel and maintained there in simple Excel sheet, columnwise.

Like this i accumulate thousands of logs. The problem ocurs when i ship out these logs. The logs are not stacked in any order. They are just stacked randomly. So, out of thousands of logs, as soon as I have a shipment, I have to move hundreds of logs into the port.

Now, I have to prepare a packing list of all the logs that I have put in the port. The problem is this paking list.

What I do now is to have two sheets, one is my master excel sheet whih has details of all the logs and the other one is just the list of logs that I have moved to the port.So i just copy and paste the list of logs from the port list and paste in my master list, then sort and then painstakingly, match each log to log.

What i wanted to know is, if theres a better way of organising the data, so that I just enter the list of logs I have moved into the port and i get all the details of the logs like the dia, length, volume etc.

Or is Access a better way of managikng this data ? I use excel because it is easier to manage with and my staff, who are not too familiar with computers have grwon familiar with excel over time due to thsi data entry trhat we do.

Pls suggest a better way of managing data. Looking forward to hearing from you.

Regards,
Ajit

Newtonboy,

Your master sheet for incoming logs seems OK in design. Maybe you could
consider adding a column with the Acquisition Date.

As to your shipments:
First of all, if you have the log id's in a column in the port sheet,
the VLOOKUP() function can be used to bring any information from the
master sheet. Assuming the id's are in column A:A and that similarly
your log data in master sheet start from column A:A (which contains the
id's), then in the port sheet you can use the formula:
=VLOOKUP(A2, 'Sheet1'!A:F, number, 0)
Change the name Sheet1 to whatever the name of your sheet. The 3rd
argument, number, is the number of the column in the source list. Thus,
if diameter is in column B:B, number is 2 (2nd column).

Does this help?
Kostis Vezerides

Newtonboy,

You could approach this with vlookup or index & match to retrieve the values
based on your unique identifier.

For vlookup --> http://www.contextures.com/xlFunctions02.html
For index & match --> http://www.contextures.com/xlFunctions03.html

If you need help figuring in this stuff out don't hesitate to ask.

ROCK ON!

Scott



"Newtonboy" wrote:

>
> Dear All,
>
> I am not the best hand @ Excel, so would like your help on this.
>
> The situation is something like this....
>
> I deal in timber. I receive logs in my log park. When ever I receive a
> log in my log park, each log is given a unique number and its details
> liek the diameter, length, speies, volume etc. are noted down. All
> these dataare then entered into Excel and maintained there in simple
> Excel sheet, columnwise.
>
> Like this i accumulate thousands of logs. The problem ocurs when i ship
> out these logs. The logs are not stacked in any order. They are just
> stacked randomly. So, out of thousands of logs, as soon as I have a
> shipment, I have to move hundreds of logs into the port.
>
> Now, I have to prepare a packing list of all the logs that I have put
> in the port. The problem is this paking list.
>
> What I do now is to have two sheets, one is my master excel sheet whih
> has details of all the logs and the other one is just the list of logs
> that I have moved to the port.So i just copy and paste the list of logs
> from the port list and paste in my master list, then sort and then
> painstakingly, match each log to log.
>
> What i wanted to know is, if theres a better way of organising the
> data, so that I just enter the list of logs I have moved into the port
> and i get all the details of the logs like the dia, length, volume
> etc.
>
> Or is Access a better way of managikng this data ? I use excel because
> it is easier to manage with and my staff, who are not too familiar with
> computers have grwon familiar with excel over time due to thsi data
> entry trhat we do.
>
> Pls suggest a better way of managing data. Looking forward to hearing
> from you.
>
> Regards,
> Ajit
>
>
> --
> Newtonboy
> ------------------------------------------------------------------------
> Newtonboy's Profile: http://www.excelforum.com/member.php...o&userid=29582
> View this thread: http://www.excelforum.com/showthread...hreadid=492821
>
>

Sounds like a case for the VLOOKUP Function.

Assuming Master sheet is where you have 5000 logs listed with their data in
columns A:F, Column A having the ID numbers.

Assuming you have 500 of these logs listed by ID on the Port sheet in Column
A.

In B1 enter =VLOOKUP(A1,Master!$A$1:$F$5000,2,FALSE)

Copy this across to F1 changing the ,2 to ,3 to ,4 to ,5 as you go across.

Now drag/copy those down columns B:F for 500 rows.


Gord Dibben Excel MVP


On Mon, 12 Dec 2005 13:21:36 -0600, Newtonboy
<[email protected]> wrote:

>
>Dear All,
>
>I am not the best hand @ Excel, so would like your help on this.
>
>The situation is something like this....
>
>I deal in timber. I receive logs in my log park. When ever I receive a
>log in my log park, each log is given a unique number and its details
>liek the diameter, length, speies, volume etc. are noted down. All
>these dataare then entered into Excel and maintained there in simple
>Excel sheet, columnwise.
>
>Like this i accumulate thousands of logs. The problem ocurs when i ship
>out these logs. The logs are not stacked in any order. They are just
>stacked randomly. So, out of thousands of logs, as soon as I have a
>shipment, I have to move hundreds of logs into the port.
>
>Now, I have to prepare a packing list of all the logs that I have put
>in the port. The problem is this paking list.
>
>What I do now is to have two sheets, one is my master excel sheet whih
>has details of all the logs and the other one is just the list of logs
>that I have moved to the port.So i just copy and paste the list of logs
>from the port list and paste in my master list, then sort and then
>painstakingly, match each log to log.
>
>What i wanted to know is, if theres a better way of organising the
>data, so that I just enter the list of logs I have moved into the port
>and i get all the details of the logs like the dia, length, volume
>etc.
>
>Or is Access a better way of managikng this data ? I use excel because
>it is easier to manage with and my staff, who are not too familiar with
>computers have grwon familiar with excel over time due to thsi data
>entry trhat we do.
>
>Pls suggest a better way of managing data. Looking forward to hearing
>from you.
>
>Regards,
>Ajit

Thanks a lot Kostis, Gord & Scott. The VLOOKUP thing gave me hope coz it pulls up data for the next 2 consecutive columns, but for the rest of the columns its comes up with #N/A.

I am not sure as to why this is hapenning. I did look into a web tutorial for vlookup and it was excatly as you guys had mentioned. But, still no go !

I am attaching a sample master sheet of 200 logs from one supplier. PLs havea look and tell me what I am doing wrong. IN the attached excel sheet, GRN No. is the Goods Receipt Note No.

Thanks a lot for all the help u guys are giving me.

Cheers,
Ajit

Hi Newtonboy,

You probably have a solution to this problem now - but in case you don't, here's my suggestion with an attached example using your file.

"=VLOOKUP($A9,'Pine Master Sheet'!$A$9:$V$210,COLUMN(B9),FALSE)"
After being modified for your file, this formula can be copied down as many rows & across as many columns as required.
(See the attached file for more explanation)

The above corrects a slight error in Gord's suggested formula & should stop the "#N/A" problem by adding a dollar sign in front of the lookup reference, ie "$A9" as above. This forces the formula to look up the log id from column A no matter which column the formula is in.
I have also made another change to make it easier for copying the formula across the columns by using the "column" function as shown above. This stops the need for changing the column reference in the vlookup formula when it is copied across the range of columns.

Hth,
Rob Brockett
NZ