Macro to Sort Coupons by Redeemer Number

I scan coupons with a desktop scanner into a giant folder and I'm trying to make this easier on myself if at all possible. I'm wondering if once I have the giant index of all the coupons if there isn't a way to come up with a macro to put the coupons into the correct column based on their four digit redeemer number.

Here is an example of the coupon data:

Column A Column B Column C, D, Column E
4/29/2014 15:10 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB1005-3\HB2013203589_04-29-2014_15-10-55.pdf 1 HB2013203589
4/29/2014 15:10 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB1005-3\HB2013249468_04-29-2014_15-10-55.pdf 1 HB2013249468
4/29/2014 15:10 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB1005-3\HB2013203553_04-29-2014_15-10-55.pdf 1 HB2013203553


The HB1005 is the redeemer number which will be found in column B. HB2013203589 etc. are the coupon numbers in column B and E (the same). I want to take all coupon numbers associated with each redeemer number and put them in a separate spreadsheet that has redeemer numbers listed at the top of each worksheet. I'm attaching that file.

The redeemer numbers (called FMNP #s) are each in column A2 of the worksheet and will always be there. So what I want is if 1005 is in A2, the macro will go to the next blank column in the worksheet and post all of the coupon numbers in that column, starting with cell 5.test coupon macro data.xlsm

Can anyone help? Thanks in advance!

Hi Norie - the data should be in sheets 2, 3, and 4. I left the summary blank since we can't share the redeemer's personal info.

~ Galena

Does this help?

Please Login or Register  to view this content.

Hmm... I tried running it and it looked like it was doing something but then nothing happened.

I may not understand what you want too happen. It takes your Raw Data Sheet and looks for the 1005. If it matches a worksheet where Range("A2") has that value then it copies the cells in Column E too the next available Column of the found worksheet? In your sample the Worksheet is Rochester Public Market. Not sure if that is what you are trying too do.

No - I'm sorry I'm not being clear. It seems this is complicated to explain. lol. What I am trying to do is set up two macros to automate this. The second half is the one you already did for me on another thread (TYVM!). That second macro takes all the coupons and lists them according to their voucher number. With the first macro I am hoping to automate the process of getting the scanned coupons into all those columns. So you can imagine the columns all blank but waiting for data from that list that says "raw data." I want to take that raw data and match a giant list of coupons belonging to many different spreadsheets (it may have data from both scalzo, wagner, and rochester on the example spreadsheet). So I will scan all the coupons into folders labeled with their FMNP or redeemer number and then put them into the "raw data." It will be much longer than 3 lines. I want the macro to then look at the giant list of all the coupons and their folders and the put the coupons that belong to each redeemer scalzo etc. into the next blank column in their worksheet. That way the coupons scanned in previous weeks are still there. Please tell me this makes sense? lol Trying my best to explain.

It should work if your Raw Data is layed out in the same way as your sample.

Maybe it was in triplicate because I ran the macro more than once. I should probably refrain from doing these kinds of things after lunch lol.

I tried running it again with 10 coupons assigned to either redeemer/FMNP # 1234 (Scalzo), 2345 (Wagner), or 1005 (Rochester) and all 10 coupons went into column C of the Rochester one. I want the macro to check all worksheets for the given redeemer numbers and put the coupons into the next blank column according to that - not just for 1005 but for each number there if that makes sense.

What does your "Raw Data" sheet look like?

This was the raw data I tried:

4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB1234\HB2013203535.pdf 1 HB2013203535
4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB1234\HB2013203534.pdf 1 HB2013203534
4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB1234\HB2013203533.pdf 1 HB2013203533
4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB2345\HB2013203532.pdf 1 HB2013203532
4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB2345\HB2013203531.pdf 1 HB2013203531
4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB2345\HB2013203530.pdf 1 HB2013203530
4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB2345\HB2013203529.pdf 1 HB2013203529
4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB2345\HB2013203528.pdf 1 HB2013203528
4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB1005\HB2013203527pdf 1 HB2013203527
4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB1005\HB2013203526.pdf 1 HB2013203526

I took off the date and time since that appears on the left also but that shouldn't change anything should it? The only other thing that is different is the HB1234 doesn't have a -3 after it. The -3 referred to the third time I scanned coupons with that code so that part can be ignored.

Can you provide a sheet with your Raw Data in it. Depicting how it would look when it is downloaded to the file. (ie what would be in what Column, how it is delimited ect.)

Yes, as soon as my boss stops buggin me! :P Here it is. I left the other data in but the last worksheet is the new raw data I was trying. When you run the macro you get this in Column C of Rochester:

30/Apr/14
HB2013203526
HB2013203527
HB2013203528
HB2013203529
HB2013203530
HB2013203531
HB2013203532
HB2013203533
HB2013203534
HB2013203535

(all 10 coupons)

test coupon macro data with raw data 4-30.xlsm

Try:

Please Login or Register  to view this content.

It worked! Is there something in there that puts the date above the coupons? I noticed it put 30/Apr/14 in both yesterday and today. Is there something I should be changing in the macro to get today's date?
And can I get your address to send you a thank you card? (seriously lol! - this will be a HUGE help since I do this 6 months out of the year).

You're welcome. Glad to help out and thanks for the feedback.

Try:

Please Login or Register  to view this content.

Beautiful - yes it worked. Is there anything I need to change in there or will it know tomorrows date &c? I guess I will have to wait until tomorrow to check! hehe

A couple of follow ups:

~Is there anyway to get it to put the Voucher Numbers above the date? So in other words the dates are in row 4, the voucher numbers are in 3. I will be working on each voucher number for maybe two weeks or so so all the coupons that I scan in that two weeks will need to have "VOUCHER #" so VOUCHER 1, VOUCHER 2 or whichever number I am working on. I can manually add them after running this, but I'm guessing you can put that in also? And then I'd just have to go into the macro and change that once I switched to the next number. I think it needs to be in caps for the other macro to read it.

~This one may be more complicated and if so, ignore: Is there any way for the macro to check and see if any of the items in the "raw data" do NOT have a worksheet to put the coupons in? So for example if there was a folder there in the raw data with HB1005 (cell B) but no worksheet for Rochester to put it into. Kind of like an error report of sorts?

Also, how did you get so smart about this stuff? I am homeschooling my 12 year old son next year (because he has grown to be too smart for public school!) and he already knows as much about html and css as I do but nothing about excel and macros - any resources you'd recommend for learning this stuff?

See if this modification handles your Voucher issue:

Please Login or Register  to view this content.

Your last request, I think is a bit more complicated than I can help with.

As for learning resources see below some of my recommendations:

John Walkenbach's Power Programming with Excel VBA series (J-Walk & Associates Home Page)

And this list was compiled by a lad named Hiker95 - I find it very useful;

Please Login or Register  to view this content.

That worked but it puts the next available number for the voucher rather than a specific number (so for sheets that had voucher 1 and 2 it put "VOUCHER 3" and for those who had 1 2 and 3 it put "VOUCHER 4") - I want to set it to a specific number for example "VOUCHER 31" and everything goes under that same voucher number until I change the macro to "VOUCHER 32." Even if there is more than one column in the same sheet with that voucher number on it.

Also found another potential bug - the one that has a redeemer/fmnp number of 0333 isn't going in since the "raw data 2" pastes it as 333. That's an Excel formatting thing but how can I set the formatting for Raw Data 2 to text since I can't see it until after the macro starts?

And thanks for the link I will take a look at those. Must be good info if he has hiker in the username - I run a small nonprofit in my spare time called Hikers for the Homeless.

See if this helps:

Please Login or Register  to view this content.

It allows you to set the Voucher number from an inputbox. It should address the bug as well.

Yes that input box works really well. Perfect! I hate to bug you again but I thought I could create one index file with the scanner software that I have - it seems I can only create index files for the individual folders.

So instead of the 4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\HB1234\HB2013203535.pdf 1 HB2013203535 data setup I would have:


4/30/2014 13:41 C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\May1\HB2013203535_HB1055.pdf 1 HB2013203535

Is there any way to modify the macro for that format? I tried to look at the difference between the two to see where to change the positioning but I am a bit confused. That way I can pot all coupons from a day into a single folder and just append the redeemer # to the end of the coupon. So it will still be in cell b but in a different position. Otherwise I have to find a way to combine all those index files which I am finding difficult since they are csv files with the same name.

I had another idea too: what if instead of finding errors, the macro could highlight each cell on the raw data sheet after it places the coupons into the worksheet with that FMNP/redeemer #? Maybe that would be an easier way of checking for errors. Also, do you benefit in any way from threads being marked as solved? If not I could leave this open to see if anyone else bothered to read this far in lol and if they have an answer for it. Even if I have to manually check double check it will still be way faster than how I was doing it but if there's some way I can check for errors that would be awesome.

See if this helps?

Please Login or Register  to view this content.

Hmmm... it seemed like it was working but it only did one line of the raw data (stopped on cell B2 of the raw data).

Can you provide another sample with the changes you noted. One where it stops on B2?

I couldn't duplicate the error with your current sample. But this modification should produce an error sheet which has the codes which were not found. Hope that helps.

Please Login or Register  to view this content.

Did you try replacing the code with the modified version in Post #31. Can you provide a sample where you are getting errors?

Oops we crossed posts. Yes I think that version didn't save and I had an older version. I edited the post above to show the error I am getting.

When it gets to this part:


rcell.Replace "C:\Users\Galena\Desktop\Coupons Folder\2014 Coupons\May1\HB2013203535_HB", "", xlPart

It says out of range and when I select debug it highlights this section above:

Sheets("Raw Data").UsedRange.Copy Sheets("Raw Data2").Range("A1").

Can you provide the attachment? Do you have a Sheet "Raw Data" with no spaces?

2014 FMHB Participant Redemptions Test.xlsm This is what I'm getting.

I still cannot reproduce the error with your current sample. However, I did notice that it did not pull all the data as intended. But that was because of how your downloaded "Raw Data" file is set-up.

See what happens with this one.

Please Login or Register  to view this content.

Yes it worked. I can't see what was wrong with my data but this one worked so I will stick with it lol.

Glad to hear it worked for you, and thanks for the feedback. Please mark this thread as solved.

Lol yes I bet no one else will make it to the bottom of this! :P

Wow. I'm afraid I'll have too agree. Have a great weekend.

You also! I hope you will be stepping away from the computer to enjoy whatever else there is to do in Jersey. I've only been there once even though I live in NY. It was a nightmare family vacation with my ex to the Jersey Shore. Before the TV show lol.