• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation by row

Andrew Quirl

Andrew Quirl

New Member
Data validation by row - the purpose of this macro is to 'clean up' a data set by analyzing each row, then submit the data set to an email address. Attached file "data dump" is an example of an unclean data set. Attached file "data validation by row" is the cleaned up result we are trying to achieve.
  1. to either delete the row or to keep the row based on if it includes one of a list of possible values:
    - valid date
    - order:
    - any of the numerical values 12345 thru 12363 as shown in the result file rows 3-21

  2. to pick out the purchase order number which will occur after keyword "order:"

  3. name the file using the purchase order number as the name, automatically email the workbook to an email address and close the workbook without saving.

  4. to include a particular value "fakename" in the subject line of the email.
 

Attachments

  • data dump.xlsx
    8.9 KB · Views: 3
  • data validation by row.xlsx
    8 KB · Views: 4
Last edited:
Msg #17 in the excelforum cross post linked above is a working example of a macro that achieves much of what we are looking for, but does leave room for improvement. What we are looking for is a fairly dynamic piece of code that takes minimal manipulation, if any, to use it with different customers whose data sets are slightly different.

Please help with modifying the code as follows:
1. Aside from choosing a row with a date, the macro triggers off the constant "EA" to allow a row to remain un-deleted. We would like to be able to have more than one choice here. (Ex: EA might be PC or PAC or CS or BX in other data sets)

2. We would like to have more than one trigger. For some data sets, one trigger is fine. But in other data sets, an additional trigger is needed. (Ex: rows remain un-deleted if they contain "EA" or "Item Number")
 
You must log in or register to reply here.
Back
Top