Checking Data

[rszlp]

Hi,

I am an excel novice so please be gentle! I am currently involved in a project where a considerable amount of data is being sent to me in Excel Spreadsheets. In order to QC the data I need to do the following:

Check that all entries in Column A contain exactly 8 characters and also that there are no duplicates in Colum A.

Is there an easy way to do this?

For duplicates I had been inserting a column then using this formula

=IF((A1)=(A2),"DUPLICATE","")

I then paste this formula down the length of the new column, paste special the value of the results into a second new column then sort the data, so all the duplicates are grouped together, then delete the redundant 2 columns. Long winded definately, but as I said I am a novice.

The trouble with the above (apart from the time) is that I have to find a duplicate in the first place to apply the formula.

I do not want to delete the data I just need to identify duplicates for rectification.

Grateful if anybody can offer any help or advice on either the duplication or the checking of characters. Thanks

 

[agihcam]

Hi -
Welcome to the board.
What would you like to do when the characters in columnA is not equal to 8?

 

[rszlp]

If the characters do not equal 8 I just need to readily identify it it so I can go back to the source to get the correct data and then amend it.

I suppose conditional formatting would be my best bet but unsure of correct formula to use.

Many thanks

 

[agihcam]

some code taken from current workbook to highlight duplicates in columnA;
hit Alt + F11, insert module, then paste this code;

Sub colordups()
Dim Rng As Range
Dim i   As Long
'highlight duplicates
i = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A1:A" & i)
With Rng
    .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    .SpecialCells(xlCellTypeVisible).Select
    ActiveSheet.ShowAllData
    Selection.EntireRow.Hidden = True
    .SpecialCells(xlCellTypeVisible).Interior.Color = vbYellow
    .EntireRow.Hidden = False
End With
End Sub