vba solution

  • Thread starter Thread starter bijan
  • Start date Start date
B

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
 
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?
 
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
 
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
---
 
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)
 
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
 
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
 
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 -
Click to expand...

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
Click to expand...
becouse you have 2 line with field2 filled.

HTH,

Wouter
 
Back
Top