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

  • Welcome. Please read how to and share a test sheet so as you can be easier helped. – marikamitsos Nov 18 at 17:30
  • Updated. Thanks! – Kevin Handy Nov 18 at 17:48
  • Sorry but I still see 36000+ rows and columns extending to AC. Not a usable sheet. :( – marikamitsos Nov 18 at 17:52
  • Imagine the API docs. lol There's a reason I'm trying to do it through Sheets! – Kevin Handy Nov 18 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 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.