Arejae.Com

Due to difference nature of work between me and my wife, I never had a chance to help her in her daily stuff related to her work and of course with my IT skills but for today, I’m happy to be able to help her.

She has a situation where she need to update her report based on the images files which is located in another PC. The files represent the matrix card which is already scan earlier and the filename is based on the matrix card number.

It’s pretty easy job as she need check the files and update her report if necessary.

What she did was, she open the folder, scan tru the images to compare the records and update it in Excel file . She also need to update the file’s modified date if it is exist.

Now, imagine what will happen if you are having a thousand files or more,it’s troublesome right ? doing the same thing all over again ?

At the moment I heard the situation, I know this problem can be solve easily with a help of VLOOKUP function in MS-Excel and a little bit DOS command added with a few more magic.

Well, as you know that sharing is caring… now I will show how it can be done.

First we need map the network drive and then get the listing of the files (Start->Run: type cmd to get the command prompt).

Using dir command will do to get the listing but in this case, we output the result to file. Open the file using textpad.

I’m using TextPad because there is a powerful option where we can have a block select mode.

Clear up the file and use block select mode to copy and paste to Excel file.(note:you can’t do this using notepad)

Now,let’s Excel do the magic.

formula to check the file exist :
=IF(ISNA(VLOOKUP($B4&".jpg",$B$14:$C$17,1,FALSE)),"No","Yes")

formula to get the file date :
=IF(ISNA(VLOOKUP($B4&".jpg",$B$14:$C$17,2,FALSE)),
"No Data",VLOOKUP($B4&".jpg",$B$14:$C$17,2,FALSE))

This only the sample data whereas the real data is lot more than this for sure.

Thing I learn : One thing might be pretty much easy for us but not for the others. Hence i share this, who knows someone will benefit from it.

Till then…Senyum Sokmo.

If you're new here, you may want to subscribe to my RSS feed or get my latest post directly in your mailbox. Thanks for visiting !