1

Let Column A = [D, A, A, B, A, C, B, D, D, D, E, E]
Let Column B = [A, B, C, D]

=FILTER(A1:A10, B1:B4=$B1)

The problem is, when you drag and apply the above formula, because there are multiple results, you can't necessarily use that right away and have to do it separately and manually like 

   C1=FILTER(A1:A10, B1:B4=B1)
   C4=FILTER(A1:A10, B1:B4=B2)
   C6=FILTER(A1:A10, B1:B4=B3)
   C7=FILTER(A1:A10, B1:B4=B4)
hiimarksman
  • 261
  • 2
  • 3
  • 12
  • Don't think Excel got any such funtion to be honest. Don't you meant to tag GS? – JvdV Aug 05 '19 at 15:26
  • Thank you for pointing that out. Yes, this is in GS. It would be nice to come up with a mechanism of displaying all the results instead of typing it one by one due to multiple rows populating – hiimarksman Aug 05 '19 at 15:28
  • Possible duplicate of [Google Spreadsheets: Filter a range by array](https://stackoverflow.com/questions/11029737/google-spreadsheets-filter-a-range-by-array) – Scott Craner Aug 05 '19 at 15:39

1 Answers1

0

correct syntax would be:

={FILTER(A1:A, A1:A=B1);
  FILTER(A1:A, A1:A=B2);
  FILTER(A1:A, A1:A=B3);
  FILTER(A1:A, A1:A=B4)}

0

but then you could just use:

=SORT(A1:A)

0

sorting by custom order - whole array:

=SORT(A1:A, MATCH(A1:A, B1:B, 0), 1)

0

sorting by custom order inclusive:

=FILTER(SORT(A1:A, MATCH(A1:A, B1:B, 0), 1), 
 REGEXMATCH(SORT(A1:A, MATCH(A1:A, B1:B, 0), 1), TEXTJOIN("|", 1, B1:B)))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks! Just a couple details I should've specified, what if Column B is not a known fixed list? i.e. sometimes it may contain 3 elements, other times 5, etc. Sort wouldn't work in this context since there are some elements in column B not necessarily in column A (I edited to reflect this). – hiimarksman Aug 05 '19 at 19:39