Adding a value to the first instance of duplicate values in a column

[deyken]

Hi Guys,

I am writing a MONSTER report that draws most of its content from 2 separate SQL Server Databases on our company server. It is an annual report that should list total expenditure for the year, subtracted from a global budget per project. Each month report (12 sheets = 12 months, where each month has its own Query delivering result for just that month) list all Requisitions processed against all active projects for that month.

On this list I will typically have multiple requisitions for a particular project. Now each line on this sub-report for the month will list the Requisition Number, Description, Project Code and its total requisition cost. So I will have several entries for different requisitions against a single project. What I want to do is to find the first instance of a Project Code (Column "F" in my case) and put that Project's entire Budget value next to it (Column "H" in my case). Essentially I have a SUM() function calculating the total Project Budgets for Column "H". If I deliver the Project Budget (using the Project Code as a function parameter/argument) then the same Budget total is printed for each line item and thus duplicated on many lines relating to the same project, so my totals are incorrect.

Can I isolate the first instance of a Project Code in Column "F" and just add that Project's Budget value into Column "H" once only? If so, how would I go about this?

[BrianMH]

You could use a vlookup as this only returns the first result in a column. Of course thats if you only have a limited number of project codes.

Are you OK to use a UDF?

[deyken]

Nah, VLOOKUP won't work, because I am working with data from a SQL Server DB. I think I found a solution. I simply created another VBA macro that collects the DISTINCT Project Codes from the Database (with the same criteria used to generate the full list of Requisitions), then I iterate through this result set. During each iteration, I simply use the Find() function on Column F, collect that Project's budget from the Database and then programmatically type it into Column H.

This is what my Code looks like (thus, I am quite fine with UDF's :yes)


Public Function GenerateAUGUST() As Boolean
' Return a complete RecordSet containing the selected Fiscal Year's List of Requisitions Captured on BuildSmart
Dim DB As New ADODB.Connection
DB.Open "Provider=SQLNCLI10.1;User ID=***;Password=***;Persist Security Info=False;Data Source=***;Database=BSBS_BrolazNew2"
Dim AugReqs As New ADODB.Recordset
Dim RowStart As Integer
Dim RowNum As Integer ' Current iterated Row Number, Dynamic on RowCount of Recordset
Dim Counter As Integer
AugReqs.Open "SELECT [CREATEDATE] as [Requisition Date], [REQID] as [Req Code], [REQNUMBER] as [Requisition Number], _
[REQSUBJECT] as [Requisition Subject], [SHORTDESCR] as [Requisition Description], (select sum(PRICE*QTY) from _
[BSBS_BrolazNew2].[dbo].[REQITEMS] WHERE REQID = dbo.REQ.REQID) as [Requisition Total], (select CONTRNUMBER from _
CONTRACTS WHERE CONTRID = (select DISTINCT TOP 1 [CONTRACTID] from [BSBS_BrolazNew2].[dbo].[REQITEMS] WHERE REQID _
= dbo.REQ.REQID)) as [Contract] FROM [BSBS_BrolazNew2].[dbo].[REQ] WHERE CREATEDATE >= '2012-08-01' AND CREATEDATE _
<= '2012-08-31' AND (select TOP 1 ALLOCATION from REQITEMS WHERE REQID = [dbo].[REQ].[REQID]) = 'Contracts' AND _
(select sum(PRICE*QTY) as [Requisition Total] from [BSBS_BrolazNew2].[dbo].[REQITEMS] WHERE REQID = dbo.REQ.REQID) > 0 _
ORDER BY [Contract]", DB, adOpenDynamic, adLockOptimistic
' Type out this Result Set onto the AUGUST Report Sheet
RowStart = 4
RowNum = RowStart
Counter = 0
' Clear Cell Contents
Sheet4.Range("A4", "J900").ClearContents
With AugReqs
.MoveFirst
While Not .EOF
Sheet4.Cells(RowNum, "A") = Format(.Fields("Requisition Date").Value, "dd/MM/yyyy")
Sheet4.Cells(RowNum, "B") = .Fields("Req Code").Value
Sheet4.Cells(RowNum, "C") = .Fields("Requisition Number").Value
Sheet4.Cells(RowNum, "D") = .Fields("Requisition Subject").Value
Sheet4.Cells(RowNum, "E") = .Fields("Requisition Description").Value
Sheet4.Cells(RowNum, "F") = Module1.GetBSContractID(.Fields("Req Code").Value)
Sheet4.Cells(RowNum, "G") = .Fields("Requisition Total").Value
'Sheet4.Cells(RowNum, "H") = GetProjectBudget(Module1.GetBSContractID(.Fields("Req Code").Value)) _
'GetGLCodeBudget(GetLedgerCode(.Fields("Req Code").Value), Module1.GetBSContractID(.Fields("Req Code").Value))
Sheet4.Cells(RowNum, "I") = "=H" & RowNum & "-G" & RowNum
Sheet4.Cells(RowNum, "J") = "=IF(H" & RowNum & "<>0, I" & RowNum & "/H" & RowNum & ", 0)"
If .Fields("Requisition Subject").Value = "Subcontractor." Then
Sheet4.Cells(RowNum, "K") = .Fields("Requisition Total").Value
Else
Sheet4.Cells(RowNum, "K") = "0"
End If
RowNum = RowNum + 1
Counter = Counter + 1
.MoveNext
Wend
End With
Dim ProjectBudgetTotal As Double
Dim CurrProj As String
Dim ProjBud As Double
ProjectBudgetTotal = 0
Dim ProjectsInMonth As New ADODB.Recordset
ProjectsInMonth.Open "SELECT DISTINCT (select CONTRNUMBER from CONTRACTS WHERE CONTRID = (select DISTINCT TOP 1 _
[CONTRACTID] from [BSBS_BrolazNew2].[dbo].[REQITEMS] WHERE REQID = dbo.REQ.REQID)) as [Contract] FROM [BSBS_BrolazNew2].[dbo].[REQ] _
WHERE CREATEDATE >= '2012-08-01' AND CREATEDATE <= '2012-08-31' AND (select TOP 1 ALLOCATION from REQITEMS WHERE REQID = [dbo].[REQ].[REQID]) _
= 'Contracts' AND (select sum(PRICE*QTY) as [Requisition Total] from [BSBS_BrolazNew2].[dbo].[REQITEMS] WHERE REQID = dbo.REQ.REQID) > 0 _
AND ((select CONTRNUMBER from CONTRACTS WHERE CONTRID = (select DISTINCT TOP 1 [CONTRACTID] from [BSBS_BrolazNew2].[dbo].[REQITEMS] _
WHERE REQID = dbo.REQ.REQID))) IS NOT NULL ORDER BY [Contract]", DB, adOpenDynamic, adLockOptimistic
If Not (ProjectsInMonth.EOF = True) And Not (ProjectsInMonth.BOF = True) Then
ProjectsInMonth.MoveFirst
While Not ProjectsInMonth.EOF
CurrProj = ProjectsInMonth.Fields(0).Value
ProjBud = GetProjectBudget(CurrProj)
ProjectBudgetTotal = ProjectBudgetTotal + ProjBud
' Find this Project in the F Column
On Error Resume Next
Sheet4.Columns("F:F").Select
Sheet4.Range("F1").Activate
Selection.Find(What:=CurrProj, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ProjectBugetTotal = ProjectBudgetTotal + GetProjectBudget(CurrProj)
' Display the Project Budget in this First instance's H Column
Sheet4.Cells(ActiveCell.Row, "H") = ProjBud
ProjectsInMonth.MoveNext
Wend
End If
Sheet4.Cells(1, "H") = "=SUM(H4:H900)"
GlobalCounter = GlobalCounter + Counter
GenerateAUGUST = True
'MsgBox "The August Report for Fiscal Year: " & Sheet3.Cells(5, "E") & " has been successfully generated with " & Counter & " Requisitions processed for that Month.", vbInformation
End Function