vba solution
 

The following code does what you require......
it uses a worksheet Sheet2 to add your summary data.

Sub Summarise()
Dim lFRow As Long, lCRow As Long, lSRow As Long
Dim bF4 As Boolean, iCol As Integer, sDate As String

lCRow = 2 ' first row of data
lSRow = 1 ' first report row on summary sheet2

With Worksheets("Sheet1")
Do While lCRow <= .Cells(.Rows.Count, "A").End(xlUp).Row
sDate = .Cells(lCRow, "A")
lFRow = lCRow
bF4 = False
Do While sDate = .Cells(lCRow, "A")
If .Cells(lCRow, 4) 0 Then bF4 = True
lCRow = lCRow + 1
Loop
If bF4 Then
Sheets("Sheet2").Cells(lSRow, 1) = sDate
For iCol = 2 To 5
Sheets("Sheet2").Cells(lSRow, iCol) = _
Application.WorksheetFunction.Sum _
(.Range(.Cells(lFRow, iCol), .Cells(lCRow - 1, iCol)))
Next
lSRow = lSRow + 1
End If
Loop
End With
End Sub

--

Regards,
Nigel




"bijan" wrote in message
...
Hi Nigel
please guide me how can I define a such rule,I have just need to sum
field4
and summerize other fields in one row all with same date.
Many Regards
Bijan

"Nigel" wrote:

Can you expand on the rule for the data to be summarized. You say "Field
4
.... records exist more than one.... and same date"

If there is only one value for field 4 in the same date range then no
action
should take place?
Is it the same dates between the first and last Field4 values, as shown
in
your example?

--

Regards,
Nigel




"bijan" wrote in message
...
Hi Experts,
Happy new year in advance,
I have a sheet with huge data and some of part these data show data
like
this:
__field1_____field2___field3___field4___field5__
1 2008/12/30 1
2 2008/12/30 5
3 2008/12/30 4
4 2008/12/30 2
5 2008/12/30 10
6 2008/12/30 3
I need a vba code first check field4 and if records exist more than one
with
same date sum those data and put other records in to the one row like
this:
__field1_____field2___field3___field4___field5__
1 2008/12/30 2 5 14 4

Thanks
Bijan

vba solution
 

Hi Bijan,

This macro compresses the lines to one for each date.

Sub BijanSum()
Dim intColMax As Integer
Dim intColLoop As Integer

intColMax = 4
Range("A3").Select
Do
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
For intColLoop = 1 To intColMax
With ActiveCell
.Offset(-1, intColLoop).Value = _
.Offset(-1, intColLoop).Value + _
.Offset(0, intColLoop).Value
End With
Next
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)
Range("A2").Select
End Sub

HTH,

Wouter

vba solution
 

Hi RadarEye
Thank you,The code works perfect, but I want to ask another question,if my
sample sheet change like this:
__field1_______field2___field3___field4__
1 2008/12/30 2
2 2008/12/30 2
3 2008/12/30 4
4 2008/12/30 3
5 2008/12/30 10
6 2008/12/30 3
and I have to sum field3 and field4 and then comperess filed2 to show just
first line,How can I change your code to do that and change sheet like this:
__field1_____field2___field3___field4__
1 2008/12/30 2 13 7

Many Regards
Bijan


"RadarEye" wrote:

Hi Bijan,

This macro compresses the lines to one for each date.

Sub BijanSum()
Dim intColMax As Integer
Dim intColLoop As Integer

intColMax = 4
Range("A3").Select
Do
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
For intColLoop = 1 To intColMax
With ActiveCell
.Offset(-1, intColLoop).Value = _
.Offset(-1, intColLoop).Value + _
.Offset(0, intColLoop).Value
End With
Next
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)
Range("A2").Select
End Sub

HTH,

Wouter

vba solution
 

Hi Nigel
Thank you for your code but it omits my titel and previous records.
Thanks
Bijan
"Nigel" wrote:

The following code does what you require......
it uses a worksheet Sheet2 to add your summary data.

Sub Summarise()
Dim lFRow As Long, lCRow As Long, lSRow As Long
Dim bF4 As Boolean, iCol As Integer, sDate As String

lCRow = 2 ' first row of data
lSRow = 1 ' first report row on summary sheet2

With Worksheets("Sheet1")
Do While lCRow <= .Cells(.Rows.Count, "A").End(xlUp).Row
sDate = .Cells(lCRow, "A")
lFRow = lCRow
bF4 = False
Do While sDate = .Cells(lCRow, "A")
If .Cells(lCRow, 4) 0 Then bF4 = True
lCRow = lCRow + 1
Loop
If bF4 Then
Sheets("Sheet2").Cells(lSRow, 1) = sDate
For iCol = 2 To 5
Sheets("Sheet2").Cells(lSRow, iCol) = _
Application.WorksheetFunction.Sum _
(.Range(.Cells(lFRow, iCol), .Cells(lCRow - 1, iCol)))
Next
lSRow = lSRow + 1
End If
Loop
End With
End Sub

--

Regards,
Nigel




"bijan" wrote in message
...
Hi Nigel
please guide me how can I define a such rule,I have just need to sum
field4
and summerize other fields in one row all with same date.
Many Regards
Bijan

"Nigel" wrote:

Can you expand on the rule for the data to be summarized. You say "Field
4
.... records exist more than one.... and same date"

If there is only one value for field 4 in the same date range then no
action
should take place?
Is it the same dates between the first and last Field4 values, as shown
in
your example?

--

Regards,
Nigel




"bijan" wrote in message
...
Hi Experts,
Happy new year in advance,
I have a sheet with huge data and some of part these data show data
like
this:
__field1_____field2___field3___field4___field5__
1 2008/12/30 1
2 2008/12/30 5
3 2008/12/30 4
4 2008/12/30 2
5 2008/12/30 10
6 2008/12/30 3
I need a vba code first check field4 and if records exist more than one
with
same date sum those data and put other records in to the one row like
this:
__field1_____field2___field3___field4___field5__
1 2008/12/30 2 5 14 4

Thanks
Bijan

vba solution
 

On 30 dec, 17:07, bijan wrote:
Hi RadarEye
Thank you,The code works perfect, but I want to ask *another question,if my
sample sheet change like this:
__field1_______field2___field3___field4__
1 2008/12/30 * * *2 * * * * * *
2 2008/12/30 * * *2 * * * * * *
3 2008/12/30 * * * * * * * * * * * * * * * *4
4 2008/12/30 * * * * * * * * * * * * * * * *3
5 2008/12/30 * * * * * * * * *10
6 2008/12/30 * * * * * * * * * 3
and I have to sum field3 and field4 and then comperess filed2 to show just
first line,How can I change your code to do that and change sheet like this:
__field1_____field2___field3___field4__
1 2008/12/30 * *2 * * * * *13 * * * * *7 *

Many Regards
Bijan * * *



"RadarEye" wrote:
Hi Bijan,

This macro compresses the lines to one for each date.

Sub BijanSum()
* * Dim intColMax As Integer
* * Dim intColLoop As Integer

* * intColMax = 4
* * Range("A3").Select
* * Do
* * * * If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
* * * * * * For intColLoop = 1 To intColMax
* * * * * * * * With ActiveCell
* * * * * * * * * * .Offset(-1, intColLoop).Value = _
* * * * * * * * * * .Offset(-1, intColLoop).Value + _
* * * * * * * * * * .Offset(0, intColLoop).Value
* * * * * * * * End With
* * * * * * Next
* * * * * * ActiveCell.EntireRow.Delete
* * * * Else
* * * * * * ActiveCell.Offset(1, 0).Select
* * * * End If
* * Loop Until IsEmpty(ActiveCell)
* * Range("A2").Select
End Sub

HTH,

Wouter- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Bijan,

If you change
intColMax = 4
to
intColMax = 3
Only columns B C and D will be summed.
The result will be:
__field1_____field2___field3___field4__
1 2008/12/30 4 13 7
becouse you have 2 line with field2 filled.

HTH,

Wouter