Google Sheets – Combine Multiple Results of Two Filter Formulas

concatenategoogle sheetsgoogle-sheets-filter

Share Link: https://docs.google.com/spreadsheets/d/1-SoQS5Y4EvExbYTOjBGdx7EAq4OYlS0sPENFsblrTr4/edit?usp=sharing

Formula Currently: =CONCATENATE("[",FILTER('BLS - Occupational Employment Statistics'!B:B,'BLS - Occupational Employment Statistics'!H:H=A2),",",FILTER('BLS - Occupational Employment Statistics'!Z:Z,'BLS - Occupational Employment Statistics'!H:H=A2),"]")

Situation

I have a spreadsheet from the Department of Labor. In in is the wages for all the careers they track broken out by state. The careers are signified with a SOC Code.

What I need is to create a list of what the median annual wage is per state for each career.

So I run a FILTER on the list to give me the state name for anything that matches the specific job code for that row. It gives me a list of them in separate cells. I then run a second FILTER to get the wages. I get those in a list in separate cells as well.

I need these combined together (ie [Arizona,54000], [Virginia,60000], etc.).

Next I put those two FILTERs into CONCATENATE and add the brackets and comma needed. Now I get [the entire list of FILTER 1, the entire list of FILTER 2] when I need [FILTER 1 result 1,FILTER 2 result 1] and so forth.

How would I get the desired results?

BONUS QUESTION: I really need these arrays separated by commas themselves without one at the end. So CONCATENATE isn't exactly my preference. I hoped that I could create a list of these individual arrays, TRANSPOSE them so they're on the horizontal axis, the run a JOIN with the comma as a delimiter. Is there a way to incorporate that into this formula or at least get the results in separate cells so I can then run the JOIN?

EDIT:
I was able to find a solution on the Google forums. Here's the updated code:
=arrayformula( "[" & filter('BLS - Occupational Employment Statistics'!B:B, 'BLS - Occupational Employment Statistics'!H:H = A2) & "," & filter('BLS - Occupational Employment Statistics'!Z:Z,'BLS - Occupational Employment Statistics'!H:H = A2) & "]" )

Best Answer

I was able to find a solution on the Google forums. Here's the updated code:

 =arrayformula( "[" & filter('BLS - Occupational Employment Statistics'!B:B, 'BLS - Occupational Employment Statistics'!H:H = A2) & "," & filter('BLS - Occupational Employment Statistics'!Z:Z,'BLS - Occupational Employment Statistics'!H:H = A2) & "]" )