# How to get the median value faster

Using Excel, I have one sheet ("db") 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("db").Select 'sheet with database

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ThisWorkbook.FullName & "; Extended Properties=""Excel 8.0;HDR=Yes;"";"

' select field from db
strsql = "SELECT Articolo, Fase, [Tipo Confezione], TURNO, Macchina, avg(KgOra) as med FROM [db$] where Time>0 group by Articolo, Fase, [Tipo Confezione], TURNO, Macchina ;" rs.Open strsql, cn, adOpenStatic, adLockReadOnly, adCmdUnspecified rs.MoveFirst Sheets("pvt").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("A2").CopyFromRecordset rs 'set range and get data from pasted recordset Set RangeVal = Range("A1", Cells(Range("A1").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 = "select last(KgOra) FROM (select top 50 percent KgOra from [db$] where " & _
"Articolo = '" & DatiVal(i, 1) & _
"' and Fase = '" & DatiVal(i, 2) & _
"' and [Tipo Confezione] = '" & DatiVal(i, 3) & _
"' and TURNO = '" & DatiVal(i, 4) & _
"' and Macchina = '" & DatiVal(i, 5) & "');"

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

• Note that this is exactly what pivot tables are supposed to do, although that's getting off-topic for this site. I.e. I think that if you asked on an Excel-specific site, I think that their advice would be to stop using VBA/SQL altogether and just use the built-in pivot table functionality. Example search result: masterdataanalysis.com/ms-excel/… Nov 4 '20 at 14:02

## 2 Answers

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

1. Collect unique Articolo/fase/tipo/etc values

2. Issue the queries only once for each combination

3. Update median values

I don't use VBA in Excel, so I can't make specific code suggestions. But given this problem, I would be looking to create a new sheet.

On the new sheet, group your data by "Articolo, Fase, Tipo Confezione, TURNO, Macchina". And store the value that you are using to generate the average and median (it looks to be kgOra).

Then just generate the average and median for each group of data.

The problem that you are hitting is that you are scanning the entire table repeatedly to extract values. Stop doing that. Instead, sort the values once (into a new sheet). Then do your work on ranges. Once you've generated the new sheet, I would skip the SQL altogether. It should be easier to work performantly with just the ranges.

Note: if this weren't Excel, I would be suggesting a temporary table with an index. But as I said, in Excel, I think that it will be more efficient to work with the cells directly. An alternative solution would be to import the data into SQL Server or Access and create your temporary table there.