Mamagregory
New Member
- Joined
- Apr 11, 2014
- Messages
- 25
I'm having a hard time coming up with a macro that will sort this data the way I want. We have farmers market coupon barcodes that will be for $1 and $2 food coupons. The $1 coupons will all have barcodes that fall within this range: 850001-860000. The coupons will always start with HB2016. So the $1 coupons will be HB2016580001 to HB2016860000. We have to modify our spreadsheet (example attached) that previously only tracked $2 coupons to make sure we don't give any farmer too much money back. This is what I want the macro to do:
1. Skip the summary sheet at the front and the last two sheets ("raw data" and "vouchering database").
2. On every other sheet which will be for each farmer's coupons, there will be rows of data as on the example. When we run the macro it will check for data in B5 of each sheet and if it is there it will add a column to the right of that column and search the B column for any coupons in the HB2016580001 to HB2016860000 range, putting them in the inserted coupons. It will not erase any of the data in column C if it exists but rather move it over. Then it will go to the next column (the data that was in C but got moved over) and do the same thing until it hits a blank row 5 with no data in it. Then it will move on to the next farmer's worksheet stopping when it gets to the end or the raw data worksheet.
Example data can be found here with 3 farmers. There will be up to 200 farmers on the final data though- https://drive.google.com/file/d/0BzEMJRQuHZM1TVllRTl1R1ZzaGs/view?usp=sharing. It is possible for a farmer to have some $2 and some $1 coupons or they might just have one or the other. You can ignore the data on the summary and raw data sheets as that is not relevant to this macro but we need it for other macros we are using on this data. I am also wondering what will happen if the macro has already done some of the data and we then add additional rows. Is there a way to make it skip the rows that have already been parsed into $2 and $1 coupons based on the barcode?
Big thanks to anyone who can help or point me in the right direction as I'm still a newbie with this stuff
1. Skip the summary sheet at the front and the last two sheets ("raw data" and "vouchering database").
2. On every other sheet which will be for each farmer's coupons, there will be rows of data as on the example. When we run the macro it will check for data in B5 of each sheet and if it is there it will add a column to the right of that column and search the B column for any coupons in the HB2016580001 to HB2016860000 range, putting them in the inserted coupons. It will not erase any of the data in column C if it exists but rather move it over. Then it will go to the next column (the data that was in C but got moved over) and do the same thing until it hits a blank row 5 with no data in it. Then it will move on to the next farmer's worksheet stopping when it gets to the end or the raw data worksheet.
Example data can be found here with 3 farmers. There will be up to 200 farmers on the final data though- https://drive.google.com/file/d/0BzEMJRQuHZM1TVllRTl1R1ZzaGs/view?usp=sharing. It is possible for a farmer to have some $2 and some $1 coupons or they might just have one or the other. You can ignore the data on the summary and raw data sheets as that is not relevant to this macro but we need it for other macros we are using on this data. I am also wondering what will happen if the macro has already done some of the data and we then add additional rows. Is there a way to make it skip the rows that have already been parsed into $2 and $1 coupons based on the barcode?
Big thanks to anyone who can help or point me in the right direction as I'm still a newbie with this stuff
