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


--
Helpful advice on posting to newsgroups here...
http://www.cpearson.com/excel/newposte.htm

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

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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164963

Microsoft Office Help

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

--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"pswanie" wrote:

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


--
Helpful advice on posting to newsgroups here...
http://www.cpearson.com/excel/newposte.htm