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 )

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