barcodes handling as daily routine

Will you have a situation where barcoded scans overlap? In the example you show where barcode 32108 duplicates. But this is only a one ticket sale.

Suppose you had one sale with a start of 32100 and end of 32104 (5 tickets) and another of start 32003 and end 32006. Can this situation happen?

Unless I hear otherwise, I will assume that the combination of start and end barcodes is used to determine if barcodes are unique. There will not be an overlap condition as I pointed out in the previous post.

First, I converted the Selling Record into an Excel Table for a couple of reasons.: Tables know how big they are, so you don't have to change formula or pivot table ranges as you add data. Tables copy down formulas automatically. You can use column header names in formulas.

The first 5 columns in the table are the same as provided in the example.

Column F parses out the third digit of the ending bar code using the mid formula and does a Vlookup against a table on the Lookup sheet to determine which cinema it is. I had to multiply the third digit by 1 because the lookup value is a number and the MID() function returns a string. Multiplying a string that looks like a number by 1 converts it into a number. Since the lookup table is also a table, if you add another cinema, you do not have to change the formula.

Column G does a similar thing except it looks at the 13th digit to determine if it is an adult or child.

Column H looks at the last 4 digits of the end and start barcode and determines the number of tickets sold.

Column I looks up against the same table as Column G, except it gets the ticket price.

Columns J, K, L are the same as in the original data.

Column M is a "composite key" it concatenates the start and end barcodes to check if the record is unique.

Column N contains the formula =MATCH([@Compostie],M:M,0)=ROW(). This returns TRUE for the first occurrence of each composite and false for subsequent occurrences. This value is used with conditional formatting to highlight the row in red if a duplicate is detected.

Both of these columns may be hidden.

I think the pivot table handles the summaries you wanted.

If you need some more information about Excel Tables, this article may help: http://www.utteraccess.com/wiki/inde...ables_in_Excel

Just getting back from holiday - will need time to catch up.

Run this by me again on how you plan to use these sheets. Particularly, what is the difference between purchase and sold?

Please elaborate on what the various tabs are. I get that matching is the lookup tables.

In the purchased workbook, the detail list looks like tickets already sold. The record tab looks like the list of prescanned but unsold tickets. Also what is the significance of barcodes 1 to 5?

In the sold workbook, why do you only show barcode 1 and 5? I think I know but tell me anyway.

This would be easier if the information were all in one workbook. However, data can be imported from multiple workbooks into one for the purposes of reporting.

One of the issues I think we will have to deal with is working with barcode ranges in the sold workbook. These will have to be exploded out into individual bar codes.

So let me rephrase the requirement: You prescan a bunch of bar codes. (please confirm this location) and you want to know which of these were used and which were not used by looking at the sales record, I presume. Is that all you want or do you want the other information like cinema and type, etc.?

Dear Dflak,

I know it is somehow confusing but I'll try to explain this as much as I can.

- Purchased record is to keep track of what we have purchased and prevent of duplication, yet for us to number each booklet (5coupons in 1) we have made
- Sold record is to keep track of what have been sold and the amount of what we shall receive by the date
- Used coupon record is to keep reference of what we shall pay to the developer monthly, and yet to prevent re-use in case error. For payment we made each month, only those have been USED by the customer counts.

** the coupon is valid for upto 2years for the customer **

First we have to purchase the coupon from the developer (let's say "A"), since we are one of the selling location for customer, we then need to purchase these coupons from A, so we have a list of barcodes which one barcode of 16digits represents one coupon, that is why we need a purchase record to keep for reference and for monthly payment, and since the cost of what we purchased is actually $2 cheaper than we sell to the customer, you can see there is two matching tables (which I tried to combine them but fail of reading data to certain pages so I stick with two tables instead). The sheet named "detailed list" is for all the coupons I have purchased everyday from the developer, the sheet of "purchased record" (I know my formula might've looked stupid) is actually a print-out report that I tried to make it run automatically so that I can generate a report of what I have purchased that day and distribute together with the actual coupons to different counters for selling as a reference. "Sold record" is a record apparently for what have been sold on the day. Now by the end of each day, I will receive a list of coupons' barcode that have been used at certain cinema, my boss would like to know several data as below, I'm thinking to use a filter but don't know how I could have all the data sorted:

1.) data shows the purchased coupon which have been sold and unsold
2.) data shows the sold coupon which have been used and unused
3.) data shows the amount(quantity and cost by date) of purchased coupon
4.) data shows the amount(quantity and cost by date) of sold coupon
5.) data shows the amount(quantity and cost by date) of used coupon

I hope I did make the picture clearer to you by now.

Thanks and look forward to hearing from you soon.

B.rgds,
Jaco

I am going to have to take a bigger look at this to make sure that what you are saying makes sense in my mind. I have to go away, "let it settle" and then come back to it.

Can you give me a short example of "before and after" so I know what you mean by "extracting from the various columns to one column?"

I'm still not sure that this is what you want. I ran into an issue. On the detailed list page, there was some hidden data down to rows 1002. Whatever it was, it was "countable" and was also detected by going to the bottom of the column and pressing CTRL up arrow or by going to the top of the data and pressing CTRL down arrow. I could not see anything in the cells but they could be filled with ASCII characters above 127.

To make the code work. I went down to the end of the visible data and deleted all rows after that.

I just got back from vacation, and have some backlog of work to clear out. It may be a while before I can get back to this.

I assume you mean row 2305 and not column 2305. I did a bit of experimenting and the reason the rows are turning red are because of conditional formatting that is checking for a false value in column N.

Column N checks for duplicate ranges with the formula =IF(D294>0,MATCH([@Composite],M:M,0)=ROW(),""). It does not check for individual duplicates. It only checks that the combination of the first and fifth bar code are unique. If you want to check that each bar code is unique, it will take a more complicated formula and I would probably do it with some helper columns to keep it from getting too complicated.

The Match = Row method of checking for duplicates can be modified to tell you where the first occurrence of the duplicate is.

Let me know what you want to do with the duplicates. Is the range good enough (as we have now), or do you want to check each bar code - the latter will have 5 match formulas for each of the 5 columns.

Now as to why it first started showing red at row 2305, I can't tell you other than you are beginning to scan duplicates. Tell me what you mean by formulas no longer being valid.

Also, I am not understanding your requirement with the sequence number. I'll need some elaboration. Perhaps a sample with a walk-through. With the example you provided and the existing numbers, we're in month 96.