Posts by JohnRW

All,

I've steped outside of my ability. I have a spreadsheet with about 1,700 records. Each record is a retail location and has a 'county' assigned to it, column J, county_desc.

I've inserted a column, Column I, and named it Area.

Next, I've assigned an order that software will be rolled out to a state based on counties. I have about 60 counties and 10 areas to rollout to. I would like a formula to go through the 1,700 records (column J) and determine which of the 10 areas the county name in column J is in, then put the appropriate value in Column I (1-10).

I've searched for something similar like VLookup from an array or nested IFs but nothing quite does the trick.

Thank you for any help you can give!!

Re: Search Find Copy

This forum is very difficult to use. I did not use the excel form section becaues excel is only a portion of the Macro... but I'll move it if need be. Shall I repost or can a Mod move. Please advise.

Hello to all, JimFuller has helped me greatly to refine/define my requirement. I’ve searched the forums over the past day and found some previous solutions that come close but do not to the job. Would anyone mind helping me to create a solution which satisfies the below (3) steps? Thank you, I am looking for a macro that will:

1) Search a folder (C:\Documents and Settings\username\My Documents\IMFILE) for filenames that match a list in an Excel workbook.
2) Move the files that match to another folder (C:\Documents and Settings\username\My Documents\IMFILEMATCH).
3) Write an error log to another sheet in the workbook when there is no match.

Thanks

John

Re: School Qualifications

Hey:

Do you have the spreadsheet with values in C & D 6? If so I'll just make it work and repost it for you.

~John

Re: Search From A File

1) The CD images will be stored in a Windows folder named "IMFILES". Correct!

2) The image files will be named XXXXXXXXXX-yyyyyy.TIF. Correct!

3) The XXs in the file names will correspond to the voucher number in column A of your spreadsheet. Correct!

4) When there is a match between the voucher number in column A and one of the filenames in the folder IMFILES, you want to copy the matching image file to another Windows folder named "IMFILEMATCH". Correct!

5) You want to use another program, not Excel, to print the files in the folder IMFILEMATCH. Close! I’ll just go into the folder and print it on my own, I am just looking for the tool to do most of the leg work in, searching, and copying in a new folder.

6) If you don't find one of the voucher numbers in the folder IMFILES, you want an error log printed on an Excel sheet for review later. Correct!

I apologize for my inability to clearly state my issue, thank you very much for patiently fighting through it with me!

~John

Re: Search From A File

1) The CD images will be stored in one file - lets call it "IMFILE". OK sounds great to me, from this point forward I’ll refer to it that way.

2) The IMFILE will contain a list of names that correspond to the voucher number in column A of your Excel file. Yes, close. IMFLIE contains files which are named in the following format, XXXXXXXXXX-yyyyyy. The XXXXXXXXXX first 10 digits (x’s) are the same digits which will be in column A.

3) The IMFILE will NOT contain anything else but a list of names. The IMFILE will contain a is a file of Images named by voucher number. As you stated in ‘1)’ “The CD images will be stored in one file’.

4) You want to search the IMFILE for voucher numbers that appear in your Excel file. Yes, I need to find the files which correspond with the records stored in the spreadsheets column A.

5) You want to do something if there is a match. Yes, I want to copy the image files which match into another file which will contain only the files which were found to match up with a record in Column A of the spreadsheet.

6) What kind of files will the IMFILE be? What is the layout of the IMFILE? The files are within a windows folder and are all .TIF type image files.

7) Where will the Voucher Numbers appear in the IMFILE? The IMFILE names are XXXXXXXXXX-yyyyyy (as discussed above) The first 10 digits XXXXXXXXXX match the spreadsheet column A voucher names which are being searched for.

What happens after you find a match? With a match found they should be copied into a new location so that I can print the entire location.

Re: Search From A File

What kind of files will the IMFILE be?
What is the layout of the IMFILE?
Where will the Voucher Numbers appear in the IMFILE?
What happens after you find a match?

The files are within a windows folder and are all .TIF files. The files open to a scan of a check. File naming convention is as follows, XXXXXXXXXX-YYYYYY, the leading 10 (X) digits are the voucher name from Column A in the SS. With a match found they should be copied into a new location so that I can print the entire location.

Thanks!

Re: Search From A File

Jim

1) The CD images will be named and stored in files. (I will pull all the files (images) from the CDs and place them into one file on my PC)
2) The list of Voucher Numbers in column A of you spreadsheet will exist in one of the files containing the names of the images. (the image names which will be all on my PC are named by voucher number)
3) You want to search the files listing the images for a match. (I want to search from the file of images using each row in column A of the spreadsheet)
4) When you find a match ( to copy the matched file and place it in a new folder. )

Having all of matching files (or voucher images) in a new folder, and having the search pull the files automatically will keep me from having to manually searching for. Next I’ll be able to print the images within the folder the processes created so that I can run the process and forget about it, once it is complete I can print the files it found. One trick is, this is for an audit of sorts, so the column A of the spreadsheet is required to be audited and I must provide the records, if it misses one Id like an error report to tell me.

Thank you,

~John

Re: Search From A File

Hello,

Thanks for the quick reply. I’ve attached a copy which shows how the spreadsheet will look. The issue is I have about 25 cds which have images of the front and back of checks which have processed. They are named using the same convention as is shown in my column A of the “Example Spread Sheet”. I need a way to search from the numbers in Column A from a folder I’ll copy all of the cd’s images into, then I would like the script or macro to copy the file I just searched into another folder which I can print.

This keeps me from individually searching each row from the spreadsheet and matching it up with one of the files and manually printing them. I need to do this for a random selection of 300 checks out of the many files stored on the CD. Is this more clear?

Thank you,

~John

Hello:

I need to search for values from my excel spreadsheet row A ('Picture #') in a folder on my C:, with a match from the spreadsheet in the file name of the folder I am searching, I need the matching file copied down to a different file. Also, I would like it to keep a record of error searches. So I separate tab on the spreadsheet being searched from to show which of the values from row A were not found.

Can anyone help me with performing these actions, either through macro or VB or what ever...

Thank you,

~john