I have an Excel spreadsheet. The first sheet contains a number of known columns in known positions (eg A=Name, B=Address1, C=City, D=Country …). the second contains a list of acceptable values for specific columns (EG A= all the acceptable cities, B=All acceptable Countries etc).

I have 2 questions with a possible bonus point:

1) Can I write a macro that will convert all the text in certain columns into upper case and strip leading and trailing spaces from values? This macro should do all the affected columns in one go if possible as there are about 40 columns affected.

2) Can i write a macro that will highlight all the cells in particular columns that do not have allowable values as listed on the second sheet?

For the bonus point : Is there some way of improving the allowable values so that the CITY column will only check for cities within the COUNTRY?

Fully coded answers welcome or just advice

Trim and upper functions are your friend

I know the function bits, it is how to put them in a macro I am struggling with. Especially the vlookup/hlookup for allowable values.

You shouldn’t need a macro to do 1. As T1000 says – use trim and upper.

if cell Sheet1!A1 contains “My name” then I’d create a new Sheet with the “cleaned” data, where cell A1 on the new sheet contains =upper(Sheet1!A1)

That will give you “MY NAME”.

If you make it =upper(trim(Sheet1!A1)) then:

” MY Name”
” My NAME “
” MY NAME”
“MY Name “
“My name”
” my name “

etc all become “MY NAME”

You can fill down and across to apply to all rows/columns and get “clean” data.

I think I could also do Part 2 without needing a macro. A VLOOKUP could do it (with a N/A error if not found). You can then apply conditional formatting to highlight the cell in red / yellow etc. If you don’t want to see N/A where there is an error (i.e. you want the entered data) then you can use ISNA to reinsert the data – which I would CONCATENATE with e.g. * to give you a “highlight” and something to conditionally format on. I haven’t tested this, and it will need some tweaking but something like this on your “clean data sheet”…

=if(ISNA(vlookup(Sheet1!C1,Sheet2!$C$1:$C$1000,1,FALSE),CONCATENATE(“* “,Sheet1!C1),Sheet1!C1)

You probably want to nest in there the trim and upper functions too.

1) Start off with this & modify to your needs: http://www.ozgrid.com/VBA/change-case-text.htm

2)Prob use conditional formatting rather than code

Bonus)Two way lookup using SUMPRODUCT function in conditional formatting could do this – bit complicated though. Could also be coded.

Can do this for you, but would take a bit of work so not doing it for free 😉

Cheers for the help so far.

I guess I could use a separate spreadsheet to hold the clean data values and then just point that at the dirty source spreadsheet.

this is going to be used to clean about 40 columns in about 35 spreadsheets each month for about 8 months so I want it as automated as possible.

Part 1
This macro will do the upper and trim function to the whole worksheet. It can be modified to do just a region if you like

Sub Macro1()
‘
‘ Macro1 Macro
‘ Macro recorded 06/06/2011 by RCP
‘

‘
Dim vCell As Range
Dim wst As Worksheet

For Each wst In Worksheets
For Each vCell In wst.UsedRange
vCell = Trim(UCase(vCell))
Next vCell
Next wst
End Sub

Part 2:
Conditional formatting would be my choice. Alternatively what I’ve done before is to insert an extra column on one side and then use V or HLOOKUP to check against a table