CODEWITHSUNDEEP
excelexcel-formula
Harvey
Harvey

Reputation: 137

Multiple Criteria in Filter

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

enter image description here

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)

Upvotes: 2

Views: 661

Answers (3)

P.b
P.b

Reputation: 11653

In case of fixed height merge

=LET(merged,A2:F23,
     seq,SEQUENCE(1+MAX(ROW(merged))-MIN(ROW(merged)),,MIN(ROW(merged))),
     condition,DROP(REDUCE(0,seq,LAMBDA(x,y, VSTACK(x,(IFERROR(1/(INDEX(A:A,y)<>"")/(INDEX(A:A,y+1)="")/(INDEX(A:A,y+2)="")+1,0))))),1),
     columnD,INDEX(INDEX(merged,,4),1+ROW(merged)-MIN(ROW(merged))+condition),
     flt,FILTER(HSTACK(TAKE(merged,,3),columnD,TAKE(merged,,-1)),CHOOSECOLS(merged,1)<>""),
IF(flt=0,"",flt))

enter image description here

This first takes the whole range of data including merged cells and names it merged.

Then a sequence is created of it's row numbers: seq

Then it checks column A row by row as defined in seq if that row contains value and the next two rows don't. If so it returns 1, if not 0. This array is named condition and is used to show the value in column D from 2 rows below if the condition-array equals 1 and the current row if the it equals 0. which results in columnD

If we then stack the merged range columns prior to the altered columnD and the last merged range column and filter out the rows where the first merged range column has blank values you get your result: flt. to avoid blank cells to show as 0 I added IF(flt=0,"",flt).

You can choose to include or exclude the header from your data. It works both ways.

in case of variable height merge

If the merged cells are not per definition 3 merged rows, the following formula works:

=LET(merged,A2:F23,
     r,ROWS(merged),
     s,SEQUENCE(r),
     shiftrow,MAP(s,s+1,LAMBDA(a,b,LET(remainder,INDEX(merged,SEQUENCE(r-a,,b),1),(INDEX(merged,a,1)<>"")*(IFERROR(INDEX(merged,b,1)="",0))*IFERROR(XMATCH("*",remainder,2)-1,IFERROR(ROWS(remainder),0))))),
     d,INDEX(merged,s+shiftrow,4),
     mf,HSTACK(TAKE(merged,,3),d,TAKE(merged,,-1)),
     unmerged,FILTER(mf,TAKE(mf,,1)<>""),
IF(unmerged=0,"",unmerged))

enter image description here

This first takes in the whole range merged, then shiftrow checks if the cell in the current row c contains a value and the value in the next row n is blank, it returns the number of rows the next non-blank cell is found from the next row -1 as a value. If shiftrow returns TRUE it checks for the first non-empty cell from the next row n to the last row; if shiftrow returns FALSE it returns 0

d returns the column D indexed in it's rows (s = sequence(rows(merged)))) + the shiftrow value.

mf combines the merged range columns A:C with the fixed values in column D d and the last column of the merged range.

unmerged filters out the rows with empty values in the first column from mf

Finally 0 is replaced with blanks.

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54948

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))

enter image description here

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

Upvotes: 2

Kevin P.
Kevin P.

Reputation: 1053

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)))

Upvotes: 2

Related Questions