How to get the median value faster Using Excel, I have one sheet (&quot;db&quot;) with data extracted from MES.
I have to analyze this data and I need: Articolo, Fase, Tipo Confezione, TURNO, Macchina, avg and median.
I searched everywhere how to get the median with SQL, but I found only how to get median for one item alone.
Then I wrote this code, and I loop each item from 1st query to get data from another query, it works but it is extremely slow!
Is there a better way to get my data or, if my code is the correct way, how I can speed up the job?
Any suggestions are appreciated.
My code:
Sub Get_Pivot()
    Dim strsql As String
    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
    Dim RangeVal As Range, DatiVal
    
    Sheets(&quot;db&quot;).Select 'sheet with database
    
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &amp;H1
    
    Set rs = New ADODB.Recordset
    cn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; &amp; ThisWorkbook.FullName &amp; &quot;; Extended Properties=&quot;&quot;Excel 8.0;HDR=Yes;&quot;&quot;;&quot;
    
   ' select field from db
    strsql = &quot;SELECT Articolo, Fase, [Tipo Confezione], TURNO, Macchina, avg(KgOra) as med FROM [db$] where Time&gt;0 group by Articolo, Fase, [Tipo Confezione], TURNO, Macchina ;&quot;
    rs.Open strsql, cn, adOpenStatic, adLockReadOnly, adCmdUnspecified
    
    rs.MoveFirst
    Sheets(&quot;pvt&quot;).Select  'sheet how to put data
    
    'insert header columns name
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1) = rs.Fields(i).Name
    Next i
    
    'insert data from recordest
    Range(&quot;A2&quot;).CopyFromRecordset rs
      
    'set range and get data from pasted recordset
    Set RangeVal = Range(&quot;A1&quot;, Cells(Range(&quot;A1&quot;).End(xlDown).Row, 7))
    DatiVal = RangeVal.Value
    Set rs = Nothing
    
'loop each row and run one qry to get median
For i = 2 To UBound(DatiVal, 1)
    strsql = &quot;select last(KgOra) FROM (select top 50 percent KgOra from [db$] where &quot; &amp; _
            &quot;Articolo = '&quot; &amp; DatiVal(i, 1) &amp; _
            &quot;' and Fase = '&quot; &amp; DatiVal(i, 2) &amp; _
            &quot;' and [Tipo Confezione] = '&quot; &amp; DatiVal(i, 3) &amp; _
            &quot;' and TURNO = '&quot; &amp; DatiVal(i, 4) &amp; _
            &quot;' and Macchina = '&quot; &amp; DatiVal(i, 5) &amp; &quot;');&quot;
           
    rs.Open strsql, cn, adOpenStatic, adLockReadOnly, adCmdUnspecified
    
'if recordset exist add value at 7th filed of DatiVal
    If Not rs.EOF Then DatiVal(i, 7) = rs(0)
    rs.MoveNext
    Set rs = Nothing
Next

'then paste new data with one columns more(median columns)
whit RangeVal
.vaue = DatiVal
End With

rstErr:
    Set rs = Nothing
    Set cn = Nothing

End Sub
The key issue can be that you run the query with the same parameters multiple times. I assume you have quite many records where Articolo/fase/tipo/etc are the same for you are looking for median. You did not state how many records you have but I think you could reduce the number of issued queries. So - without knowing too much details - I would

Collect unique Articolo/fase/tipo/etc values

Issue the queries only once for each combination

Update median values