vba solution

[bijan]

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

 

[Nigel]

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?

 

[bijan]

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

 

[Max]

Perhaps a simple SUMIF in another sheet will cut it for you?

With source data as posted in Sheet1's cols A to E
dates in col A, numbers in cols B to E

In Sheet2,
with the unique dates listed in A1 down
Put in B1: =SUMIF(Sheet1!$A:$A,$A1,Sheet1!B:B)
Copy across to E1, fill down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

 

[Nigel]

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
(e-mail address removed)

 

[RadarEye]

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

 

[bijan]

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

 

[bijan]

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

 

[RadarEye]

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      








- 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