Link to home
Start Free TrialLog in
Avatar of dcmathis
dcmathis

asked on

Use the results of a macro to gather a subset of data and average that data.

With the attached file, running macro "generatetabs" will search the each of the first seven tabs (1.1-1.7) for an instance of a text value found on tab "Clients," and group the resulting records in a single tab with the same name as the text value from Clients.  The second part of this challenge is to take the Data1 value from the resulting data, search each of the first seven tabs (1.1-1.7) and average all columns of data with matching Data1 values.  For instance, if we look at client CO1, it has a Data1 value of PA.  A search of tab 1.1 returns the 17 records with the same Data1 value and an 18th record with totals of all numeric values.  An average of the non-total records needs to be generated:
User generated imageAnd a table created in the tab for CO1 with the average values from each of the tabs:
User generated image
Any ideas on how best to do this?
sample.xlsm
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Quick question now for instance if the same Company are their in multiple states? How do you want to create this table then?

In additional in the updated file can you show me once the tab is created..How do you want to see this average for what you are looking for...

Saurabh...
Avatar of dcmathis
dcmathis

ASKER

It's possible, especially with this sample spreadsheet, that there could be one company name and more than one state.  However, if we were to use values from Data4 for our search criteria instead of Data3 (as I was told initially), then the issue wouldn't arise.  Assume that the search criteria is no longer Data3, and use these values for sample use under the clients tab:
Data3           Data4
CO1             170183
Jefferson     351112

As for the report, it should look like this:
User generated imageAnd if there are search criteria that return the same state, then the average table should appear on each tab with that state in data1.
Okay so if i understand correctly you want the macro to use Data4 instead of data3?

And is it to safe to assume data4 will be only 1 per state?? In additional where do you want this report created in  a new tab only or in the tab right at the end post data is created..? Also if i understand correctly you want this report created only for sheets where the data was copied from..So for instance 1.3 has PA but data hasn't been copied from there you don't want to show in the report or you want to show in the report?

Saurabh...
1. correct. data4 instead of data3.
2.  Yes.  All items in data4 may be assumed to be unique and will have a single state value for each data item.
3.  The report should be created in the tab for the data4 item in question.  For instance if we have a data4 value of 123456, and it points to ME for the state, then the averages table should be put in that tab below the original results.  If we have a second data4 value of 987654, and it ALSO points to ME, then the averages table should ALSO appear in its tab.  
4.  you may also assume that there will be data in all seven tables for each data4 value.

dcm
One last thing...

Now in Data6,Data7,Data8 you have values which are blank as a result when you do calculate since the values are blank its excluding those values in average...

But i'm buidling a macro on weighted average which is basically sum/count.. Now what does that means even if the value is blank it will get counted as a result my average is lesser then what you have shown in the screenshot...

So when i count do you want me count only for filled rows where value is present for that state or count everything which i'm doing
Edit:  Assume that blanks cells are zero value.  Then if you have 15 cells but values in 10, then you'd run your average based on the full 15 cells.
Correction to my last post.  please count everything, like you're doing.


dcm
I'm assuming this is what you are looking for...

Sub generatetabs()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range, cell As Range
    Dim lrow As Long, lr As Long, lr1 As Long
    Dim r1 As Range, c As Range


    Set ws1 = Sheets("Clients")

    'finding the lastrow of the tab range
    lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    'setting up my range to create tabs
    Set r = ws1.Range("A2:A" & lr)

    ' Deleteing earlier sheets or data

    For Each ws In ActiveWorkbook.Worksheets

        If ws.Name <> ws1.Name And ws.Name <> "1.1" And ws.Name <> "1.2" And ws.Name <> "1.3" _
           And ws.Name <> "1.3" And ws.Name <> "1.4" And ws.Name <> "1.5" And ws.Name <> "1.5 " And ws.Name <> "1.6" And ws.Name <> "1.7" Then
            ws.Delete
        End If
    Next ws

    'starting the search

    For Each c In r
        'creating tab for this value
        Worksheets.Add after:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
        Set ws2 = ActiveSheet
        'Copying headers
        Sheets("1.1").Range("A5").EntireRow.Copy ws2.Range("A1")
        'starting search at each ws
        For Each ws In ActiveWorkbook.Worksheets
            'checking for correct sheet to copy data
            If ws.Name <> ws1.Name And Application.WorksheetFunction.CountIf(r, ws.Name) = 0 Then
                lrow = ws.Cells(Cells.Rows.Count, "D").End(xlUp).Row
                Set rng = ws.Range("D10:D" & lrow)
                For Each cell In rng
                    If Trim(UCase(cell.Value)) = Trim(UCase(c.Value)) Then

                        lr1 = ws2.Cells(Cells.Rows.Count, "D").End(xlUp).Row + 1
                        cell.EntireRow.Copy ws2.Range("A" & lr1)
                    End If
                Next cell

            End If

        Next ws

        lr1 = ws2.Cells(Cells.Rows.Count, "D").End(xlUp).Row
        If lr1 > 1 Then
            'creating summary
            lr1 = ws2.Cells(Cells.Rows.Count, "D").End(xlUp).Row + 2

            ws2.Range("A" & lr1).Value = "Report"
            ws2.Range("b" & lr1).Value = "State"
            ws2.Range("C1:K1").Copy
            ws2.Range("C" & lr1).PasteSpecial xlPasteValues
            Application.CutCopyMode = False



            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> ws1.Name And Application.WorksheetFunction.CountIf(r, ws.Name) = 0 Then

                    lr1 = ws2.Cells(Cells.Rows.Count, "a").End(xlUp).Row + 1
                    lrow = ws.Cells(Cells.Rows.Count, "D").End(xlUp).Row
                    ws2.Range("A" & lr1).Value = ws.Name
                    ws2.Range("B" & lr1).Value = ws2.Range("A2").Value
                    ws2.Range("E" & lr1 & ":K" & lr1).Formula = "=SUMPRODUCT(('" & ws.Name & "'!$A$10:$A$" & lrow & "=$B" & lr1 & ")*('" & ws.Name & "'!$D$10:$D$" & lrow & "<>$B" & lr1 & ")*('" & ws.Name & "'!E$10:E$" & lrow & "))/SUMPRODUCT(('" & ws.Name & "'!$A$10:$A$" & lrow & "=$B" & lr1 & ")*('" & ws.Name & "'!$D$10:$D$" & lrow & "<>$B" & lr1 & "))"
                    ws2.Range("E" & lr1 & ":K" & lr1).Value = ws2.Range("E" & lr1 & ":K" & lr1).Value
                    ws2.Range("E" & lr1 & ":h" & lr1).NumberFormat = "0.00"
                    ws2.Range("i" & lr1).NumberFormat = "0.00%"
                End If


            Next ws


        End If

        ws2.Cells.EntireColumn.AutoFit
    Next c

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Open in new window


Your workbook...

Saurabh...
sample.xlsm
In the macro which i posted it count everything like i was doing earlier..
That looks really close.  I need to dig through it and make sure, but it looks good.
Saurabh,

It's working just fine, except that I'm getting divide by zero errors in some of the reports.  The fact that those columns are empty in some of the data is fine, but I can't figure out where to put the IF (X=0," ", Y/X) expression.  I'm sure it's in here somewhere, but can't locate the place for it.  
ws2.Range("E" & lr1 & ":K" & lr1).Formula = "=SUMPRODUCT(('" & ws.Name & "'!$A$10:$A$" & lrow & "=$B" & lr1 & ")*('" & ws.Name & "'!$D$10:$D$" & lrow & "<>$B" & lr1 & ")*('" & ws.Name & "'!E$10:E$" & lrow & "))/SUMPRODUCT(('" & ws.Name & "'!$A$10:$A$" & lrow & "=$B" & lr1 & ")*('" & ws.Name & "'!$D$10:$D$" & lrow & "<>$B" & lr1 & ")*('" & ws.Name & "'!E$10:E$" & lrow & "<>""""))"

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That nailed it, Saurabh.  Thanks a million!
Glad to help... :-)