VBA Excel: compiled & sum data based on unique values v2

Luis Diaz
Luis Diaz used Ask the Experts™
on
Hello experts,

Further solution proposal of question: https://www.experts-exchange.com/questions/29114446/

I would like to include the following requirements:

1-Perform the sum based on unique values of column F (Who)
2-Generate Output result in different sheets based on unique values of column F (Who)

By doing so we can have the vision for each (Who) defined in Input sheet split by sheets.
I attached an example file with required tabs.

If you have questions, please contact me.

Regards,
EE_follow_up_v2.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
The code that I have added will separate the data into separate sheets based on Who.

let me know if this is correct and I'll look at the sum macro to incorporate.
EE_follow_up_v2--1-.xlsm
Luis DiazIT consultant

Author

Commented:
Thank you Roy,

I tested and it works. As regards the extract sheet procedure I was wondering if we can add the following requirements (if difficult we can go skip this):
1-Report in the filter which are the sheet or sheets what I want to extract from column F (Who). This will allows me to see the wanted sheets in a first place and if in the future I will need to check all the sheet I remove the filter:
rData.Columns(6).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True

Open in new window

2-Start copy-paste as of second row. In first row I want that the procedure report last update with now date as attached in my example file bellow.

Let me know what do you think.

Thank you very much for your help.
EE_follow_up_v2--2.xlsm
Roy CoxGroup Finance Manager

Commented:
I'm not clear what your point #1 is. Can you explain a little more.

I have adjusted the destination, added the date stamp and tidied up the formatting of the copied data.
EE_follow_up_v2--3.xlsm
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Roy CoxGroup Finance Manager

Commented:
1-Report in the filter which are the sheet or sheets what I want to extract from column F (Who). This will allows me to see the wanted sheets in a first place and if in the future I will need to check all the sheet I remove the filter:

I've added the search term to the sheet, is this what you mean?
EE_follow_up_v2--3.xlsm
Luis DiazIT consultant

Author

Commented:
Ok, for #1 what I need is to exclude some values of column F (who).
In my example I have 3 unique values: LDI, ACA, SBO. What I need is to exclude some values for example I just want to generate unique sheets with the sum for LDI and ACA and not generate sheet for SBI.
Luis DiazIT consultant

Author

Commented:
If this is complicate, the workaround would be to generate all the sheets and then call another procedure to delete unwanted sheets but if we can just generate the wanted sheets in the same procedure it would be great.
Roy CoxGroup Finance Manager

Commented:
This amendment excludes creating a sheet for SBO, is this what you want?
EE_follow_up_v2--3.xlsm
Luis DiazIT consultant

Author

Commented:
Yes, Just for my knowledge if I want to exclude more than one value.
Example:
I can do:  If sNm <> "SBO" Or sNm <> "LDI" or I need to create another if condition?
Thank you very much we can go ahead incorporating the sum function.
Roy CoxGroup Finance Manager

Commented:
That should work. For multiple names you could use a Select Case which is more readable and easier to maintain.

Note,      Case "SBO", "123", "etc" represents names in the workbook to be excluded, change for your actual sheet names.

Sub ExtractToSheets()
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim rData As Range, rList As Range
    Dim rCl As Range
    Dim sNm As String

    Set ws = Sheets("Input")

    'extract a list of unique names
    'first clear existing list
    With ws
        Set rData = .Range("A1").CurrentRegion
        .Columns(.Columns.Count).Clear
        rData.Columns(6).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, .Columns.Count), Unique:=True

        Set rList = .Cells(1, .Columns.Count).CurrentRegion
        Set rList = rList.Offset(1, 0).Resize(rList.Rows.Count - 1, _
                                              rList.Columns.Count)
        For Each rCl In rList
            sNm = rCl.Text
          ''/// exclude certain sheets
            Select Case sNm
            Case "SBO", "123", "etc"
            ''/// do nothing
            Case Else
              ''///add new sheet (only if required-NB uses UDF)
                If WksExists(sNm) Then
                    'so clear contents
                    Sheets(sNm).Cells.Clear
                Else
                    ''///new sheet required
                    Set wsNew = Sheets.Add
                    wsNew.Move After:=Worksheets(Worksheets.Count)    'move to end
                    wsNew.Name = sNm
                End If
                ''///AutoFilter & copy to relevant sheet
                rData.AutoFilter Field:=6, Criteria1:=sNm
                With Worksheets(sNm)
                    rData.Copy Destination:=.Cells(2, 1)
                    .Cells(1, 1).Value = "Last Update: " & Format(Date, "YYYMMDD") & " for " & sNm
                    .UsedRange.Columns.AutoFit
                    .UsedRange.Columns(9).ColumnWidth = 60
                    .UsedRange.Rows.RowHeight = 15
                End With
            End Select
        Next rCl
    End With
    ws.Columns(Columns.Count).ClearContents        'remove temporary list
    rData.AutoFilter        ''///switch off AutoFilter
    MsgBox "Report completed", vbInformation, "Done"
End Sub

Open in new window

Luis DiazIT consultant

Author

Commented:
Got it,
I test it but I got the following error. Once we are able to debug can we include the sum so I can check both actions in the same file?
Thank you very much for your help.
Roy CoxGroup Finance Manager

Commented:
What error are you getting?

I've just run the last code with no errors
Luis DiazIT consultant

Author

Commented:
Sorry, I did not attach the error message.
Please find error message bellowerror-filter.pngI also attached xml file.
Thank you very much for your help.
EE_follow_up_v2--3.xlsm
Roy CoxGroup Finance Manager

Commented:
The Function that checks if a sheet exists  is missing in the code

Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Open in new window


Here's an updated version to use the select case that I mentioned.

I need to look at the sum part. The existing code seems to create one sheet , do you want  the sheets that we are creating to  have like a block of  totals at the bottom.

I'm actually thinking that a PivotTable would be simpler
EE_follow_up_v2--3--1-.xlsm
Luis DiazIT consultant

Author

Commented:
Thank you now it works, pivot can be complicated as you can see in the sum part we have a filter for the last 5 weeks dynamically set up so it would be better to use the sum procedure.
If a block totals at the bottom is not straightforward we can set up in the procedure
Thank you.
Roy CoxGroup Finance Manager

Commented:
I'll have a look at the sum as soon as I can.
Roy CoxGroup Finance Manager

Commented:
I've added some options for reviewing the stats.

  1. Pivot All requires no VBA. You can select weeks required and Tool based on the INPUT sheet data.
  2. LDI Pivot is based on the VBA created LDI sheet


I think the Pivot All is the best solution. It's versatile and all data is available on one sheet. Pivots can be refreshed as data is added to the Input sheet. This means that you can select any combination of weeks,

You could create a table  in each sheet created but I think that is unnecessary. If you want individual sheets then probaly have a PivotTable set up. This would mean having a template sheet for each WHO
EE_follow_up_v2--4.xlsm
Group Finance Manager
Commented:
I have added Slicers to Pivot All which makes it easier to select different views.
EE_follow_up_v2--4.xlsm
Luis DiazIT consultant

Author

Commented:
Hello Roy,

Thank you very much for this proposal. Having all in a Pivot doesn't fully cover the initial requirement of the question which was generate sum for each column f. I prefer having the sum view for each who with VBA rather than having all in pivot even if pivot is also a way of entry.

Having different sheets for each who allows to directly go to the sheet and see reference data of each who instead of modifying pivot.

Thank you.
Roy CoxGroup Finance Manager

Commented:
VBA would be far less efficient than using a PivotTable. Have you looked at the formula results?
Luis DiazIT consultant

Author

Commented:
Thank you for your message Roy.
The formula seems to be ok.
I don't think vba is less efficient. Based on previous question:
https://www.experts-exchange.com/questions/29114446/

Open in new window

for one who the result is generated in less than 20 seconds.

Thank you.
Roy CoxGroup Finance Manager

Commented:
A PivotTable would be almost instant, but you still haven;t asnwerd my question awhether the formulas I added worked.
Luis DiazIT consultant

Author

Commented:
Yes, sumif formulas works. However formula are statics and in the future, additional tools can be added so the formula solution don't cover the final need.
If we already have the sumlastfiveweeks procedure which cover the need why we cannot add the extracttosheets provided in one of your last comment?
Thank you again for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial