+ Reply to Thread
Results 1 to 8 of 8

Product sell with barcode reader using timestamp sorted per day

  1. #1
    Registered User
    Join Date
    11-08-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    10

    Product sell with barcode reader using timestamp sorted per day

    Hello I would like to ask if someone can help me, I want to create a excel bar-hotel inventory with the help of barcode reader, when we sell a drink it gives me a barcode number and next to it timestamp, on right side columns I get unique products, all I need is to extract every product sold every single day for all year possibly unique products too, you can see everything in the image below. Can somebody help me? I can also publish excel file if interested

    1 ----A ----------- B-------- C
    2 barcode01 28-01-15 Caffe espresso
    3 barcode01 28-01-15 Caffe espresso
    4 barcode01 28-01-15 Caffe espresso
    5 barcode02 28-01-15 Caffe espresso
    6 barcode02 28-01-15 Caffe espresso
    7 barcode01 29-01-15 Caffe espresso
    8 barcode01 29-01-15 Caffe espresso
    9 barcode03 29-01-15 Caffe espresso
    10 barcode03 30-01-15 Caffe espresso
    11 barcode03 30-01-15 Caffe espresso


    I would like to get this:


    1 ----D--------------- E -----------F ----------G ------- ---- H -------- I ----------
    2 28-01-15 number of products 29-01-15 number of products 30-01-15 number of products
    3 barcode01 -----3 ----------barcode01 ----- 2 --------- barcode03 -------- 2
    4 barcode02 ----- 2 ----------barcode03 ----- 1


    I would like to get names and number of products sold every day (28-01-15, 29-01-15, 30-01-15)
    What I don't know is how to get unique products per every day when the barcode is read with criteria (28-01-15 ecc.)

    so can you pls. somebody help me to know what function to put in D3 E3 F3 G3 ecc


    barcodeexcel.jpg

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Product sell with barcode reader using timestamp sorted per day

    Upload a small worksheet please
    Quang PT

  3. #3
    Registered User
    Join Date
    11-08-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Product sell with barcode reader using timestamp sorted per day

    thank you bebo for Your respond, here is the file , the part I am interested in is the SCARICO sheet, and K3:P100 ecc, http://4ella.com/tour/GennaioJetHotel.xlsx

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Product sell with barcode reader using timestamp sorted per day

    In K2 the drag down:

    Please Login or Register  to view this content.
    You used NOW() in B2 column to get the date. It is better to use TODAY().
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-08-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Product sell with barcode reader using timestamp sorted per day

    Bebo thank you very much for your time, I was trying to make it work but it gives me an error, can You pls. add that function into correct cells in attacched file for me? And what about L2? There are 2 functions necessary, one for unique products (or barcodes or names of product) per day and the other one for number telling me how many products (how many caffe espresso) has been sold on 29-january-15 and how many on 29-january-15

    example
    K2 ----------------L2 --------------- M2 ----------------N2
    ----------28-january-15------------------29-january-15------------
    caffe espresso------------- 8 ----------caffe americano-----------4
    caffe americano------------3---------------------------------------
    Last edited by danfolt; 01-29-2015 at 08:02 AM.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Product sell with barcode reader using timestamp sorted per day

    See attachment

    It is oK, no errors found
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-08-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Product sell with barcode reader using timestamp sorted per day

    Bebo You are absolutely the best !! Thank You very very much, it works perfectly, shall I edit the post with "solved" prefix ?

  8. #8
    Registered User
    Join Date
    11-08-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Product sell with barcode reader using timestamp sorted per day

    You used NOW() in B2 column to get the date. It is better to use TODAY().
    Bebo I have a problem with formule in M1 - O1 ecc to connect them with cells in B2-B2000 where I have example if number in A is scanned give me date when it was scanned in column B
    Please Login or Register  to view this content.
    , when I insert dates in K1 , M1, O1, and so on and also everywhere (including B2-B2000) I put the same custom format gg-mmmm-aa, excel doesn't recognize it and sold products for that day are not extracted, do you have some idea why I can't get it work? When I use your file it seems to work when I don't enable to edit it, when I enable to edit, formula M1 =K1+1 doesnt work for me anymore, instead when for example 10-feb-2015 is in B15 and I direct M1 to =B15 it works , I dont know where I make a mistake, I everywhere inserted the same date or custom format, with dates I have generally a problem because I need to never change date after barcode scanning, I want to know when it was scanned, so I have also put iteration=1

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Barcode reader macro
    By StBiggles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2015, 10:35 PM
  2. Stock taking with barcode reader
    By zuzz1991 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2015, 10:11 PM
  3. Inventory control using barcode reader
    By ehtoo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-07-2014, 11:47 AM
  4. Need to specify barcode reader sheet/cell input.
    By PCSOhio in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2014, 07:22 AM
  5. barcode reader and excel problem.
    By bradbev in forum Excel General
    Replies: 1
    Last Post: 06-12-2008, 02:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1