Can Vba Help With Auto-checking My Spreadsheet?

[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 OR FUNCTION to write a query that if Member ID is th
same, then Start/End Dates on all corresponding rows should be the sam
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

 

[Jason Morin]

Let's assume MEMBER NO is in column A, START date in
column B, and END date in column C. In D2 (row 1 has
headers), put:

=SUMPRODUCT((A2=$A$2:A2)*(B2=$B$2:B2)*(C2=$C$2:C2))=COUNTIF
($A$2:A2,A2)

and fill down the column. Anytime you see FALSE, it means
the entry for that member does not match another entry
above for that member in the list. To help see it better,
you may want to apply an autofilter (Data > Filter >
AutoFilter) to columns A-D. Then filter by "FALSE" or by
member no.

HTH
Jason
Atlanta, GA

 

[AlfD]

Hi!

I would be inclined to use Advanced Filter to extract a list of uniqu
records (Member No;Start Date; End Date: just these 3 columns).
If any Member No occurs more than once then that can only be because
date (or two) is different.

You can check on repeats of Member No:

Sort on Member No

If your data is now in A1:C2000,

put in D2 =if(A1=A2),"#","")

and copy that down to D2000.

This will flag any repetitions. Use Autofilter to isolate them.

Al