Rename files based on column match

Apank9

New Member
Joined
Feb 22, 2016
Messages
9
I have a folder of images that I need to link to. Each image is named as the employee social. In order to link to the image files, I need them to be named as the employee ID number.

I have 2 columns right now. A = Employee ID B = Corresponding employee social.

I would like to batch change the file names of the images to the employee ID number based on the columns I have that I know are correct. So, if 123.jpg matches 123 in B15 then change to 321.jpg based on A15.

I hope that makes sense. I can diagram it if it helps.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
There is a freeware called 'Advanced Renamer'. It can do batch rename files using a list. I think it easier than to do it using excel macro.
 
Upvote 0
There is a freeware called 'Advanced Renamer'. It can do batch rename files using a list. I think it easier than to do it using excel macro.

I downloaded it and it looks very useful for direct renaming. I can't see a function where it will rename based on matching from another list.
 
Upvote 0
OK, here's the steps:
1. create list in excel, use full path name for the file to be renamed in col A & just the name for the new name in col B.
2. save as csv file then close the file
3. open advance renamer
4. click import in the menu
5. insert the csv filename
6. you will see the preview how it is gonna look like
7. click import then start batch

List in excel look like this

Book1
AB
1D:\atry\ccc\DF.xlsxaa
2D:\atry\ccc\FFF.xlsxbb
3D:\atry\ccc\sfd.xlsxcc
rename
 
Upvote 0
OK, here's the steps:
1. create list in excel, use full path name for the file to be renamed in col A & just the name for the new name in col B.
2. save as csv file then close the file
3. open advance renamer
4. click import in the menu
5. insert the csv filename
6. you will see the preview how it is gonna look like
7. click import then start batch

List in excel look like this
AB
1D:\atry\ccc\DF.xlsxaa
2D:\atry\ccc\FFF.xlsxbb
3D:\atry\ccc\sfd.xlsxcc

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
rename

There is a step in between that is causing me the issue. The new name of the file is based on a comparison to another column. I know column A (emp ID) matches B (ss). C has a list of the image file names (ss.jpg) that may or may not have a match in B. So, IF C has a match in B, rename to A.

So matter how I type it out it sounds confusing :) I'm thinking that I need to do the comparison of socials first and then I could use the program to do the batch renaming.
 
Upvote 0
Employee IDSocialcurrent Image file name
12341234567890
\\test\1234567891.jpg

<tbody>
</tbody>
12351234567891\\test\1234567890.jpg

<tbody>
</tbody>








So the end result for the first row would be \\test\1234.jpg
 
Upvote 0
You mean '1234567890.jpg' become 1234.jpg, right?
Try this formula:
First, change B$10, if your data has 50 row than it becomes B$50
Then put the formula in cell D2 and copy down.

Code:
=INDEX(A$2:[COLOR=#b22222]B$10[/COLOR],MATCH(C2,B$2:[COLOR=#b22222]B$10[/COLOR],0),1)
 
Upvote 0

Forum statistics

Threads
1,214,276
Messages
6,118,629
Members
448,844
Latest member
Jayee04e

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top