Microsoft Excel Barcode Scanning

***FIRST I APOLOGIZE BUT I DID HAVE A COLD WHEN PUTTING THIS TOGETHER***

My partner and I are putting together more applications that have been a huge success at our company. All using excel with powerful VB scripting. We have put together an entire shipping and receiving barcoding that tracks the skids of up to 30 jobs at one time. The goal is simple....give tools to companies so they don't get ripped off by hundreds of thousands of dollars in software. Bottom line custom applications allow companies to innovate, create powerful IT departments, and customize what works for you so your software works for you and not the other way around. I'm simply a frustrated entrepreneur sick of getting ripped off by paying for trumped up software promises.

FEATURES INCLUDE

-Barcode scanning using smart phone ($7.99 app through CLZ Barry)
-Wireless transmission into excel application with the dump of barcodes and qties
-Compare features that compare pick lists to job numbers to assure proper pulls.
-Inventory management with mins and max's and automatic order points

Companies pay tens of thousands of dollars to utilize the features this free excel application can do

My Installation/Service Dept is approx a 4-5 million dollar per year dept. Installation inventory has always been very difficult to manage as it is separated from our central inventory system. This dept is now the first dept to implement barcode scanning. It has some evolving to do for sure as I would like to get away from using pivot tables and utilize more of the VB code as opposed to formulas. Also would like to create an external database to house inventory items....that said this application single handed created the most successful dept in our company what we call ISS (Installation Support Services).

BARCODE SCANNING IS DONE WITH AN AMAZING APP CALLED CLZ BARRY. This app just brings the raw barcode in and then sends the barcodes to the excel application wireless. Truly amazing what you can do these days with Microsoft Excel!!!

For a more in depth on how I put the code/formulas together to do this will call for another more technical video. This was created for internal purposes so if it generates some interest....I'll do more videos for folks who want to take this to their own company/departments. Also great and very very easy if someone wanted to use for DVD, Pantry, Video game, etc management.

---

Closed Caption:

so now the ISS bar code database adding
things into the master database and how
to adjust your inventories and to trust
your inventory counts
so first we're going to start with the
same thing that we did on the on the
asset database and that is taking a look
at the pivot table which just for
understanding of how that pivot table
works and also to get a captured on
video so that we can set it back up if
need be
so here we go pivot table which is a
first
unprotect while it's already objected
I'm gonna
actually set up a sheet 1 i'll rename it
right there is to it
ok so how this pivot table basically
works is very simple
it's just a series of looking at the
scanned page and taking a total of all
the ins and all the out some of the ends
and sum of all the outs
based on the actual ISS number so that's
all that's doing
I got my is number and out
I says numbers over here some in some
out and values
so this is how this looks this needs at
any time there's a pivot table it needs
to be refreshed that refresh is up here
and how that works
hide this bag so it's no longer seen but
just know that it is a it's there
so if via will get back into the
inventory but for right now i am going
to just give a brief
rundown on the undie scan page in
general
so the way this works is there's a
master database this master database is
what contains all the information that
gets pulled over when a chase
sku number is placed into this field
so let's play some of this field that
pulls everything that's in that row over
that's in this master database so I for
example i'm just going to put this one's
blank 11,000 is blank so I'm just going
to put a test scan all right so on the
scanning page I'm going to do a noose
can I want to get something to control
and which is going to pretty much filter
everything up
i'm going to press return to go over
here is 11,000 and sure enough tests can
is what pulls up and that's 45 that
location a 4-under number test under
this is a formula based going to promote
and protect the sheet so you can see a 4
comes over army service and everything
that gets pulled over when I take that
number and it will disappear when I so
there we go
I know that you know how that basic
function works as far as the scanning
how it relates to the master database
let's go ahead and clean up some of this
master database
and when we're talking about cleaning up
what I what I do here is uh
I actually turn off the formulas so that
it works faster
so i'm going to go ahead and do that on
the master database page I'm going to
go to formulas up here and i'm going to
switch it over into manual
I usually takes a little bit for it to
really kick in as I push
delete uh no actually i was working
pretty fast now so I can
so what i'm doing here is 11,000
anything that was last year I designated
some of these two to the 11,000 just a
few items inside this database where I
just needed to track it for a little bit
wasn't something that we actually gave a
permanent location to as you can see
that a lot of these don't have a permit
applications if they do i'll leave it
so what I'm going and I I does it gave
the department are amazing service so
i'm going to go ahead and delete a lot
of these out just clean some of this
stuff doesn't have a location and I
remember these are just things that need
temporary spots for so i could track it
when he came to I came in here and and
basically it will
I've already anything that you can
pretty much do you have free reign over
this the only thing that has formulas is
this inventory right here when you leave
the ISS numbers along
other than that you pretty much just
follow my lead on this them taking this
out
leaving this in here because it's got
locations this is coming out of this is
coming out and because i don't have a
filter on them and how I know I don't
have a filter is how is all these arrows
are are good this is this means it's
sorted but it doesn't have a filter so
I can I can do more
long range of clearing of cells and why
I bring that up is that is if this was a
let 1102 old and it's 22
you look over here is his 22 if this was
filtered in the next one said 50 and I
went down and then a drop down here for
20 to 50 and clear the cells or copied
and pasted it's going to do that from 20
to 30 50 is going to do everything in
between
that's why I don't like copying and
pasting are clearing things by pulling
and dragging but in this case when there
is no filter everything is in line
you can do a little bit more of a
broader pull down
clear multiple cell Rose is another one
here it
so this is kind of software maintenance
if you will
he's got locations so i'll leave home
because i dunno i do remember Josh
actually going through here I'm gonna
get the Department of controls these are
like some extra stuff that was sitting
in inventory that they didn't need but
it's like control stuff that we're just
housing in ISS remember doing that and
Josh gave it a location and you get my
SS numbers spell the electrical look so
nice copy and paste
so I changed all these out controls
i'm also going to I don't need this RNA
service and i'm going to a broad
sweeping with us and instead of copying
and pasting I'm just gonna
I just want to replace army service to
with the with miscellaneous install
miscellaneous and install columns find
what our way service replaced with this
install replace all going to unprotect
the sheet place all should be quite a
bit of home 521 to be exact
it's saying that there was some
protected cells in there
I'm going to check that out sometimes it
does that even though they're not
protected
check it out now
format cells they're all unprotected so
not quite sure right to that and i'll
copy and paste this in here
good showing
miscellaneous install
I'll tell you what I'm doing this here
in a second i'm going to keep going with
it all the way down start on the next
available on go down up to controls and
even passed it covers more blanks and
icing
start there and go down to although we
don't even through Gaylord
oops pics
ok I'm going to clear these gay Lords
out because we now go through shipping
and receiving to get the gaylord so
there's no we're actually not using
these at all so there's no sense of all
right so now we have a large number of
cell database that we can use with
whatever and i'll explain that in a
minute
all right so what do I mean by we can
use it for whatever
what I mean is that like i showed before
the test email you know if I put this in
here 11,000 and I put 11,000 here not
going to do anything now just so you
know because i have my formulas turned
off so they go to automatic it switches
back over and the air comes not testing
that should tests can handle America's
Test can
so what this could be used for is for
that special requests by Terry let's say
that one of Terry's request is some kind
of half inch blue aluminum 90 degree
I'm fitting you don't necessarily know
what it is but you just want to get it
in here
maybe you want to do an alt return just
so you can have another line item in
there and put requested requested by
Terry Berg
you know what whatever you want to put
in there to help you understand what
what you're doing there and then
so now you actually give it a partner
11,000 all right
it's under miscellaneous install and
what that does is if you when you do
your compares this is like the breakdown
of the I will
why the benefits of such a application
like this is when you do your compares
and I want to compare is that and the
install guys do the list when they
create their list of what it is that
they want what you find when you click
on compared to I'm not hyperlinked there
right now because it's from the ISS on
command center computer and and I'm not
linked to that right now but with that
basically pulls up though it's just a
link to this
from dropbox folder under is s
department and is bar coding and compare
scans and jobs so it's basically like
this is what you will see when you click
this from the ISS computer click compare
to what brings you to this and then you
are a pole and then you click on
whatever job that you are you want to
compare to this one is an investor job
so it's opening it's telling me this
because I'm hoping it from a different
computer and single they're my links or
whatever I must come out that update if
you notice
well this one
they all have part numbers but sometimes
somebody put a special request in here
and you'll see like four or five things
in here that don't have part numbers
the way that they they do it in ISS and
and see if I can pull out and that
actually has maybe first data has one
going to update
yes first day - does a lot of these
didn't have part numbers and some
special requests right here I can tell
the way that they're doing now is
because they don't have a part number
it can it'll it'll always be pink all
the ones that that have initials here
are pink
these initials are basically telling us
hey somebody took care of it but it's
not comparing it to anything because it
doesn't have a part number to compare it
to it was never actually scanned out on
this page with an actual part number so
we had to have in this case Julius put
his initials there just to let us know
that hey it's pink and pink is always
pink is just something that tells us
when we went to our compare when we run
our compare feature of this one is good
because it ran a comparison yet by the
quantity that was requested was too and
guess what - was scanned out when it
looked through the scam page and
basically what it's doing is looking
through this page
it's looking at it's looking at this
so basically what this does is you put
the job number here and you push you
know this button it populates the field
of like 60,000 cells and within those
sixty thousand cells then it just
basically it pulls in
it looks for this job number and then
one by one line by line it pulls in
everything that
that's within this database that has
that it has that job number and then it
does a whole bunch of comparing that
goes on so anything pink is basically
saying hey I got 25 that was requested
and nothing scanned out to this job
number you're still 25 short
so once you give it a part number it
compares it to have something to compare
to not only that there's close this out
so that's that's the one reason why you
want to give any special requests a a
part number right away but two is that
eventually
let's say that you do order these these
fittings and it's a month before the job
starts
and you want to put it somewhere where
we're going to put this stuff I you know
we haven't started building a gaylord
yeah captain
well here at least now as long as you
have it in here you can put it in a 5 on
the shelf on under a five and and then
one attempt comes time to do your
compare and actually when he'd run your
compare and actually scan
you know you pull your compare up and
then it gives us part number of 11,000
and then you scan it out 11,000 you're
like I don't even know what this thing
is
as soon as you scan and oh yeah that's
that thing for Terry that area question
i was sitting over a five and and then
also any other information here maybe
there's a vendor number that you finally
maybe its final you know as you go
a week later you is you start looking
into this will work i get this you find
a lender we get the cost of a $13 sixty
cents per box and under Department see
how these are all under missile you can
keep it under miscellaneous install or
maybe you want to give it a different
apartment name
just because you're not gonna want to
give it like air fittings or anything
like that not until we know it's
something we want to keep in a
continuous stock but maybe you want to
give it up
the actual job number which before I do
that I want to make sure that it's set
to on general
ok maybe you want to get the you know
560 85
so just something to help you identify
what was this thing on the compare shoot
is 11,000 i forgot what that was
as soon as you put that number in the
scan 11,000 mom again I gotta turn on
formulas back on as soon as I do that
now here I was all the information
department 56 oh that's for Terry's job
560 85 was requested by tier 8 sitting
over at a five ok that's what that was
all right
ok I'll i know exactly what it is now so
that's what this is for
that's what all these these blank cells
are our forces to populate with whatever
that's what i mean by whatever so these
are our blank remain blank and once if
we if we did get it to a point where we
want to actually give it an actual
install spot we can remove it from
11,000 and move it to electrical
accessories or maybe it's a mechanical
success for you maybe it's an air
fitting
whatever the case is and you can just
move it over or you just leave it in
there and after six months we go through
kind of like what i did now after a year
you just delete it because it's no
longer there was only something I need
to temporarily and we use that number
but that's that's what we did here
very uh very helpful and it's something
necessary when we get a special request
from installation
ok also and it's also something if it's
a they were helping Tiffany out or
whatever the cases and something has a
hill mount part number
at least it's something we get to our
database give it a hill top part number
anyway of put it on the
instead of getting initials on the you
know and as soon as we do that instead
of giving initials like what Julius did
I actually give it the 11,000 part
number or whatever number that was and
now all the information is there an
outside help my part number maybe it's a
certain gear ratio roller whatever it is
but now at least it's captured when you
pull up this compares giving you a
number of 11,000 to to do a scan out and
natural compare features anyway
and again from the compare just so you
know you can only change those in the
database if I try changing the callous
and sometimes get confused there's a
reminder look that's protected
it's through a database we make those
changes to close out that now that said
i want to show use another feature
that's very high clean this up
basically I i took the last to know from
the very start at 23,000 scans from the
time we started this so i took basically
everything up to the month of May and
and put it into its own little database
and now you know so basically this scan
page is only up through to her
start dating and may first so we have
950 scans
so it's almost like a fresh start
let's talk about inventory
the one of the features that is on this
is is an inventory control that is very
Bob
very friendly as far as if it's done
right you can do a lot of things with
how we do our inventory in fact I
actually have our inventory to screen
out our mins and maxes from an actual
inventory page like compare page that we
have and i'll go through that once I am
set up at the command center pc because
i need to have that link to do so but
what-what were
let's go through the actual inventory so
right now the way that isn't a scroll
down through here you're going to see
some inventory numbers on here
these inventory numbers like a hundred
twelve 8516 these are not something i
added in
but basically it's it's automatically
calculating all the ins and outs and
it's doing that through that pivot table
that i was talking about it
it's taking all the ins and outs for you
know the ins for 160 20 and subtracting
the outside and coming up with this
number 47
so let's look at one that's a little bit
lower 1-6 of 6 that has eight of them
alright so if i go to the scan page and
I filter out 16 / 06
so we are now we're at 1600 six and the
inventory amount the an inventory is at
eight
because this column right here at bank
is telling me the inventory so basically
what it did is it said okay at some
point 16
you know the first scan that ever hit
the Scan sheet with 16 and then 10 more
skills that was scanned in see that 10
were scanned out which leaves 66 were
scanned out which leaves 08 lower scan
back in which leaves eight
that's the number you have right here so
you can see you're 16 span in tents can
out to five 72 and 76 or scan out again
for this one and eight scan back in from
5-7 265 so we have eight in stock
according to just the scan control and
the
the way to speed the keep the all the
tables calculating correctly
this is done with a refresh future which
is up here on this deal right here so as
soon as I push refresh you do have to
wait like maybe 10 seconds for all the
numbers to go through and refresh
if we did a automatic consistent
inventory check your table of 11 very
very very slow
so that's the reason why you have to
refresh
so let's do a control and I just give it
a little bit of us
test then and demo so 1600 six it
it's saying just based on your scanning
in a scanning out you have a double but
let's say I actually have 10
well if you go over here and you
actually are you know this is adding or
subtracting so say that i actually had
for so if i have four then instead of
putting in a positive number put in a
ticket you know
negative 4 so it's actually going on
starting to look for maybe I actually
have zero and negative 8
whatever it is you know this is where
you refresh which is refresh inventory
so if you have somebody come back to you
at the end of the day and say hey you
got 12 these 16 all sixes i'm going to
have four in here and call and and now
it's starting at 12
so now i'm going to go to scanning and
I'm a type in 1646 as if i'm scanning it
out and you can kind of watch the
inventory be adjusted as we go
well if I don't have an X in check in
its by default going to go to check out
so if i go to 10 here
it's it's automatically going to out
unless I put an X and check in so it's
automatically going to help
but you can see that the inventory
didn't adjust still at 12 should be at
two right
well because I you know you're twelve an
inventory and i just checked out 10
remember we have to refresh the
inventory so as soon as i refresh the
inventory you wait like 10 seconds and
we'll see your inventory get adjusted
from 12-2
and there's your two and if we were to
check 1600 64 home in and then push your
inventory refresh
and then obviously you're going to see
your six
yeah
so there you go so now if you don't have
six and you've got to go back to master
database and ok listen i don't actually
have six
now remember and you already had a 4
here as soon as I take away that for its
going to bring me back to to let's say I
actually had 10 are but obviously it's
covering my back to so i'm going to
change this for enough to a i'm going to
change it to an eight but whatever it is
it's a place where you can actually
refresh your inventory overwrite this
number right there
and it doesn't give you negative values
I have it to where it stops at zero so
if I if I check out arm
right now I got to an inventory and I go
16 or six and i'm checking out another
10
it's not going to my refresh inventory
it's just going to take me down to zero
this is much harder trying to manage all
the negative all the negative numbers so
i just had it stopped at zero as opposed
to you know negative 8
so just just a rundown of how that
inventory works and
very very useful feature that I know we
can start taking advantage of right away
and if and I think actually i might be
able to
let's try it so there is a inventory
since pdfs compared and quarters
you have activated
I thought there was a hyperlink to an
inventory page
in fact it's not a hyperlink it's the
it's a macro so my macros it's a
inventory check
it's not control
there was a control I you know
this picture in trouble our options
there is an inventory check that's it
oops
ok
their control
all the small I
yeah
brings it up
and the reason why wont is because it's
trying to do it from the ISS computer
all right ignore all that i'll just
bring it up manually
so what control I does is it actually
opens up a system lazy persons we are
doing something but if i go to ISS
department is barcoding inventory data
is basically what it is at cycle
inventory data
it's
it's basically I all right
I don't think that so if you look at all
these Reds how my inventory is supposed
to work and it can use some tweaks sure
it gives you your inventory college but
it also sets this because of based off
your meds and Max's so
it's assuming that everything that
doesn't see a zero
that doesn't see I mean I a number here
that it's at zero and you need to order
because 16 all one steal flex has a 200
minimum to it
so let me go to actually maybe a 200
maximum i think we order to the maximum
and where we see that well based on
names of max if i go to 1600 one trip
i'm sitting over 100k need to see here
it's actually orders to the max so if it
gets below a hundred it orders to a max
of two hundred the way it's set up so if
I was to do a hundred one
my inventory here you can see the
inventory adjusted
ok
so as I was showing you i did have to
adjust something in the formula to
actually show negative numbers just be
way too complicated to do it any other
way
so what this is doing is basically
showing what I've done a test with
sequins 6001 behind the scenes created a
series of three scans 10 out our which
is
showing me 47 but I didn't do a we
should let me take this out this was the
test i did I clear this out
I'm clear this out
go back to master and I'm going to go
and do a refresh
I think you're going to believe you see
it at negative 17 i believe was all said
and done
ok so it's at negative 17 so we were
looking at our inventory data is showing
me all these negatives which is telling
me to to order to my maximum never hear
my maximum at 205 if i set my max - 400
when I go back to that inventory sheets
gonna say or 417
it'sit's ordering to my max but if i go
back to go back over here and put it to
my minimum to negative 20
I'm you're going to see that it's going
to disappear
see it's not ordering anything any marks
matter of my minimum point is that
negative 20
well I'm not quite there yet so I'm
going to put this back - 200
and if I readjust that my my account I
remember readjusting now there is some
math it's going to go along if I
actually have 10 in my inventory
well then obviously i'm gonna have to do
27 because I go to a conference and we
have 10 in my inventory which you know
if I have a hundred if I want to get to
my minimum here
it was 100 200 when i get to my minimum
I have to actually do a hundred and
eighteen i want to be a hundred and one
mom to show you and now I go to my
inventory page and if this is a in my
reorder point disappears because i'm
still not at my minimum yeah so what I'm
basically showing it was always in red
here automatically calculated to be
adjusted based off my men and my men and
max I don't have a minute Mac set that's
not going to do anything and actually i
believe let's go - 250 -
ok
ok i do have a min and Max there if I
don't have a master's go to school too
curious and going to this 16 17 1
an inventory there
going to see let's see the links with
like
16 old six and i probably don't have
either one of two things that are a
little maximum sent
6
yeah
oh I got eight
how
part
oh this is 1 600 600 i met my max so
1606 i'm actually at my max trying to
find something that doesn't have a man
max that that actually has all right 132
all three
- that looks like I'm 323
yeah
yeah one of these so it doesn't have a
min and Max set it doesn't give you a
reorder point
so you can utilize your your
pre-orders & filter out your blanks and
fish is done correctly you can
base here
reorders off of your men's max so it
should be in it
I believe that's the way we can get it's
just a matter of adjusting your
inventories correctly and as far as your
scans are concerned you are you will be
able to trust your arm your scan points
are pivot table gives a very accurate in
and out so there's no reason why we
can't adjust
so if you're going through
once you do your inventory checks daily
or weekly your daily La inventory check
where you go through certain departments
your wire and so on and so forth
you are continuously update your
inventory just by adjust your adjustment
eventually your reordering once you pull
up your inventory page should be
automatic
and you don't have to and it's got
everything she needs in here should at
least for ordering accept a lot part
numbers but those get ya
so yeah that's this is how how
inventories and close out
save it as far as your forearm Hydra
pivot
and i did add this in here to kind of do
all the calculations for me
for your pivot table which is basically
subtracting your ins and outs space down
here and then again i do a lookup and
the master database in inventory is to
look up and I look at that pivot table
and I pull
I pull this right here
which is basically the difference
between this subtracted by that
okay that should be it
and over and out
I
more thing real quick is going to be
there's another tab on here called your
inventory sheet and this is basically
what can be filled out
I believe it's printed can be printed as
a your standard letter r you can do 11 x
17 if you want more room for notes and
such i have it
page numbered and but basically if you
make any
I don't care if you add just temporary
things like we talked about earlier and
you don't update your inventory sheet
this is just going to be basically if
you make a change and add something into
a constant inventory where it has a
location that is where we need to make
that change on your inventory sheet as
well
so that is basically what I did here is
I i made it to where i copied and and
pasted everything that's in this
database and that's relevant to do an
inventory check
maybe some maybe some of it still maybe
I'm missing something or maybe I have
something here that doesn't need to be
in there but for the most part I did it
so that you're filtering can go faster
there's no formulas in it you can sort
it you can obviously you can't sort of
the master database you can sort based
on fittings here is easy you can sort by
location data and also that you can kind
of just go through and you scroll
through your a's and all the way through
your locations on and then for printing
should be very easy to print it's a
daily
once you get set up that hey listen uh
we're going to do
air fittings brackets consumables and
here's where you do your inventory
checks and you print this out
there's 11 pages of stuff for them to go
through and then put their name and
their date is there reorder that they
see that needs to be in here and any
notes
I'm and in those notes i think that one
of the things that can put to maybe
build on this is going to be the actual
quantity
you know that's that's there
I'm you know spot checking some mom the
inventory
I think there could be a constant task
for anybody who comes back maybe that's
done separate but you know you just want
to just do a spot check on wire and and
then you go to print this and how
there's like three pages and
this whole thing could be just a not
necessarily for reorder of a quantity we
want to put our in fact i'm going to
change then the in to reorder
notebooks and for actual party
ok
ok
here , our room
special notes and or quantity
you might want to do once is for air
fittings a print this out and you give
it over to one of the guys are coming
back over by ISS to do some work and I
look for something for them to do here
go check the air fittings and I want you
to put down the actual quantities inside
the notes
put your name your date and then when
they're done there should be a basket
where they put that and then before
anything else is done
if they're actually doing this for an
inventory we need to make sure to
control the air fittings then and then
this needs to be plugged in and the math
needs to be done immediately to the
actual master database to plug in the
Refresh counts
I and and the way how you would want to
do that is is buy it actually turning
the calculations to manual again so you
can do it quickly and selecting air
fittings
ok
yeah
yeah
ok so i had to stop the recording for a
while but what you're not going to want
to do what I just did here and filter
even with the calculations off filter
and just takes forever but basically
what you would do is go find your
fittings column and with that piece of
paper now that's handwritten
from this sheet right here we had
written out by somebody you would have
to go through an update
so if they had 20 20 air fittings and I
heard 200 feet of poly - are you would
actually have to put on
432 in here to make that two hundred
that you're looking for and I do have
the calculations off so once i go to
automatic you have to go 24 and 32 if
there was actually 576 here then you
just change that our server hundred you
have to change your adjustments here
keep them keep those refresh counts like
this and then as long as your scans are
ins and out you know you can trust your
state you're doing your job and scanning
these numbers will be accurate
so that is that's it thank you very much