Rename files based on column match

[Apank9]

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.

 

[Akuini]

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]

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]

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
	A	B
1	D:\atry\ccc\DF.xlsx	aa
2	D:\atry\ccc\FFF.xlsx	bb
3	D:\atry\ccc\sfd.xlsx	cc
rename

 

[Apank9]

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

	A	B
1	D:\atry\ccc\DF.xlsx	aa
2	D:\atry\ccc\FFF.xlsx	bb
3	D:\atry\ccc\sfd.xlsx	cc

<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]

Employee ID	Social	current Image file name
1234	1234567890	\\test\1234567891.jpg <tbody> </tbody>
1235	1234567891	\\test\1234567890.jpg

<tbody>
</tbody>

So the end result for the first row would be \\test\1234.jpg

 

[Akuini]

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.

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