CODEWITHSUNDEEP
arraysexcel
Lou
Lou

Reputation: 2519

How to return an array matching a certain criteria in Excel?

I'm trying to parse an XML file in Excel, which is a Japanese dictionary. It contains several translations of each entry into different languages, and some entries have multiple translations per language. I want to write a formula that finds all of the translations by their language code, returns them as an array, and concatenates them using a TEXTJOIN formula. But I don't know how to go about this in Excel.

In Google Sheets, this would be easily solved by a FILTER function, but I can't use Sheets as there's too much data, and I haven't managed to get access to the beta FILTER function yet.

In the below picture, I'm trying to return the values in the <gloss xml:lang*> column, by searching for the values in the lang column. So for example, I want to return all values which have a "dut" next to them, and concatenate those into a single line using TEXTJOIN.

Any idea how I could go about doing this?

enter image description here

Upvotes: 2

Views: 1544

Answers (1)

Lou
Lou

Reputation: 2519

I fixed this by downloading the FILTER function. This is part of the Office Insider program, which releases Beta features if you choose to participate. You can access the Insider program by going to File > Account > Office Insider. Then to update your Office version go to File > Account > Office Updates to install the Insider update.

To filter the list by the "lang" column the formula looked like:

=FILTER([range in H column], [range in I column]=T$2)

I haven't specified either range because I used a formula-defined range using the INDIRECT function to avoid filtering through a million rows. The H range is what I want in the results of the filter, the I range is what I want to filter by - the "lang" code. T$2 represents the "lang" code, in this case "dut", and when I copy it across it will filter by each of the 8 lang codes in Row 2.

Then I used TEXTJOIN to combine it the array result into one column using the comma separator:

=TEXTJOIN(", ", TRUE, FILTER(...))

Upvotes: 2

Related Questions