VBA Excel: sum values based on unique range

Hello experts,

I have the following flat file attached (Input Sheet).
I am trying to build a vba procedure that covers the following requirements:
1-Check if Output sheet if so delete and create a new blank
2-Transfer the various unique values in column H, J and AI
3-Consolidate the sum values for the various month from AJ to BV split by column AI

I attached dummy file with expected Output sheet.

I think that we can proceed steps by steps, such as copy unique values related to H & J.
For this we can use the following procedure:

Sub CopyRangeUniqueValues(FromWSheet As String, FromRange As String, ToWSheet As String, ToRange As String)
'Sub treats first cell in FromRange as a header label, and will copy it to ToRange even if it is duplicated in the list
Dim celHome As Range, rgDest As Range, rgSource As Range
Dim n As Long
Set celHome = ActiveCell
Set rgDest = Worksheets(ToWSheet).Range(ToRange)
Range(rgDest, rgDest.End(xlDown)).ClearContents

With Worksheets(FromWSheet)
    n = .UsedRange.Row + .UsedRange.Rows.Count - 1
    Set rgSource = .Range(FromRange & n)
End With

rgDest.Worksheet.Activate
rgSource.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rgDest, Unique:=True
Application.GoTo celHome

Open in new window


Concerning the consolidated sum we can also go ahead with a formula reported in macro:

Sub LoopFormula2(strwsName, strColflag, strCol As String, strColName As String, formula As String)

'Call LoopFormula2("ImportCC", "1", "D", "Test", "=clean(sheet1!G2*sheet1!I2)") 'example of formula
    Dim ws As Worksheet
    Set ws = Worksheets(strwsName)
    ws.Activate
     '==>Clear Destination Column
    Range(strCol & ":" & strCol).Clear
    UsedRows = ActiveSheet.UsedRange.Rows.count + ActiveSheet.UsedRange.Rows(1).Row - 1
    If strColflag = 1 Then
        Range(strCol & 1).Value = strColName
    End If
    '==>Clear Destination Column
    RightFormula = Replace(formula, "1048576", UsedRows)
    '==>ApplyFormula
    Range(Cells(2, strCol), Cells(UsedRows, strCol)).formula = RightFormula
    Range(Cells(2, strCol), Cells(UsedRows, strCol)).Value = Range(Cells(2, strCol), Cells(UsedRows, strCol)).Value
End Sub

Open in new window


Thank you in advance for your help.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You forgot to upload the file.
LD16Author Commented:
Sorry. Please find attached file.
Project-Summary.xlsx
Ejgil HedegaardCommented:
Try attached.
Start with the button.
To speed up the calculation, the Input sheet are sorted on ID, Project name and Request Status.
Then the 3 columns are copied as values to Output.
Duplicates removed.
Because of the sort, the Output values are in the sane order as Input, so summing can be done on small ranges for each type (Output row), instead of looking at all 20,000 rows for each sum.
For each type the input values are loaded into an array, the sum done in VBA, and the result transferred to Output.
Finally the Input sheet are sorted back to what it was, don't know it you need that.
You can follow the progress at the Status Bar in the lower left corner.
The process are done in a few seconds.
Project-Summary.xlsm
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

LD16Author Commented:
Great! Excellent as always. Is it possible to have a second proposal with just ID and Project Name, exclude Request status?
Concerning the second line of output is it normal that we don't have information in ID, Name and request status?

Thank you again for your help.
Ejgil HedegaardCommented:
Function with 2 criteria added.

The second line of Output is because the last data row on Input 20016, has empty text in all fields.
Select cell A7, press End and then arrow down.
Then the last cell with text in the column is selected, A20016.
It looks empty, but is not.
Press End, and then arrow right, and cell BV20016 is selected, so none of the cells in that range is empty.
Empty text means it is text with no length, but the cells are not empty.

Delete the cells, or the row.
Project-Summary.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Got it. Thank you again for your help! As soon as I can test the second proposal I will keep you informed.
LD16Author Commented:
I tested and it works. Thank you again!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.