Form Populate unique Totals

[Emoncada]

I have a frmTotals that I would like for it to come up before closing spreadsheet.I would like for it to do this.

I need it to look at column A:A and count how many unique orders there are and for those orders then Look at column H:H and count how many of them are "DEPOT", "MORTGAGE", "VAM".

FrmTest

LblDepotTotal = 'Total of Unique orders that are "DEPOT"
LblMORTGAGETotal = 'Total of Unique orders that are "MORTGAGE"
LblVAMTotal = 'Total of Unique orders that are "VAM"

Any Help would be great.

[RonMcK]

Emoncada,

Please upload a worksheet showing what the data records look like, particularly in Columns A and H, and showing us your Form and how you have tried to code this.

Ron

[mikerickson]

The number of unique items in A1:A10
=SUMPRODUCT(--(COUNTIF(A:A,A1:A10)=1))

The number of rows that have something unique in A and "cow" in H
=SUMPRODUCT(--(COUNTIF(A:A,A1:A10)=1),--(H1:H10="cow"))

The A1:A10 and H1:H10 can be replaced by dynamic ranges of any size, but they must be limited (i.e. not the whole column A:A).

[Emoncada]

mikerickson that doesn't seem to work I got all zero's.
Column A had various order numbers if that matters.

Example
COLUMN A

DPT20080109-TR2141
20080109-DS3521

COLUMN H

DEPOT
DEPOT
VAM
DEPOT
MORTGAGE

Is there something I forgot?

[Emoncada]

OK it works if I manually input the data on the spreadsheet.
I forgot to mention this data that goes on the spreadsheet comes directly from a Form. It doesn't seem to pick up that data.

[Emoncada]

Is there a way to make that formula run from a script?

[mikerickson]

It should work fine if the A:A and H:H is populated by userform. Is your calculation set to manual?

[Emoncada]

No it's set to Automatic.

[mikerickson]

I've been away for a couple of days.

Hmmm...the only thing I can think of is events might be diasbled..(but that shouldn't cause that behaviour)

Can you post the UF code that populates the cells? Perhaps adding a
Calculate
line will fix things.

[Emoncada]

Well this is what I got

UF Code
Private Sub UserForm_Initialize()
FrmTotals.LblTotalDepot.Caption = Range("$R$1")
FrmTotals.LblTotalVam.Caption = Range("$S$1")
FrmTotals.LblTotalMortgage.Caption = Range("$T$1")
FrmTotals.LblTotalServiceReq.Caption = Range("$U$1")
End Sub

And the Spreadsheet Code is

In Q1

=SUMPRODUCT(--(COUNTIF(A:A,A2:A999)=1))

IN R1
=SUMPRODUCT(--(COUNTIF(A:A,$A$2:$A$999)=1),--($H$2:$H$999="DEPOT"))

IN S1
=SUMPRODUCT(--(COUNTIF(A:A,$A$2:$A$999)=1),--($H$2:$H$999="VAM"))


In T1
=SUMPRODUCT(--(COUNTIF(A:A,$A$2:$A$999)=1),--($H$2:$H$999="MORTGAGE"))

iS THERE ANY WAY TO MAKE THIS WORK JUST VIA VB?

[Emoncada]

bump

[Emoncada]

This isn't working great with the Userform. It works without the userform but very slow and it looks like it slows down the Userform process also. Is there a way to have this run from vb or at least have it calculate totals before close?

[mikerickson]

No, there is no way to make those formulas update the labels on the Userform.
The UF's labels will need to be explicitly refreshed everytime the UF writes to the sheet.

[Emoncada]

Is there a worksheet refresh function that can do that?

[mikerickson]

You could put this in ThisWorkbook's Calculate event.

If Frm.Totals.Visible Then
FrmTotals.LblTotalDepot.Caption = Range("$R$1")
FrmTotals.LblTotalVam.Caption = Range("$S$1")
FrmTotals.LblTotalMortgage.Caption = Range("$T$1")
FrmTotals.LblTotalServiceReq.Caption = Range("$U$1")
End If

(You'll have to qualify the ranges to meet your situation.)

[Emoncada]

What do you mean by

(You'll have to qualify the ranges to meet your situation.)

So i would need to change this
=SUMPRODUCT(--(COUNTIF($A:$A,$A$2:$A1000)=1),--($H2:$H1000="VAM"))

To
=SUMPRODUCT(--(COUNTIF($A2:$A1000,$A$2:$A1000)=1),--($H2:$H1000="VAM"))

Or something like that

[mikerickson]

I was refering to the VB code

With ThisWorkbook.Sheets("Sheet1")
If Frm.Totals.Visible Then
FrmTotals.LblTotalDepot.Caption = .Range("$R$1")
FrmTotals.LblTotalVam.Caption = .Range("$S$1")
FrmTotals.LblTotalMortgage.Caption = .Range("$T$1")
FrmTotals.LblTotalServiceReq.Caption = .Range("$U$1")
End If
End With

[Emoncada]

For some reason this isn't working with the form. this is the UF Code that drops the data on the spreadsheet maybe it's something there.


Private Sub CmdPrintSave_Click()
Dim mpLookup As String
Dim mpRange As Range
Dim mpCell As Range
Dim mpFirst As String
Dim mpFind As Long

mpLookup = UserForm1.TxtOrdNum.Text
On Error Resume Next
mpFind = Application.Match(mpLookup, Worksheets("Packing Slip Pim").Columns(1), 0)
On Error GoTo 0
If mpFind = 0 Then

InsertEm
Else

If MsgBox("A Match has been found do you wish do delete previous one(s)?", _
vbYesNo) = vbYes Then

With Worksheets("Packing Slip Pim").Columns(1)

Set mpCell = .Find(mpLookup)
Set mpRange = mpCell
mpFirst = mpRange.Address

Do

Set mpCell = .FindNext(mpCell)
If Not mpCell Is Nothing Then

Set mpRange = Union(mpRange, mpCell)
End If
Loop Until mpCell Is Nothing Or mpCell.Address = mpFirst
End With
InsertEm
If Not mpRange Is Nothing Then mpRange.EntireRow.Delete
End If
End If
If UserForm1.CmbBoxProject.Value = "MORTGAGE" Then Call MortBackUpPS
ActiveWorkbook.Save
'Call SaveBackupCopy
End Sub
Private Sub MakeFolders(fp As String)
FilePath = FilePath & fp
If Dir(FilePath, vbDirectory) = "" Then MkDir FilePath
End Sub

Public Sub InsertEm()
Dim RowNext As Integer, i As Long, j As Long
'last row of data
RowNext = Worksheets("Packing Slip Pim").Cells(Rows.Count, 1).End(xlUp).Row
'Count number of items
j = 0
For i = 1 To 54
If UserForm1.Controls("CmbBoxDesc" & i).Text <> "" Then
j = j + 1
Else
Exit For
End If
Next

For i = 1 To j
With Worksheets("Packing Slip Pim")
With TxtOrdNum
If Left(.Text, 2) = "00" Then
UserForm1.TxtOrdNum.Value = "'" & UCase(UserForm1.TxtOrdNum.Value)
Else
UserForm1.TxtOrdNum.Value = UCase(UserForm1.TxtOrdNum.Value)
End If
End With

.Cells(RowNext + i, 1) = UCase(UserForm1.TxtOrdNum.Value)
.Cells(RowNext + i, 2) = UserForm1.TxtShipDate.Text
.Cells(RowNext + i, 3) = UserForm1.LblShipVia.Caption
.Cells(RowNext + i, 4) = UCase(UserForm1.Controls("TxtTrack" & i).Value)
.Cells(RowNext + i, 5) = UserForm1.Controls("TxtSN" & i).Value
.Cells(RowNext + i, 6) = UserForm1.Controls("CmbBoxDesc" & i).Value
.Cells(RowNext + i, 7) = UserForm1.Controls("TxtQua" & i).Value
.Cells(RowNext + i, 8) = UserForm1.CmbBoxProject.Value
.Cells(RowNext + i, 9) = UserForm1.LblRacf.Caption
.Cells(RowNext + i, 10) = UserForm1.CmbBoxClientName.Value
.Cells(RowNext + i, 11) = UserForm1.CmbBoxLocation.Value
.Cells(RowNext + i, 12) = UserForm1.TxtShippedBy.Text
.Cells(RowNext + i, 13) = UserForm1.TxtComments.Text
If UserForm1.ChkBoxComments = True Then .Cells(RowNext + i, 14) = "YES"
If UserForm1.ChkBoxNewHire = True Then .Cells(RowNext + i, 15) = "YES"
End With

Next
' <> means not equal to
If UserForm1.CmbBoxDesc37.Value <> "" Then
UserForm1.CmdPrintSave.SetFocus
FrmPrint3.Show
Else
UserForm1.CmdPrintSave.SetFocus
FrmPrint2.Show
End If

End Sub

[Emoncada]

I was able to find this formula that seems to work the same and doesn't give me any issues.

=SUM(IF(FREQUENCY(IF($A$2:$A$1000<>"",IF($H$2:$H$1000="DEPOT",MATCH($A$2:$A$1000,$A$2:$A$1000,0))),ROW($A$2:$A$1000)-ROW($A$2)+1),1))

Do you know why this would work and not the other?