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:
And a table created in the tab for CO1 with the average values from each of the tabs:
Any ideas on how best to do this?
sample.xlsm
And a table created in the tab for CO1 with the average values from each of the tabs:
Any ideas on how best to do this?
sample.xlsm
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:
And if there are search criteria that return the same state, then the average table should appear on each tab with that state in data1.
Data3 Data4
CO1 170183
Jefferson 351112
As for the report, it should look like this:
And 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...
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...
ASKER
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
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
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
ASKER
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.
ASKER
Correction to my last post. please count everything, like you're doing.
dcm
dcm
I'm assuming this is what you are looking for...
Your workbook...
Saurabh...
sample.xlsm
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
Your workbook...
Saurabh...
sample.xlsm
In the macro which i posted it count everything like i was doing earlier..
ASKER
That looks really close. I need to dig through it and make sure, but it looks good.
ASKER
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.
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 & "<>""""))"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That nailed it, Saurabh. Thanks a million!
Glad to help... :-)
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...