CODEWITHSUNDEEP
arraysgoogle-sheetsfilteruniquegoogle-sheets-formula
Digital Farmer
Digital Farmer

Reputation: 2107

Merge two formulas into one

=UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),TO_TEXT({E2:E;H2:H})<>"")))

enter image description here

=IFERROR(UNIQUE(FILTER(P2:R,(text(P2:P,"yyyy/mm/dd")&" "&TEXT(Q2:Q,"hh:mm"))>$A$1,(text(P2:P,"yyyy/mm/dd")&" "&TEXT(Q2:Q,"hh:mm"))<TEXT(($A$1+"24:00"),"yyyy/mm/dd hh:mm"))))

enter image description here

I tried in many ways to join these two formulas, but in all attempts, returned in error, could not deliver the values within the calculation.

I would like to know if it would be possible to merge these two formulas into one ... I need to merge the data that are in the columns E F G & H I J and from that data filter only those that have not started and that will start within 24 hours maximum.

I'll leave the spreadsheet link if it makes it easy to look directly at it the general scenario:
https://docs.google.com/spreadsheets/d/1jjLTmfUZUmlAF2j9jXJEKRkAefo922OlMrxhUYBwdP0/edit?usp=sharing

Upvotes: 1

Views: 160

Answers (1)

player0
player0

Reputation: 1

try:

={UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),TO_TEXT({E2:E;H2:H})<>""))); 
 IFERROR(UNIQUE(FILTER(UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),
 TO_TEXT({E2:E;H2:H})<>""))),
 (TEXT(INDEX(UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),
 TO_TEXT({E2:E;H2:H})<>""))),,1),"yyyy/mm/dd")&" "&
 TEXT(INDEX(UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),
 TO_TEXT({E2:E;H2:H})<>""))),,2),"hh:mm"))>$A$1,
 (TEXT(INDEX(UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),
 TO_TEXT({E2:E;H2:H})<>""))),,1),"yyyy/mm/dd")&" "&
 TEXT(INDEX(UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),
 TO_TEXT({E2:E;H2:H})<>""))),,2),"hh:mm"))<
 TEXT(($A$1+"24:00"),"yyyy/mm/dd hh:mm"))))}

0

or maybe you just want:

=IFERROR(UNIQUE(FILTER(UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),
 TO_TEXT({E2:E;H2:H})<>""))),
 (TEXT(INDEX(UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),
 TO_TEXT({E2:E;H2:H})<>""))),,1),"yyyy/mm/dd")&" "&
 TEXT(INDEX(UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),
 TO_TEXT({E2:E;H2:H})<>""))),,2),"hh:mm"))>$A$1,
 (TEXT(INDEX(UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),
 TO_TEXT({E2:E;H2:H})<>""))),,1),"yyyy/mm/dd")&" "&
 TEXT(INDEX(UNIQUE(IFERROR(FILTER(TO_TEXT({E2:G;H2:J}),
 TO_TEXT({E2:E;H2:H})<>""))),,2),"hh:mm"))<
 TEXT(($A$1+"24:00"),"yyyy/mm/dd hh:mm"))))

Upvotes: 1

Related Questions