I have an excel holding the translations for my app, where the base language is english. Inside the app, the same label could be used in different modules, therefore the same english word could appear several time.
Here's a compact example:
as you can see, the word "cat" and "car" are listed multiple times in the excel file, and sometimes they may be already translated but, due to the size of the file (thousand of rows), that's not easy to spot.
I'd like to avoid the re-translation of already translated labels, therefore I'm looking for an excel formula that, given a key (1st column) which has not been translated yet, looks through the entire document to check if that key has been already translated, somewhere else. If so, I need to "fill the gaps" with the right translation.
I was thinking to combine a VLOOKUP with some other construct, but I'm stuck trying to get the first cell with a non empty value, if any. Also, I don't know how to handle duplicates (for example, "cat" could have been translated as "gatto" or "micio" in italian in different rows), it may be enough to take the 1st translation available, not caring about the others.
Can anyone help? thanks in advance
CodePudding user response：
No script needed, just do the following 4 steps below. (The sorting is so that the empty translations get deleted)
- Select the whole list
- Go to Data tab and click on Sort
- Sort first column ascending (A-Z) and the second column descending (Z-A)
- In Data tab, click on delete duplicates and select the first column and then OK
CodePudding user response：
This is to keep your original list unchanged and just to fill the empty cells with the first appearance of a translation of the same word. Multiple translations remain unchanged.
You may want to adjust it to more specific conditions as needed:
=LET(filteredList;FILTER(B$2:B$19;NOT(ISBLANK(B$2:B$19))*(A$2:A$19=A2));firstItem;INDEX(filteredList;1);IF(ISBLANK(B2);IFERROR(firstItem;"(no translation)");B2))
- filteredList is the array with the translations of the same word
- firstItem is the first item of that array
