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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,936
Office Version
  1. 365
Platform
  1. Windows
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.
 

Apank9

New Member
Joined
Feb 22, 2016
Messages
9
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.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,936
Office Version
  1. 365
Platform
  1. Windows
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
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">D:\atry\ccc\DF.xlsx</td><td style=";">aa</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">D:\atry\ccc\FFF.xlsx</td><td style=";">bb</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">D:\atry\ccc\sfd.xlsx</td><td style=";">cc</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">rename</p><br /><br />
 

Apank9

New Member
Joined
Feb 22, 2016
Messages
9

ADVERTISEMENT

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.
 

Apank9

New Member
Joined
Feb 22, 2016
Messages
9
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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,936
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

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
Top