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!
- ah wait I notice 2 things.
a) the names don't always match. In your example above you have:
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
and so you have a "2" after Erika and Brandon and you also have Debi instead of Deborah and Josh instead of Joshua
b) it has JPG instead of jpg
you can try the following version that takes last name and first 2 characters of first name instead but know a) it can pull the wrong result and b) it won't find nicknames that start different like "Belle" instead of "Isabelle"
=LET(n,B1:B1000&"_"&LEFT(A1:A1000,2)&"*", IFERROR(INDEX(D1:D1000, XMATCH(n, D1:D1000,2)), ""))
