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:
All records with Data1 value + PAAnd a table created in the tab for CO1 with the average values from each of the tabs:
Table giving Records for 1.1-1.7 in CO1 Tab
Any ideas on how best to do this?
sample.xlsm
dcmathisAsked:
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.

Saurabh Singh TeotiaCommented:
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...
0
dcmathisAuthor Commented:
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:
average3.PNGAnd if there are search criteria that return the same state, then the average table should appear on each tab with that state in data1.
0
Saurabh Singh TeotiaCommented:
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...
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

dcmathisAuthor Commented:
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
0
Saurabh Singh TeotiaCommented:
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
0
dcmathisAuthor Commented:
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.
0
dcmathisAuthor Commented:
Correction to my last post.  please count everything, like you're doing.


dcm
0
Saurabh Singh TeotiaCommented:
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
0
Saurabh Singh TeotiaCommented:
In the macro which i posted it count everything like i was doing earlier..
0
dcmathisAuthor Commented:
That looks really close.  I need to dig through it and make sure, but it looks good.
0
dcmathisAuthor Commented:
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

0
Saurabh Singh TeotiaCommented:
Replace that line with this...

ws2.Range("E" & lr1 & ":K" & lr1).Formula = "=IFERROR(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


Saurabh...
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
dcmathisAuthor Commented:
That nailed it, Saurabh.  Thanks a million!
0
Saurabh Singh TeotiaCommented:
Glad to 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
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.