How to match only Max Freq by WO group


#1

Hi there
Anyone knows how to query WO and WO Quantity base on Max freq
image


#2

What’s the condition for max freq?


#3

I would like to pull data in column I and J by Max Freq. and By Group I


#4

So how about a mix of order by max freq and group by I?


#5

Order is not important here


#6

As long as I can group it by I and filter to only freq max.


#7

Issue solved. There are 2 parts to get this. First use query to pull max freq. Second use array vlookup formula. That will get what u want

=iferror(SORT({(UNIQUE(‘Cycle Time Table’!T2:T)),ArrayFormula(vlookup(UNIQUE(‘Cycle Time Table’!T2:T),sort(query(‘Cycle Time Table’!T:V,“SELECT T,V, COUNT(T) WHERE T IS NOT NULL GROUP BY V,T ORDER BY T ASC LABEL V ‘WO QTY’, COUNT(T) ‘QTY FREQ’”),2,false),2,false))},1,TRUE))


#8

@desmondlee thanks for sharing your solution!