KEEPING COUNT

[Mondou]

Don't know if this is possible but here goes:

I'm trying to take inventory of products with a Bar code scanner which
returns a numerical code in a cell once an item is scanned. When I
scan a second item, a new code appears in the cell below and so on.
eg.

BAR CODE
1001
1025
1535
1001
1003
1025
1001
....

Problem is, I end up with a long list of products in my column, and
MANY repeat themselves.

Is there a formula or a code that could add up and generate the total
number of identical codes to reduce the size of the column ?

eg.
BAR CODE QTY
1001 3
1025 2
1535 1
1003 1
....

I don't have a problem generating this on a different Sheet.

... I'd prefer not to have to sort the column and add them afterward

Any help would be appreciated

 

[Jordon]

Don't know if this is possible but here goes:

I'm trying to take inventory of products with a Bar code scanner which
returns a numerical code in a cell once an item is scanned. When I
scan a second item, a new code appears in the cell below and so on.
eg.

BAR CODE
1001
1025
1535
1001
1003
1025
1001
...

Problem is, I end up with a long list of products in my column, and
MANY repeat themselves.

Is there a formula or a code that could add up and generate the total
number of identical codes to reduce the size of the column ?

Pivot table.

 

[Mondou]

Pivot table.

Thanks Jordon, but is there a way to automatically refresh the pivot
table every time a new product is scanned ?

 

[Jordon]

Thanks Jordon, but is there a way to automatically refresh the pivot
table every time a new product is scanned ?

Maybe with some code but I'm horrible with VB. I have a few spreadsheets
similar to what you're trying to do and I record a macro that creates
the pivot table and place a button that runs the macro near the top of
the sheet.

 

[Mondou]

Thanks Jordon, but is there a way to automatically refresh the pivot
table every time a new product is scanned ?

I've figured out how to refresh by using a macro button.

However, I have a new problem:
When I scan the bar code, the numeric value appears in my Data sheet
in column A and, in order to be able to add each scanned code as 1,
I've inserted a formula (*) in column B that shows a value of 1 if
column A is greater than 0 and a blank value if not ... I've copied
this down:

eg.
(data sheet)
UPC PRODUCT QTY
100000 PT-1030-02-T21 1
100001 PT-1032-02 1
100000 PT-1030-02-T21 1
100000 PT-1030-02-T21 1
100001 PT-1032-02 1
100002 PT-1134-07 1

.... 13 more blank rows


When I refresh the pivot table, it adds all of the data correctly but
it also adds a ''(blank)'' row with a total of blank fields

eg.
(pivot table)
Count of QTY
UPC Total
100000 3
100001 2
100002 1
(blank) 13
Grand Total 19

My true total is really 6 and not 19.
The only way I can get rid of this row is by deleting my formula (*)
in my data sheet ... but then how do I get the pivot table to add my
scanned products.

So:
A) is there a way I could make my pivot table recognize my UPC code as
1 ? ... this way the formula will no longer be an issue
or
2) Is there a way to remove this ''(blank)'' row from the pivot
table ?

 

[CellShocked]

Maybe with some code but I'm horrible with VB. I have a few spreadsheets
similar to what you're trying to do and I record a macro that creates
the pivot table and place a button that runs the macro near the top of
the sheet.

The scanner is currently set to add a carriage return after the scan
completes. You could just as easily have it add a control character that
incites the excel code to loop or continue.

You could also have it do it in steps where you click "OK" or the like
on a button in a dialog box that you pop up in the code.

 

[CellShocked]

I've figured out how to refresh by using a macro button.

However, I have a new problem:
When I scan the bar code, the numeric value appears in my Data sheet
in column A and, in order to be able to add each scanned code as 1,
I've inserted a formula (*) in column B that shows a value of 1 if
column A is greater than 0 and a blank value if not ... I've copied
this down:

eg.
(data sheet)
UPC PRODUCT QTY
100000 PT-1030-02-T21 1
100001 PT-1032-02 1
100000 PT-1030-02-T21 1
100000 PT-1030-02-T21 1
100001 PT-1032-02 1
100002 PT-1134-07 1

... 13 more blank rows


When I refresh the pivot table, it adds all of the data correctly but
it also adds a ''(blank)'' row with a total of blank fields

So turn it off. It has a drop down button that opens a dialog that
allows you to select which figures are tallied and shown in the result
table.

eg.
(pivot table)
Count of QTY
UPC Total
100000 3
100001 2
100002 1
(blank) 13
Grand Total 19

My true total is really 6 and not 19.

So, you turn it off.

The only way I can get rid of this row is by deleting my formula (*)
in my data sheet ...

No, just turn it off in the pivot table.

but then how do I get the pivot table to add my
scanned products.

So:
A) is there a way I could make my pivot table recognize my UPC code as
1 ? ... this way the formula will no longer be an issue
or

You should be able to concoct a formula that does NOT add blank lines
or values or tallies.

2) Is there a way to remove this ''(blank)'' row from the pivot
table ?

Yes, the table "column" header should have a drop down button that will
let you turn it off.