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

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
LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Roy CoxGroup Finance ManagerCommented:
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
0
LD16Author 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
0
Roy CoxGroup Finance ManagerCommented:
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
0
Determine the Perfect Price for Your IT Services

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

Roy CoxGroup Finance ManagerCommented:
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
0
LD16Author 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.
0
LD16Author 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.
0
Roy CoxGroup Finance ManagerCommented:
This amendment excludes creating a sheet for SBO, is this what you want?
EE_follow_up_v2--3.xlsm
0
LD16Author 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.
0
Roy CoxGroup Finance ManagerCommented:
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

0
LD16Author 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.
0
Roy CoxGroup Finance ManagerCommented:
What error are you getting?

I've just run the last code with no errors
0
LD16Author 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
0
Roy CoxGroup Finance ManagerCommented:
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
0
LD16Author 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.
0
Roy CoxGroup Finance ManagerCommented:
I'll have a look at the sum as soon as I can.
0
Roy CoxGroup Finance ManagerCommented:
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
0
Roy CoxGroup Finance ManagerCommented:
I have added Slicers to Pivot All which makes it easier to select different views.
EE_follow_up_v2--4.xlsm
0

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:
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.
0
Roy CoxGroup Finance ManagerCommented:
VBA would be far less efficient than using a PivotTable. Have you looked at the formula results?
0
LD16Author 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.
0
Roy CoxGroup Finance ManagerCommented:
A PivotTable would be almost instant, but you still haven;t asnwerd my question awhether the formulas I added worked.
0
LD16Author 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.
0
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.