macro to validate data

[saravanan1981]

I want macro to check few validation and correct data as mentioned below.

1) Serial number in column A Should be end of the data. If not Show popup “Serial number incomplete”
2) Trim and data in rows and columns from Row number 2 to till end of the data range
3) All the character should be converted to capital letters
4) There should not be any duplicate member. If there is duplicate member show popup “There is Duplicate member in Data” (validation to find duplicate is Name, DOB and Gender) and end macro.
5) The Family code mentioned against each Self to be unique. If one family code is mentioned against more than one Self then Show popup “There is duplicate family code” and end macro.
6) Data in Column E under “RELATION1” to be blank, If there is any data show popup that “Relation is already prescribed” and exit macro.
7) There should be value against each self in column I, J, K, L, M, N, and O. If any cell blank Show popup stating “Value under (Column heading) is blank.
8) Once Data in Column E Deleted and completely clean Need Macro for below mentioned task
a. Copy Relation “SELF” from Column “D” to Column “E”
b. If Relation is “Employee” in Column “D” then Copy Relation as “SELF” to Column “E”
c. Now Count number of children in one family with the help of family code in column B. Accordingly mention relation as Child1, Child2, Child3, Child4 and so on in Column E. For example refer employee code “10000522” there are 3 child in family hence in E, Mentioned as Child1, Child2 and Child3.

[Keebellah]

Hi, this is not helping out, this is asking for someone to write an application for you.
Have you tried the paid section of this forum?

[saravanan1981]

No I haven't tried.

This is I am just doing as an voluntary activity for maintaining Covid infected people as a free service to keep records of their hospital admission medicine etc.
And I can not pay for this.

If anyone is ready to help then ok. Otherwise Kindly ignore.

[Keebellah]

I didn't mean to be rude about it but this is something that requires a little more that a tip and some help to point you in the right direction.
'I want macro ...' well I think that there are many that want something.
How is you knowledge of macro's and programming with VBA? If it's zero then It won't do you much good since you also need to maintain it and maybe even correct of modify something.
I've downloaded your file and the 'request', will have to red it to see if I fully understand your query.

[saravanan2021]

I can maintain it once it is done by anyone.

[Keebellah]

I took a look but you would not really need macro's you could use conditional formatting to mark the empty cells that need to be filled.
The XYZ at the end of the names, where does that come from, I assume that the XYZ will stand for something else

[saravanan2021]

you can ignore XYZ. that is just sample data

Can you give mean example of conditional formatting.

[Corbett]

I have a data entry system called Dexel Form which may be of some use to you. I wrote it as a generic data entry system but it also has a data validation component and will highlight those records which do not comply with the rules you specify. I wrote it for Office 365 but it may work on your version of Office. I didn't fully understand all the validations you listed but I have implemented some of them in the attached sample file. If you download and install the addin from dexelform.com you can try it out.

[Keebellah]

Hi @Corbett, sounds good will take a look, that graet about this forum, we all share and learn from others and get new ideas
@sravaan2021: some links to lear to work with conditional formtting
https://trumpexcel.com/excel-conditional-formatting/
https://careerfoundry.com/en/blog/da...d...%20More%20
and if you Googel there's much more.