View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MyVeryOwnSelf MyVeryOwnSelf is offline
external usenet poster
 
Posts: 213
Default Comparing Excel # and JPEG Names

I have a listing of products in Excel.
For about 50% of those items I have a JPEG that uses the item # as the
name of the JPEG.
I need to create a list of items numbers that do not have an
associated JPEG. Is there a way that I can compare the JPGS that
reside on my hard drive against the Excel listing?


This is far from elegant, but such things have worked for me.

Using the "DIR" command-line command in Windows, get a textual list of file
names.

Then paste the list into Column A of a new worksheet.

Then use a formula in column B to extract just the file names there. Use
MID(...) or LEFT(...) or whatever. Also use IF(...) to get an empty cell
for lines that don't have file names in them.

Now go back to the "listing of products" sheet and use COUNTIF(...) or
similar to match with column B of the new sheet.

When things are updated, overwriting Column A of a new worksheet with a new
list should update things. The formulas should all continue to work.