Excel Validation Help Needed

[Sam]

Ok, so here is my situation..

Small business who advertises with local paper with Barcoded Coupons..
each customer has an account # with a memebrship card..

I can import the coupon # and the Card number directly into an Excel
spreadsheet using Barcode reader & Card reader..

how can I validate it so a coupon code and custer account# can never
be used in that combination again

For example:

Col A1 - contains ad data xyz
col B1 - contains card # data 123

what I am trying to accomplish is if customer 123 uses xyz coupon for
the first time it will be redeemed, then the corresponding cells then
will be copied (A1, B1) to a master validation sheet, then the data
will be cleared for next customer..

If customer 123 attempts to use xyz coupon again it will display
duplicated entry warning, then copy those cells to an abusers sheet
for review later..

However if customer 123 gets a new abc coupon, that can be redeemed
and the whole process loops around again to add new coupon and
customer number to validation list..


Can someone help with this? no big budget for IT crew hoping for help
here..

Thanks,
Sam

 

[Norman Jones]

Hi Sam,

If you are amenable to a manual solution:

Assume all coupon numbers are entered in Col A , all Card Numbers in Col B.

Use/Insert a helper Col C. In C1 enter the formula:
= A1 & "/" & B12

Drag C1 down to (say) C1000 (or however many coupon redemption applications
you anticipate - you can always extend later!)

Select cells A1:C1000:

Select Conditional Formatting on the Format Menu.
In the first box, select :
Formula Is

In the second box type:

=COUNTIF(C$1:C$1000,C1)>1

Hit the Format button, select the Patterns tab, select a color that you can
live with. Hit OK to confirm the color. Hit OK to confirm Conditional
Formatting.
Done!

Now, existing duplicate entry rows will be highlighted in the selected color
and future entries will automatically be highlighted if the represent
duplicate entries.

 

[Norman Jones]

Hi Sam,

Small typo:

Change the formula:

=COUNTIF(C$1:C$1000,C1)>1

to:

=COUNTIF($C$1:$C$1000,$C1)>1