Inventory Management

[Newtonboy]

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

 

[vezerid]

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

 

[Scott Wagner]

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

 

[Gord Dibben]

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

 

[Newtonboy]

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

I am not sure as to why this is hapenning. I did look into a we
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. PL
havea look and tell me what I am doing wrong. IN the attached exce
sheet, GRN No. is the Goods Receipt Note No.

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

Cheers,
Aji

+-------------------------------------------------------------------
|Filename: Sample.zip
|Download: http://www.excelforum.com/attachment.php?postid=4118
+-------------------------------------------------------------------

 

[broro183]

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


+-------------------------------------------------------------------+
|Filename: Sample with suggested solution.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4176 |
+-------------------------------------------------------------------+