Forum Discussion
Aligning 1st and Last name column in Excel with similar .jpg file name in separate column for SQL
I have an excel spreadsheet. I have a column listed for first name and another column for last name. I have another column for JPEG images. The file name is similar to the name which is Lastname_Firs...
so maybe if you provided a sample file we might understand better but if all you need is to have a 3rd column that says what the jpg filename is and that filename is Lastname_Firstname.jpg then couldn't you just use (assuming firstname in col A and lastname in col B)
=B1&"_"&A1&".jpg"
and fill down or in excel 365 assuming rows 1:1000
=B1:B1000&"_"&A1:A1000&".jpg"
=B1&"_"&A1&".jpg"
and fill down or in excel 365 assuming rows 1:1000
=B1:B1000&"_"&A1:A1000&".jpg"
mtarler Thank you for your response. See sample file. Names have been changed, but basically I have copied the file name from the folder and pasted into a column on the document. There are 684 names and over 1000 .jpg file names, so they do not line up if I try to ascend and alphabetically. I am trying to get the Lastname_Firstname.JPG file to line up as seen in rows 10-13, I inputted these in manually.
I appreciate your assistance with this.
Thank you!
LastName FirstName ImageData JPEG abbot Paul Ackerman_Erin.jpg Abbott Adam Acosta_Alexandria.JPG Abbott Jeff P. Acosta_Angie.jpg Adye Austin Acosta_Olga.JPG Allen jill Adams_Mikey.JPG Allen Chandra Adams_Sam.JPG Almanza Flores Angel D. Adams_Thomas.JPG Alvarez Veronica Adriana.JPG Amaya Erika Amaya_Erika2.JPG Adye_Austin.JPG Anderson Logan Anderson_Logan.JPG Aerni_Shannon.jpg Archer Deborah Archer_Debi.JPG Affolter_Brandon.jpg Archibald Joshua Archibald_Josh.JPG Affolter_Brandon2.JPG Arneson Bryan Abbot_Paul.JPG Aufdermauer Faith N. Abbot_Adam.JPG Ayala Sanchez Ana G. Abbot_Jeff Ayers Jon Adye_Austin.JPG Baladez Mabelyn Allen_Jill.jpg Bales Justin Allen_Chandra.jpg Barber Richard Arneson_Bryan Barbour Maximilian L. Aufdermaur_Faith.jpg - https://answers.microsoft.com/en-us/msoffice/forum/all/populate-cell-based-on-similar-text/82bde8c1-23ab-48c7-bc88-2f71c925d408
fuzzy match
find minium edit distance? so basically you only want the file name listed if it exists in column D? try this:
=LET(n,B1:B1000&"_"&A1:A1000&".jpg", IF(ISNUMBER(XMATCH(n, D1:D1000)), n, ""))If you have an older excel you can use:
=IFERROR(VLOOKUP(B2&"_"&A2&".jpg", $D$1:$D$1000, 1, 0),"")
and then fill down
so in answer to your question, I guess not, this is as direct as I can think.
mtarler Thank you for the information. I appreciate your discerning and detailed assistance with this.
Yes, that is correct I would only want the file name listed if it exists in Column D. I am currently using the MS Excel latest Rev.
If I plug in the function =LET(n,B1:B1000&"_"&A1:A1000&".jpg", IF(ISNUMBER(XMATCH(n, D1:D1000)), n, "")) it highlights rows A B and D in Red, Blue and Purple and creates a spill column but I do not see any data in the spill column other than a few 0's. Not being an Excel guru, I would think the result should show the matched names in the spill column?
Thanks!
