I have a large number of drawing numbers that I want to cross reference to any workbooks in a folder that contain the drawing number.
The folder contains around 3,000 files, some of which will be backups, and I only want to check those that are a 6-digit number beginning with '6' and with an .xls extension
The workbook may contain multiple worksheets, but I only want to check the active worksheet (i.e. the worksheet that is active when the workbook is opened.)
Once the '6nnnnn' files have been identified/listed, I then need to find the workbooks in another folder that include that number (without the extension). The second search will apply only to files with a 6-digit number beginning with '1' and an .xls extension.
Example:
Drawing number: 502862
First file search path: "P:\600\"
Files in P:\600\ found containing "520862": 600883.xls; 601108.xls
Second file search path: "P:\100\"
Files in P:\100\ found containing "600883" or "601108": 101324.xls; 102769.xls
It is most likely that the searches in each case will only result in one or two files, but potentially could be more. However, I would accept a maximum of five results being returned. (These are 'rare' drawings and so by definition should not be frequently listed!)
Nominally I intend to start off with a worksheet listing the files/results as follows:
Column A: '5nnnnn' drawing number to be searched for (manually entered, up to 100)
Columns B-F: up to five '6nnnnn' file names (without extension) containing the number in Col A
Columns G-K: up to five '1nnnnn' file names (without extension) containing the number(s) in Cols B-F
A | B | C | D | E | F | G | H | I | J | K | |
1 | 5nnnnn | 6_1 | 6_2 | 6_3 | 6_4 | 6_5 | 1_1 | 1_2 | 1_3 | 1_4 | 1_5 |
2 | 502862 | 600883 | 601108 | 101324 | 102769 | ||||||
3 | 502317 | ||||||||||
4 | 502107 | ||||||||||
5 | |||||||||||
6 |
Any assistance on this would save me untold hours and be greatly appreciated!!