Auto-checking Data In Excel Spreadsheet

  • Thread starter Thread starter ieagune
  • Start date Start date
I

ieagune

I have a very large spreadsheet which contains the "MEMBER NO" column
the services they received at different times and their assigne
"START" & "END DATES" with the company. There could be multipl
entries/rows for each member in the spreadsheet, but the assigned STAR
& END DATE should be the same for every single row relating to tha
member.

This is where I need your help. I need to check that the above is tru
i.e. dates same for all member entries.......for data quality

I could do a "filter" and manually check them, but this would take
very long time.
Is there any way I can automate this?
Maybe using VBA, FORMULA OR FUNCTION to write a query that if Member I
is the same, then Start/End Dates on all corresponding rows should b
the same too.......else highlight the error
And any help in writing this little script will be much appreciated.

Thank you very much for your help.

Esthe
 
For this sort of job I usually sort the data (by member no.) and ad
another column to check that consecutive rows for the member contai
the same data with a formula :-

Say members in column A and data to check in column B.
=IF(A1=A2,IF(B1=B2,0,1),0)
puts a 1 if there is a difference. Can therefore SUM the column t
check total number of differences.

Could also possibly use VLOOKUP in a simlar way, without sorting
assuming you have a list. This way it is useful to make anothe
temporary column with a similar formula to above to check that th
VLOOKUP column cell is the same as the data cell value and put 1 or 0
 
Back
Top