freakszzy
07-26-2012, 07:50 AM
Hi guys, need your help in creating a vba code in validating 2 columns of data.
This are the criteria:
() Data are in Column B and C
() Column B is the Country ID and Column C is the Country ID_2
() Data in both column are consist of numbers
() Data in Column B and C has been group and there separator is a blank row/cell. Group can be in 3, 4, 5, 10, 50, rows etc. (not fix)
() In Column B 2 or more cell having the same number is consider as Valid.
() In Column B if all value in the group has the same number and Column C has no data it is consider as valid
() In Column B if all value in the group has the same number but 1 or more of column C has value the row in Column C having value should be consider as error.
() In Column B, if there are 5 rows in a group 4 having the same number and the other 1 is different this row will be consider as error
() In Column C if the entire group has value and column B is blank it is still consider an error.
() If Both Col B and C has value it is consider as error.
() Note: 2 or more same number in a group in Column B is consider as valid
() "Error" remarks will be put in Column A (Validation Result) for errors found. (Red Font)
() If there was no error found on entire column A will be filled with color orange.
Column A - Validation Result
Column B - Country ID
Column C - Country ID_2
Column D - Error Definition
"Separator" - this is blank rows (group separator)
Below is my example:
Validation Result Country ID Country ID_2 Error Definition
Error 3 1 Col B and C has Value
1
1
separator separator separator separator
6
6
Error 4 Column B (4) does not match the group w/c is 6
6
separator separator separator separator
8
Error 2 2 Col B and C has Value
8
8
separator separator separator separator
9
9
9
Error 5 Does not match the group w/c is 9
Error 4 Does not match the group w/c is 9
Error 3 Does not match the group w/c is 9
separator separator separator separator
7
7
Error 4 Col B has no value but col C has 4
Error 4 Col B has no value but col C has 4
separator separator separator separator
Error 8 Col B has no value but col C has 4
Error 8 Col B has no value but col C has 4
Error 8 Col B has no value but col C has 4
If my explanation is unclear pls. let me know. Thank you
I already posted this to mrexcel\forum heres the link Validating 2 Columns using excel VBA (http://www.mrexcel.com/forum/showthread.php?649169-Validating-2-Columns-using-excel-VBA)
If re posting coming from other forum is prohibited kindly delete my post admin and im sorry.
thank you
freakszzy
This are the criteria:
() Data are in Column B and C
() Column B is the Country ID and Column C is the Country ID_2
() Data in both column are consist of numbers
() Data in Column B and C has been group and there separator is a blank row/cell. Group can be in 3, 4, 5, 10, 50, rows etc. (not fix)
() In Column B 2 or more cell having the same number is consider as Valid.
() In Column B if all value in the group has the same number and Column C has no data it is consider as valid
() In Column B if all value in the group has the same number but 1 or more of column C has value the row in Column C having value should be consider as error.
() In Column B, if there are 5 rows in a group 4 having the same number and the other 1 is different this row will be consider as error
() In Column C if the entire group has value and column B is blank it is still consider an error.
() If Both Col B and C has value it is consider as error.
() Note: 2 or more same number in a group in Column B is consider as valid
() "Error" remarks will be put in Column A (Validation Result) for errors found. (Red Font)
() If there was no error found on entire column A will be filled with color orange.
Column A - Validation Result
Column B - Country ID
Column C - Country ID_2
Column D - Error Definition
"Separator" - this is blank rows (group separator)
Below is my example:
Validation Result Country ID Country ID_2 Error Definition
Error 3 1 Col B and C has Value
1
1
separator separator separator separator
6
6
Error 4 Column B (4) does not match the group w/c is 6
6
separator separator separator separator
8
Error 2 2 Col B and C has Value
8
8
separator separator separator separator
9
9
9
Error 5 Does not match the group w/c is 9
Error 4 Does not match the group w/c is 9
Error 3 Does not match the group w/c is 9
separator separator separator separator
7
7
Error 4 Col B has no value but col C has 4
Error 4 Col B has no value but col C has 4
separator separator separator separator
Error 8 Col B has no value but col C has 4
Error 8 Col B has no value but col C has 4
Error 8 Col B has no value but col C has 4
If my explanation is unclear pls. let me know. Thank you
I already posted this to mrexcel\forum heres the link Validating 2 Columns using excel VBA (http://www.mrexcel.com/forum/showthread.php?649169-Validating-2-Columns-using-excel-VBA)
If re posting coming from other forum is prohibited kindly delete my post admin and im sorry.
thank you
freakszzy