Solved: Create quarterly summary report from details

[sujittalukde]

I have attached a file wherein a sheet called ?Summary? exist..
This summary sheet should import data from the Sheet ?Details?
Data should be imported in the following manner.
The ?Summary? sheet is based on Quarter. For eg., Cell D2 contains the term ?Taxable Expenses? for Quarter 1. Similarly F2, H2 & J2 contains the trem for Quarter 2,3 & 4 respectively.
The cell A3 & onwards contains Head of Expenditures. This Head of Expenditure & Quarter also exist in Details Sheet.
Now I want that a macro should look for the first Head of Exp. For each quarter in the summary sheet, then go to Details sheet, look for the first Head of Exps. For the quarter and if found pick the same from the Detail Sheet and put the value in the Summary sheet for that quarter. If the Head of Exps. Is not found for the quarter then put 0 (Zero).

Can this be done by a macro?

The summary sheet format is fixed ie will not change but the number of rows for Details sheet will change but the columns will be fixed.

The data in the summary sheet is done manually this I want to import by macro.

[sujittalukde]

I have tried the same and madw this code but some error is there. Cn someone please modify it to suit the requirement?

I have removed the subtotals.
Inserted a new row in Sheets Summary at Row 3.
Now I am running the following code:

Sub MakeSumm()
Dim Fbthoe As String
Dim Qtr As String
Dim urows As Double
Dim Total As Double
Dim j As Double
Dim i As Double
Dim q As Long
For j = 4 To 21
Sheets("Summary").Select
Fbthoe = Range("A" & j).Value
q = 3
Qtr = Range("d" & q).Value
Sheets("Details").Select
urows = ThisWorkbook.Sheets("Details").UsedRange.Rows.Count
For i = 2 To urows
If Range("C" & i).Value = Fbthoe And Range("F" & i).Value = Qtr Then
Total = Total + Range("G" & i).Value
'MsgBox Total
Else
'do nothing
End If
Next i
Sheets("Summary").Select
Range("D" & j).Value = Total
q = q + 2
Next j
End Sub

This code is only bringing the first matched item for Qtr 1 only to all the cells in column D ie Qtr1 only from the first match ie Conveyance.

The modified file is attached.

[mdmackillop]

Don't think you need a macro for this
Enter into D4
=SUMPRODUCT(--(Details!$C$2:$C$58=Summary!$A4),--(Details!$F$2:$F$58=Summary!D$3),Details!$G$2:$G$58)
copy down and then copy into other yellow columns.

[sujittalukde]

Thanks md but this is a part of my entire peoject where I cant use formula. So a macro is needed.

[sujittalukde]

Sorry to mention the face that I have also posted the same at ozgrid for a solution earlier but due to net problem, I couldnot post the link here.
The thread is located here:
http://<a href="http://www.ozgrid.co...hp?t=87666</a>

[mdmackillop]

Enter the formula by macro then copy/paste special the result if you don't want formulae left in the sheet.
[vba]
Range("D4").FormulaR1C1 ="=SUMPRODUCT(--(Details!R2C3:R58C3=Summary!RC1),--(Details!R2C6:R58C6=Summary!R3C),Details!R2C7:R58C7)"

[/vba]

[sujittalukde]

Thanks
In the mean time at ozgrid, Krishnakumar has solved the issue. Thanks to all for the help.

[mdmackillop]

How about a working link to the solution.

[sujittalukde]

THis is the link
http://www.ozgrid.com/forum/showthread.php?t=87666