Stock taking using excel

Stock taking in library is a time consuming process. Here is a simple way for carry out stock verification in the libraries where Koha ILMS is using.

1.  Run the Accession Register report in koha

   SQL Query for Accession Register report. Copy the following text and paste in the SQL report box in koha

 SELECT items.barcode,items.dateaccessioned,items.itemcallnumber,biblioitems.isbn,biblio.author,biblio.title,biblioitems.pages,biblioitems.publishercode,biblioitems.place,biblio.copyrightdate,items.price
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
ORDER BY items.barcode ASC

2. Download the report and save the file in excel compatible format. i.e  (.xlsx)

3. Copy the accession numbers taken during the stock verification in a different sheet of the same excel file of koha accession register report.

4. Rename the sheet2 of excel file as "sub" and sheet1 as "Main".

5. Go to sheet1 and insert a blank column just right to the column of  Accession number

6. Apply the following formula in second row of the column and drag the mouse to the end of the column

                         =VLOOKUP(A1,$sub.A:A,1,0) 

7. Now the blank column show appropriate missing numbers

Note: Make sure that the number in stored as 'numeric' in both koha report and value entered during stock verification