Check data for consistency and completeness

[Noah14]

Hi,

I am looking for a macro to check my data for consistency and completeness. Every couple of weeks I get a file with several hundreds to a thousand lines. Cells in the file contain document names, each cell may hold up to 16 document names. Document names differ at the fourth position from the right indicating a series of documents (e.g. L vs R or 0 vs 1 vs 2). Each series may consist of several duplicates indicated by the last two digits of the document number (e.g. ...-L-01 up to ...-L-04). Document names in cells are separated by line breaks. See attached sample file or below:

623-M-AB-200003-02-1-L-01
623-M-AB-200003-02-1-L-02
623-M-AB-200003-02-1-R-01
623-M-AB-200003-02-1-R-02

What I would like the macro to be able to do is a bit tricky:
1) for each duplicate of an L-series there should be a corresponding duplicate of an R-series (if there is ...-L-03, there must also be a document ...-R-03). Same holds for document series 1 vs. 2.
2) the macro should identify the highest duplicate number in a cell and check that all duplicates with a lower number exist in that cell (if there is ...-1-04, there must be ...-1-03, ...-1-02, ...-1-01 duplicates)
3) If any of the above checks results in FALSE, write FALSE into the cell in the column next to it or better still,...
4) ... put the names of the missing documents into the cell next to the one being checked
5) If any of the document names in a cell does differ in the digits up the the slash indicating the series, put a FALSE or the document name in an output cell two colums to the right.

To me this is impossible. But I would appreciate even a solution to parts of the problem.

[LJMetzger]

Hi Noah14,

Try the attached file, which does the following:
a. Puts the names of missing files in Column 'C'.
b. Puts the names of extra files (different base file name) in Column 'C'.
c. Leaves the 'Data File' open after completion.

Lewis

[Noah14]

Hi Lewis,

thanks a lot for the macro (an awesome piece of coding from my beginner's perspective and good for learning more about VBA) and my apologies for this late reply. I just did not manage to test this any earlier.

Hopefully you won't mind me bothering you with this again, but I encountered a couple of issues with the macro on my computer (running Excel 2010 on Windows 7 Pro SP1). First error encountered is untime error 1004: Method 'Select' of object '_worksheet' failed (debugging pointer at line 212 (ws.select) in the ModFindMissingFileNames Module). Even though it seems like the macro does get the correct input value from cell B10 (variable defined as sDataSheetName) . I checked this by inserting Msgbox and also while hovering over ln 202/203 definitions the correct values show.

Anyway, instead of opening another file, I just copied the data into the file you provided and ran the macro using this source file. Then I encountered run-time error 9: Subscript out of range, with the debugging pointer atln 241 (sBaseFileName = a(0)). Despite this error, the macro seems to run through both passes and actually populates column C with the incorrect/inconsistent entries... Hm, I just ran it again after deleting a blank row and now it did work without error!

Regards,
Noah

[LJMetzger]

Hi Noah,

Don't worry about the late reply. I know that people are very busy multitasking. Thank you for the detailed explanation of your problems.

First error encountered is runtime error 1004: Method 'Select' of object '_worksheet' failed (debugging pointer at line 212 (ws.select) in the ModFindMissingFileNames Module).

An error like this is difficult for me to debug without seeing your file. I did some error checking, however it looks like I must have overlooked something.

Then I encountered run-time error 9: Subscript out of range, with the debugging pointer at line 241 (sBaseFileName = a(0)).

This is caused by the blank line, which is another thing I didn't test, but easy enough to fix.

----------------------

If you can, please upload a copy of the file that causes the 1004 error, so I can try to find out what I did wrong. To upload a file, click on 'Go Advanced', underneath the reply window. Then click on the 'PaperClip Icon' above, or the 'Manage Attachments' CommandButton below the message window.

Lewis

[Noah14]

Dear Lewis,

I have uploaded the file (JB06MC) that caused the error. It actually contains five sheets, but I have deleted the contents of the other four due to sensitive data included.

I have also uploaded your original file. In this I have copied the docs to be checked into the docs tab and then ran the macro. Which worked excellent except for the blank line error. If I may add a little spec though from what I noticed upon verifying the macro results: Instead of L and R combinations there may seldomly be A and F combinations. So if there is an A and F, there is not an L or R missing and the entry is correct (A119). However, there should only be A and F or L and R, but no A/L or R/F combination. Anyway, there is only few cases, so if it is too complicated to add this, this is not a big issue.

Regards,
Jens

[LJMetzger]

Hi Noah,

Thank you for uploading the data file. It was most helpful in diagnosing the errors in my original file (caused by insufficient testing on my part). I corrected those errors and added the A-F combination. I also added output of the words 'DATA TYPE MISMATCH', if file names are not all of the same type (L-R, A-F, numeric), or if the file name contains a different letter.

Please let me know if you need any additional changes.

Lewis