0

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) & "]" )

5
  • Welcome. Please read how to and share a test sheet so as you can be easier helped. Nov 18, 2020 at 17:30
  • Updated. Thanks! Nov 18, 2020 at 17:48
  • Sorry but I still see 36000+ rows and columns extending to AC. Not a usable sheet. :( Nov 18, 2020 at 17:52
  • Imagine the API docs. lol There's a reason I'm trying to do it through Sheets! Nov 18, 2020 at 19:40
  • 1
    Welcome. It looks as though you have found an answer to your own question. Congrats. For the benefit of others, would you submit an answer to your question. It may help another user since it will flag your question as having been answered, If you're in any doubt see Can I answer my own question?.
    – Tedinoz
    Nov 18, 2020 at 21:33

1 Answer 1

0

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) & "]" )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.