2

can you please help?

I would like to autopopulate column C with values in column B sorted by the priority in column A.

Column A: Project names manually ordered by priority. Most important in top.

Column B: Mixed tasks from multiple projects.

Column C: Expected results.

enter image description here

1

1 Answer 1

3

Here's one way to do it. We need to strip the -0 off of the input values, but otherwise its fairly straightforward. If you have FREAK-2 and FREAK-10, this will sort them as 2 and 10 instead of 10 then 2.

=ARRAYFORMULA(
  IFERROR(
   QUERY(
    {B2:B,
     IFERROR(
      REGEXEXTRACT(B2:B,"(.*)-")&"-"&
      TEXT(
       --REGEXEXTRACT(B2:B,"-(\d+)"),
       "000")),
     IFERROR(
      VLOOKUP(
      REGEXEXTRACT(B2:B,"(.*)-")&"*",
       {A2:A,ROW(A2:A)},
       2,FALSE),99)},
   "select Col1
    where
     Col1 is not null and
     Col3 >0
    order by Col3, Col2",0)))

working demo

5
  • yeah, @daniele, without a match it'll revert to zero via the IFERROR. Did you want these values to go to the bottom of the list or disappear all together? I added a second formula for you to try. It won't return the values without a match.
    – tomf
    Jun 9 at 7:39
  • try the sheet I linked to at the bottom of the post. With the revised formula, anything without a match will not show up. In the sheet I included a second option that will give all non-matches a priority of 99, so they're at the bottom sorted alphabetically. Not sure why this response is downvoted.
    – tomf
    Jun 9 at 7:50
  • 1
    I think that now it's OK :)
    – Daniele
    Jun 9 at 7:54
  • 1
    Beautiful! Thanks you so much! :D
    – Szpiep
    Jun 10 at 6:10
  • happy to help! Thanks to Daniele for helping me refine it, too! :)
    – tomf
    Jun 10 at 8:54

Your Answer

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

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