 A different order for a part is in each row. To get the average number of parts ordered, which is the quantity of each order, I need to count how many of each order there are. I need some formula or vba solutions to automate this because I have a lot of rows.

I need to provide the results in a single line, removing all the individual order lines after I have the number of orders and the average number of parts per order filled in.

I'm searching for guidance on how to go about accomplishing this. I appreciate your thoughts and time.

Although I'm still working on this, I'm not sure if VBA is the only option for getting what I need. I was attempting to construct a range based on the part name matching. One problem is that the code skips a cell when the partname is different from the value currently in that cell before fixing the problem, leaving gaps. Additionally, once I've constructed the range, I'm not sure how to just average the third column within the range.

```    Sub aveCount()

Dim rng As Range
Dim cl As Range
Dim partName As String
Dim ws As Worksheet
Dim count As Double
Dim orders As Double
Dim i As Integer

Set ws = ActiveWorkbook.Worksheets("Sheet1")
'lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
i = 0
For Each cl In ws.Range("A89:A433")
If i = 0 Then
partName = cl.Value
End If

If cl.Value = partName Then
i = i + 1

If rng Is Nothing Then
Else
Set rng = Union(rng, ws.Range(cl.Address).Resize(, 4))
End If
Else
i = 0
End If
count = rng.Rows.count
Stop

Next cl 'next row essentially

End Sub```
Jan 17 in Others 47 views

## 1 answer to this question.

Try this:

```Sub aveCount()

Dim rng As Range
Dim cl As Range
Dim partName As String
Dim ws As Worksheet
Dim count As Double
Dim orders As Double
Dim i As Integer

Set ws = ActiveWorkbook.Worksheets("Sheet1")
'lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
'initializing the variable

i = 1
For Each cl In ws.Range(startAddress & ":A433")
If cl.Value = cl.Offset(1, 0).Value Then
i = i + 1
Debug.Print (i)
If rng Is Nothing Then
orders = cl.Offset(0, 2).Value
Else
Set rng = Union(rng, ws.Range(cl.Address).Resize(, 4))
orders = orders + cl.Offset(0, 2).Value
End If
Debug.Print (orders)
Else
orders = orders + cl.Offset(0, 2).Value
Debug.Print (orders)
ws.Range(startAddress).Offset(0, 4) = orders / i
i = 1
End If

Next cl 'next row essentially

End Sub```
• 53,920 points

## Company will be able to quickly issue SIM to user and expected gain in volume is approximately 10 times as the manual process of verification is replaced with secure automated system

LifeTel Telecom is the latest entrant in the ...READ MORE

## Why AWS recommends to avoid the use of public/internet gateways in favor of AWS PrivateLink and VPC endpoints?

A VPC endpoint enables connections between a ...READ MORE

## How to hide and unhide the columns of an excel sheet using asp.net

Use this. For Row: worksheet_sub.Row(i).Height = 0; For Column: ...READ MORE

## How to format numbers as lakhs and crores in excel/ google spreadsheet when the number could be negative too?

Excel formatting, in my opinion, can only ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

## Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

## Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE