I have a question for advanced Excel users that none of the AI applications were able to help me with.

[iccy123]

I am trying to create a formula or something with VBA to help me sort five columns of data.

I’ll briefly explain – I use video games to learn languages, and I’m trying to create a multi-subtitle system for Baldur’s Gate 3. The text is organized via Content UIDs (for all languages). Unfortunately, in each language file the UIDs are not ordered the same, so I am unable to sort and align them to combine them into one cell.

Here is an example of the cells and the UIDs:

<content contentuid="h000006d4gcefbg4092gbb39gfeb27a3bb0a7" version="1">Sorry, darling, I haven't got time for underlings.</content>

<content contentuid="h000006d4gcefbg4092gbb39gfeb27a3bb0a7" version="1">抱歉，亲爱的，我可没时间搭理下属。</content>

<content contentuid="h000006d4gcefbg4092gbb39gfeb27a3bb0a7" version="1">Lo siento, cielo, no tengo tiempo para subordinados.</content>

<content contentuid="h000006d4gcefbg4092gbb39gfeb27a3bb0a7" version="1">Прости, моя радость, со слугами не разговариваю.</content>

<content contentuid="h000006d4gcefbg4092gbb39gfeb27a3bb0a7" version="1">Désolé, très cher, je n'ai pas de temps à perdre avec les sous-fifres.</content>

And here is what I want it to look like :

<content contentuid="h000006d4gcefbg4092gbb39gfeb27a3bb0a7" version="1">Sorry, darling, I haven't got time for underlings. || RU: Извините, дорогая, у меня нет времени для подчиненных. || ES: Lo siento, cariño, no tengo tiempo para los subordinados. || FR: Désolé, chérie, je n'ai pas le temps pour les subalternes. || ZH: 抱歉，亲爱的，我没有时间的下降时间。</content>

There are over 200,000 of these lines of dialogue. My question - is there any way to have the other four columns (B-E) align in order using Column A as a reference using the unique UIDs?

Here is an example Google Sheet of what it currently looks like (and want I want it to look like):

Untitled spreadsheet

Sheet1 English,Chinese,Spanish,Russian,French <content contentuid="h000006d4gcefbg4092gbb39gfeb27a3bb0a7" version="1">Sorry, darling, I haven't got time for underlings.</content>,<content contentuid="h000006d4gcefbg4092gbb39gfeb27a3bb0a7" version="1">抱歉，亲爱的，我可没时间搭理下属。</content>,<content contentu...

docs.google.com

Hopefully someone knows how to do this, because AI is not up to the task yet!

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: I have a question for advanced Excel users that none of the AI applications were able to h
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.