Excel: Is it possible to reorder the data in 2 columns to match up if they have a certain number of-CodePudding

I am currently re-ordering massive amounts of media files and trying to conform them to a logging system. I have 2 lists now, one is a list of all the URLs that the videos have been ripped from.

The other are the .mp4 filenames from the bulk download.

Column A looks like

C:\Users\XXX\BIN\MEDIA\1614561651289915393_2.mp4
C:\Users\XXX\BIN\MEDIA\1615390470523191305_2.mp4
C:\Users\XXX\BIN\MEDIA\1612041791171084288_2.mp4
C:\Users\XXX\BIN\MEDIA\1612425997130911747_2.mp4

Column B looks like:

https://twitter.com/user/status/1611717485828489221?s=12&t=HFVIWyICVNxavFltOixxVg
https://twitter.com/user/status/1612425997130911747?s=12&t=gU4QIpi1peMHASLbiRGKjA
https://twitter.com/user/status/1612831672805855232?s=12&t=IQ_M-PpP05ylmIRsBjlIfg
https://twitter.com/user/status/1604835107759853568?s=12&t=wzY0CTl6lyINeaFxisyZcQ

I want to reorder column A so that entries match up with the corresponding URL that contains the filename between /status/ and ?s=12&t.

Does that make sense / is that possible?

Thanks!

I tried match and v lookup but it only gives me a true / false. Would appreciate any help here :)

CodePudding user response：

Not sure you have provided us the best possible sample data. Just one of these values in column A seem to have a match:

Formula in C1:

=XLOOKUP("*/"&LEFT(TEXTAFTER(A1:A4,"\",5),19)&"~?*",B1:B4,B1:B4,"Not Found",2)

Or, vice versa:

Formula in C1:

=XLOOKUP("*\"&LEFT(TEXTAFTER(B1:B4,"/",5),19)&"_*",A1:A4,A1:A4,"Not Found",2)

CodePudding user response：

Slightly different approach - same result :-)

=LET(files,A1:A4,
URLs,B1:B4,
f,BYROW(files,LAMBDA(r,TEXTSPLIT(TAKE(TEXTSPLIT(r,"\"),,-1),"_"))),
XLOOKUP("*/" & f &"~?*",URLs,URLs,"not found",2))

Compared to JvdVs solution, this one is independant of the number of subfolders of the path.