New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Barcode Scanner to highlight rows to copy

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Barcode Scanner to highlight rows to copy

Postby kirtis_m » Tue Apr 26, 2016 10:56 pm

I work for a grocery store and Long story short I am the only store out of our 40 stores that know how to print barcode labels with the program seagull bartender software

We Have two excel files

one file that has our entire inventory and one file that's blank

Anytime we want to make Shelf Price Tags, we open up the MASTER INVENTORY EXCEL FILE and hit "CTRL F", and enter either the Order Number or the UPC NUMBER to find that item, we then highlight that item (entire excel row) and copy and paste it into the Blank Excel File

Is it possible to have a barcode scanner scan barcodes and automatically find that number in the Master Inventory Excel File and add it to the Blank Excel File automatically ? - THIS WOULD BE AWESOME, and SAVE MAJOR TIME>

If that wont work will the barcode scanner be able to scan a UPC Number and highlight that row of that item scanned and then be able to scan yet another item and keep that one highlighted as well , that way say if we scan 40 items, then all we have to do is hit ctrl c to copy all 40 items, and then paste them in the Blank Excel File for the labels to be printed using my seagull bartender software.


If I need to attached the excel files that I am working with I can .
  • 0

Last edited by kirtis_m on Tue Apr 26, 2016 11:08 pm, edited 1 time in total.
kirtis_m
Rookie
 
Posts: 5
Joined: Apr 26, 2016
Reputation: 0
Excel Version: 2016

Re: Barcode Scanner to highlight rows to copy

Postby kirtis_m » Tue Apr 26, 2016 11:04 pm

here is the master inventory produce list

Produce Labels XLSX.xlsx
  • 0

You do not have the required permissions to view the files attached to this post.
kirtis_m
Rookie
 
Posts: 5
Joined: Apr 26, 2016
Reputation: 0
Excel Version: 2016

Re: Barcode Scanner to highlight rows to copy

Postby kirtis_m » Tue Apr 26, 2016 11:05 pm

here is the blank list that we copy items from the master list over to this one to print the files.

Produce Labels Blank XLSX.xlsx
  • 0

You do not have the required permissions to view the files attached to this post.
kirtis_m
Rookie
 
Posts: 5
Joined: Apr 26, 2016
Reputation: 0
Excel Version: 2016

Re: Barcode Scanner to highlight rows to copy

Postby NoSparks » Wed Apr 27, 2016 11:51 pm

Into what cell of what sheet in what workbook are you scanning the bar code for Excel to work with ?
Is that bar code text or numeric ? Your attached workbook has the UPC and the Itemcode both as text.
Is there a particular reason for your 'Labels to print' being a separate workbook rather than a separate sheet in the same workbook ?


Have a look at these threads, perhaps you can get some ideas.
How to automate Time in & out; and detect double entries
macro for library inventory


A slight rant....
Your sheets have seventeen billion, one hundred seventy-nine million, eight hundred sixty-nine thousand, one hundred and eighty-four (17,179,869,184) cells with some kind of formatting.

Your used range is 12,400 cells.

Perhaps it doesn't matter but this increases file size and gives Excel more to 'look after' than necessary.

I doubt you'll ever use row 1,048,576 or column 16,384.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Barcode Scanner to highlight rows to copy

Postby kirtis_m » Thu Apr 28, 2016 10:40 am

I didn't knowing had that many cells. I opened up a blank spreadsheet and just saved it

Yea the cells are formatted as to work in the program bartender - a program for printing shelf tags for stores

It will take whatever number is in the excel file under upc and item code and produce a barcode for that tag

I have recently emailed bartender and they told me it's possible to scan barcodes and highlight items through their program. I just need to learn how do it
  • 0

kirtis_m
Rookie
 
Posts: 5
Joined: Apr 26, 2016
Reputation: 0
Excel Version: 2016

Re: Barcode Scanner to highlight rows to copy

Postby kirtis_m » Thu Apr 28, 2016 10:42 am

I'm wanting a use scanner to scan a product and it automatically find it in excel and then be able to scan another upc and it find that item as well and keep everything highlighted. Both items , or more

That way I can copy those highlighted cells into a different sheet and use bartender to print all of the copied and pasted items


I have to have it as text instead of numeric because it takes away the zeros at the beginning of the number if I change it to numeric. I have to keep the zeros for bartender to make my code correct
  • 0

kirtis_m
Rookie
 
Posts: 5
Joined: Apr 26, 2016
Reputation: 0
Excel Version: 2016

Re: Barcode Scanner to highlight rows to copy

Postby Sisyphus » Sun May 01, 2016 6:12 am

I took a little longer route to what you want. Let's see if you like it.
The first thing I did was to make your Main workbook's tabs visible. In the attached workbook, look at the tabs at the bottom of the application screen. You will see a slide, just between the left-arrow of the horizontal scroll bar and the tab for adding a worksheet. You can drag this slide left and right. In the workbook you posted it was pulled all the way to the left. Therefore your worksheet's tab wasn't visible. (Probably done intentionally by someone)
Next, I added a blank worksheet which Excel called 'Sheet1'. This sheet contains the solution and I shall tell you more about it in a minute.
Finally, I made a copy of that sheet and called it 'Template'. This is your backup. The idea is that you might very well damage 'Sheet1' while working with it. In that case, delete 'Sheet1', make a copy of 'Template', call the new copy 'Sheet1' and you should be back in business.
Now, you will see two groups of 2 lines each in Sheet1. The difference is in columns A and B. In rows 1 and 2 you have a setup where you can enter a number in column A, in rows 7 and 8 the number should be entered in column B.
  1. You can copy any of the rows of either type to any row in your sheet.
    There is no need to keep the groups together or separate.
  2. The formulas in column A and B are different. The formulas in columns C:F are identical. You can copy them horizontally, if you like. The idea is to let you copy entire rows vertically.
  3. You can write numbers in the cells with white font on red background.
    You don't need to write the leading zeros in column A.
  4. Note that the question marks in the red underlaid cells (either column A or B) will appear only if the cell value = 0, but not when the cell is blank.
For now, you have to enter the number manually, in the correct column of the correctly formatted row, and the text will appear in the entire row. In a blank row columns C:F will display "Blank" until you enter a valid number. In the future you might enter the numbers using your scanner. That should be a simple thing to accomplish.

Note that there are some problems with your data sheet 'Main'. In column A every number must have 14 digits, including leading zeros. There are some numbers which don't match this pattern. They will not be found.
"J A C K S O N" will not be found because it is not a 14-digit number. In column B "xxxxxxx" will not be found. However, the formulas in my solution will look for a number in column B if no number is found in column A. Therefore "xxxxxxx" will never cause a problem because there is a valid number in column A.

Now you will have to teach your label printing software to use the sheet. Unfortunately, this site didn't allow me to download your blank workbook. Therefore I might have missed something about its formatting. Anyway, ..
  1. Your printer software will probably look for the first sheet in the workbook.
    That could be 'Sheet1' or it could be the first sheet in the workbook - and never mind its name.
    Actually, I suspect the latter. Right-click on the 'Sheet1' tab, select "Move or Copy" and move it to "Before Main". That should make sure that your printer will read from this sheet.
  2. I presume that your printing software will start reading in row 1 and continue reading until it encounters a blank row. Therefore it is possible that your printing software might ignore the items below row 3 in my attached Sheet1.
  3. Worst case, you can always export the solution in 'Sheet1' to a single tab separate workbook (right-click on the tab, Move or Copy, and select a New Workbook). You may have to Copy/PasteSpecial -> Values to change all formulas to hard values before printing.
Since I am not a regular visitor to this forum anymore I invite other experts to help you in case you need for assistance. Good luck.
  • 0

You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 72 guests

cron