VBA Code to optimize and clean data

**JoaoVr** · 03-20-2012, 01:36 PM

Hey everyone,

I'm a complete noob at VBA Code so I need some guidance to optimize and clean some unwanted data in a sheet that I have.

My sheet is something like this:

Name	Adress	Email	Phone	Code	Code Name
Oliver Jacob	Russia	OliverRussia @ hisISP.com	356 555 983	083	Designer
Jack Ethan	Germany	[email protected]	356555985	085	Engineer
Harry Michael	Turkey	[email protected]	356555982	081	Accounting
Alfie Jayden	France	[email protected]	356-555-984	Engineer	Engineer
Charlie William	United Kingdom	[email protected]	356555981	085	Engineer
William Noah	Ukraine	[email protected]	356555980	X	X
Alfie Jayden	France	[email protected]	356-555-984	Engineer	Engineer
Jack Ethan	Germany	[email protected]	356555985	085	Engineer

I need some bits of separate VBA code that detect some situations and tag them. (-Maybe in the column after the last one put a X mark so I can use a filter confirm each situation-)

-> Detect identical rows (all columns). - In RED
-> Detect empty rows (all columns).
-> Detect if some number isn't in 55555555 format /a email in [email protected] format / A code (specific clumns). - In GREEN

Thanks in advance,

**Leith Ross** · 03-20-2012, 06:54 PM

Hello JoaoVr ,

Welcome to the Forum!

I have attached an example workbook with the macro shown here. The macro is run from a button added to the worksheet. The only column that has special formatting is column "E" which has been formatted as text to allow for the leading zero or zeroes in the Code number.

The following checks are made. The Email address must contain at least 1 character in the domain part , an at sign ("@") separator, 1 character in the followed by at least 1 period followed at least 1 character in the local part. Also, no spaces are allowed in the Email address. The macro is not designed to check for a valid email address according to RFC syntax rules.

Some adjustments may be needed to adapt the code to your workbook. If you have any problems, let me know.

Please Login or Register  to view this content.

[code]

**JoaoVr** · 03-22-2012, 12:09 PM

Many many thanks! It's working perfectly! It will be huge time saver!

I have a second problem, other sheet columns have information something like this:

Smokes?	Nr of Kids?	Observation
Yes	3
Yes	2
No	0

Is there a way if I want to delete the "Smoke" and "Nr of Kids" columns and save the information in one "Observation" column? Like:

Observation:

Smokes; 3 Kids

Smokes, 2 Kids

Don't Some, 0 Kids

Thanks for your patience!

**Leith Ross** · 03-22-2012, 01:40 PM

Hello JoaoVr,

You can use this formula and drag it down. Change the cell references to what you are using.

=IF(A2<>"",IF(A2="Yes","Smokes, "&B2&" Kids","Non Smoker, 0 Kids"),"")

**JoaoVr** · 03-23-2012, 02:35 PM

Hey again!
#1
In your "Check Data ver 1" solution I got an issue. It finds the first double entry "Jack Ethan" but it dosent find the second one "Alfie Jayden".

Name	Adress	Email	Phone	Code	Code Name
Oliver Jacob	Russia	OliverRussia @ hisISP.com	356 555 983	083	Designer
Jack Ethan	Germany	[email protected]	356555985	085	Engineer	<------
Harry Michael	Turkey	[email protected]	356555982	081	Accounting
Alfie Jayden	France	[email protected]	356-555-984	Engineer	Engineer	<------
Charlie William	United Kingdom	[email protected]	356555981	085	Engineer
William Noah	Ukraine	[email protected]	356555980	X	X
Alfie Jayden	France	[email protected]	356-555-984	Engineer	Engineer	<------
Jack Ethan	Germany	[email protected]	356555985	085	Engineer	<------

#2
Didn't manage to make that code work, can you check my example:

Name	Adress	Email	Phone	Code	Code Name	Smokes	Nr of Kids	Observations
Oliver Jacob	Russia	[email protected]	356555983	083	Designer	Yes	3
Jack Ethan	Germany	[email protected]	356555985	085	Engineer	Yes	2
Harry Michael	Turkey	[email protected]	356555982	081	Accounting	No	0
Alfie Jayden	France	[email protected]	356555984	085	Engineer	Yes	1	Nice Guy
Charlie William	United Kingdom	[email protected]	356555981	085	Engineer
William Noah	Ukraine	[email protected]	356555980	001	Lawyer			Nice guy too

To something like this:
(This way I can delete columns without losing data - but keeping all that information in the observation field - merge 3 columns )

Name	Adress	Email	Phone	Code	Code Name	Observations
Oliver Jacob	Russia	[email protected]	356555983	083	Designer	Smokes, 3 kids
Jack Ethan	Germany	[email protected]	356555985	085	Engineer	Smokes, 2 kids
Harry Michael	Turkey	[email protected]	356555982	081	Accounting
Alfie Jayden	France	[email protected]	356555984	085	Engineer	Smokes, 1 kids, Nice guy
Charlie William	United Kingdom	[email protected]	356555981	085	Engineer
William Noah	Ukraine	[email protected]	356555980	001	Lawyer	Nice guy too

#3
Not trying to exploit your good will, but just to finish my data optimization I need to do one more thing:

-> I manage to integrate all my excel "databases" in one excel file (multiple sheets), but I need to compare if my main sheet has everyone in it, something like this:
Sheet1:

Name	Adress	Email	Phone	Code	Code Name
Oliver Jacob	Russia	[email protected]	356555983	083	Designer
Jack Ethan	Germany	[email protected]	356555985	085	Engineer
Harry Michael	Turkey	[email protected]	356555982	081	Accounting
Alfie Jayden	France	[email protected]	356555984	085	Engineer
Charlie William	United Kingdom	[email protected]	356555981	085	Engineer
William Noah	Ukraine	[email protected]	356555980	001	Lawyer

Sheet2:

Name	Adress	Email	Phone	Problem
Oliver Jacob	Russia	[email protected]	356555983
Jack Ethan	Germany	[email protected]	356555985
João Pinto	Portugal	[email protected]	356555979	NEW

Some VBA code that check if all phone numbers on the Sheet2 are in the Sheet 1 phone numbers column and tag them (The new ones).

Thanks for your patience!