Multiple Criteria in Filter-CodePudding

Is there a way I can do multiple criteria inside filter function?

In the picture above, I only want to filter Column D (From Table 1) the duration of their calls ONLY and that would show on Table 2. The problem is that there are other data or value (random characters sometimes) in that single cell in Column D which you can see is row merged (In Table 1), I wanted to exclude everything inside except for the duration which is highlighted in yellow so I can make everything unmerged and final output will show in Table 2. Is this possible in Filter function?

Current Formula I'm using in Cell I3

=HSTACK(A3:A23,B3:B23,C3:C23,D3:D23,F3:F23)

CodePudding user response：

UPDATED

Use this formula to 'extract' the values that are time.

=TEXT(FILTER(D3:D23,IFERROR(TIMEVALUE(TEXT(D3:D23,"[h]:mm:ss")),"")<1),"[h]:mm:ss")

TIMEVALUE will return a #VALUE if the cell is not of time formatting. Taking advantage of this will allow you to ignore all non-time cells.

If you are attempting to retain the row position then this should work as a whole:

=HSTACK(A3:A23,B3:B23,C3:C23,TEXT(IFERROR(TIMEVALUE(TEXT(D3:D23,"[h]:mm:ss")),""),"[h]:mm:ss"),F3:F23)

COMMENT UPDATE

I think this should work for filtering out all data where Column D does not have a time format or include a -.

=HSTACK(FILTER(A3:C23,A3:A23<>0),FILTER(D3:D23,(D3:D23="-") (IFERROR(TIMEVALUE(TEXT(D3:D23,"[h]:mm:ss")),0)>0)))

CodePudding user response：

Filter Data With Merged Cells

EDIT

My sample data was too simple. Added CleanTimeCol.

=LET(array,A3:D23,
    TimeCol,TAKE(array,,-1),CleanTimeCol,IF(ISNUMBER(TimeCol),TimeCol,"-"),MergeShift,2,
        TimeArray,IFERROR(INDEX(CleanTimeCol,SEQUENCE(ROWS(array),,1 MergeShift)),"-"),
        FilterArray,HSTACK(DROP(array,,-1),TimeArray),
    FirstCol,TAKE(array,,1),
        FilterInclude,FirstCol<>"",
            FILTER(FilterArray,FilterInclude))

Formula working for the data in this post's screenshot:

=LET(array,A3:D23,
    TimeCol,TAKE(array,,-1),MergeShift,2,
        TimeArray,IFERROR(INDEX(TimeCol,SEQUENCE(ROWS(array),,1 MergeShift)),"-"),
        FilterArray,HSTACK(DROP(array,,-1),TimeArray),
    FirstCol,TAKE(array,,1),
        FilterInclude,FirstCol<>"",
            FILTER(FilterArray,FilterInclude))

array,A3:D23 - all data
TimeCol,TAKE(array,,-1) - the last (time) column
MergeShift,2 - a constant to cover for merged columns
TimeArray,IFERROR(INDEX(TimeCol,SEQUENCE(ROWS(array),,1 MergeShift)),"-") - SEQUENCE will produce 3,4,5,...23 (21 rows), INDEX will return errors for rows 20 and 21 (for the numbers 22 and 23), and IFERROR will replace them with dashes
FilterArray,HSTACK(DROP(array,,-1),TimeArray) - stacking the first 3 columns and the time array producing the 1st FILTER parameter
FirstCol,TAKE(array,,1) - the first column
FilterInclude,FirstCol<>"" - first column non-blanks producing the 2nd FILTER parameter
FILTER(FilterArray,FilterInclude) - the FILTER formula