Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulagoogle-query-languagenested-if

Pick unique values from two non-adjacent columns

Please see the attached sample: https://docs.google.com/spreadsheets/d/1m625-WxG9VBv5AG-VR9-pf4bUCcG8AMCrb_z3jMYCqo/edit#gid=0

Columns A,B,D and E are my source columns. I want to pick unique values from columns B and E for each date into a dynamic table (Columns G, H and I). As shown in columns H and I, unique values from each column are to be adjacent to each other.

I have tried using the following formula: =unique(filter(A2:E,{1,1,0,1,1})) However, this returns two separate columns for dates.

Solution

  • try:

    =ARRAYFORMULA(UNIQUE(QUERY({
 A2:B, IFERROR(B2:B/0, B1), ROW(B2:B); 
 D2:E, IFERROR(E2:E/0, E1), ROW(B2:B)}, 
 "select Col1,max(Col2) 
  where Col2 is not null 
  group by Col1,Col4 
  pivot Col3 
  order by Col1 desc")))

    enter image description here