checkif/sumif question

[pswanie]

Hi

I have a sheet with names scrambled in sheet1.column A down. column B down
dates, C down items bought, d down price per item.

I want to hit a button and have it summarised on sheet 2. for example...

Emp1 29/12/09 pie 12.50
emp2 2912/09 juice 4.50
emp1 30/12/09 cake 9.50
emp3 27/12/09 fruit 10.50
emp1 25/12/09 pie 12.50
emp1 30/12/09 cake 9.50
emp1 29/12/09 juice 4.50



the summary should look like this

emp1 25/12/09 pie 1 12.50
29/12/09 pie 1 12.50
juice 1 4.50
30/12/09 cake 2 19.00

emp2 29/12/09 juice 1 4.50

emp3 27/12/09 fruit 1 10.50

 

[Bernd P]

Hello,

If you do not want to use a pivot table I suggest to use one of my
UDFs Pfreq (together with Sfreq) or Pstat:
http://sulprobil.com/html/listfreq.html

Regards,
Bernd

 

[joel]

To get you exact format required a lot of code

Sub Employeestotals()

Set SourceSht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")
StartRow = 1

With DestSht
.Cells.ClearContents
.Range("A1") = "Employee"
.Range("B1") = "Data"
.Range("C1") = "Item"
.Range("D1") = "Quantity"
.Range("E1") = "Total"
.Columns("E").NumberFormat = "#,##0.00"
End With

With SourceSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row

'sort data
.Rows(StartRow & ":" & LastRow).Sort _
header:=xlNo, _
key1:=.Range("A" & StartRow), _
order1:=xlAscending, _
key2:=.Range("B" & StartRow), _
order2:=xlAscending, _
key3:=.Range("A" & StartRow), _
order3:=xlAscending

OldEmp = ""
OldDate = ""
OldItem = ""

RowCount = StartRow
NewRow = 1
Do While .Range("A" & RowCount) <> ""
NewEmp = UCase(.Range("A" & RowCount))
NewDate = .Range("B" & RowCount)
NewItem = UCase(.Range("C" & RowCount))
Price = .Range("D" & RowCount)

With DestSht

If NewEmp <> OldEmp Then
NewRow = NewRow + 2
.Range("A" & NewRow) = NewEmp
.Range("B" & NewRow) = NewDate
.Range("C" & NewRow) = NewItem
Quantity = 1
.Range("D" & NewRow) = Quantity
.Range("E" & NewRow) = Price
Else
If NewDate <> OldDate Then
NewRow = NewRow + 1
.Range("B" & NewRow) = NewDate
.Range("C" & NewRow) = NewItem
Quantity = 1
.Range("D" & NewRow) = Quantity
.Range("E" & NewRow) = Price
Else
If NewItem <> OldItem Then
NewRow = NewRow + 1
.Range("C" & NewRow) = NewItem
Quantity = 1
.Range("D" & NewRow) = Quantity
.Range("E" & NewRow) = Price
Else
Quantity = .Range("D" & NewRow)
.Range("D" & NewRow) = Quantity + 1
.Range("E" & NewRow) = _
.Range("E" & NewRow) + Price
End If
End If
End If
End With

OldEmp = NewEmp
OldDate = NewDate
OldItem = NewItem

RowCount = RowCount + 1
Loop
End With

End Su

 

[Paul]

Do you want to perform this with a pivot table, or is there something else
you need to do ?