PDA

View Full Version : aggregate Data and create new dashboard



Oryos
03-15-2016, 02:00 PM
hi everyone,

I'm kinda stuck with my dashboard and I hope you can help me out.
I've a create a dashboard based on data from a kind of log file of software bugs. I've attached the file that describes the situation with the outcome in worksheet Table2.

15655

The dashboard is a summary of all the log incidents that happened on one day and I already wrote a vba code that doesnt really achieve what I want.


Sub test()
Dim rng1 As Range, rng2 As Range, c As Long, b As Long, u As Long, rng3 As Range
Dim find As Date
Dim rng As Range
Dim Table2 As Worksheets

With Worksheets("Table2")

Set rng = Range("A1")
For c = 1 To Sheets("Table1").Range("B" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("Table1").Range("B" & c)
For b = 1 To Sheets("Table2").Range("A" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("Table2").Range("A" & b)
If rng1 = rng Then
rng2.Offset(0, 2).Value = rng1.Offset(0, 2).Value
End If
Set rng2 = Nothing
Next b
Set rng1 = Nothing
Next c
End With
End Sub



What i'm basically challenged with are:
1. how do I create a table in the middle of a worksheet? My macro starts from the top of the sheet.
2. how can I merge cells so that they look like in my table?
3. my code loops through the all the cells and overwrites the results it already found. Example: cell value "bug description B" for the 15.03. is found first und written into Table2 column C but afterwards "bug description A" is found too and "bug description B" is overwritten. How can I avoid that?

Thanks for your effort and your thoughts!

Oryos
03-23-2016, 12:11 PM
Hi everyone,

it's been a while since I started this thread an I was working on my macro a litte bit to achieve what I want to do.

This is how it looks right now:


Sub DoFilter()

Dim rCrit2 As Range, rRng1 As Range, rRng2 As Range
Dim myDate As String
Dim rng1 As Range
Dim Rng As Range
Dim iVal As Integer

Set Rng = Sheets("Table2").Range("A1")
myDate = Date

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set rRng1 = Sheets("Table1").Range("B1:E1")

With rRng1

.AutoFilter Field:=1, Criteria1:=myDate, Operator:=xlOr
For c = 1 To Sheets("Table1").Range("B" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("Table1").Range("B" & c)
If rng1 = Rng Then
Sheets("Table2").Range("C" & c).Value = rng1.Offset(0, 1).Value
End If
Set rng1 = Nothing
Next c
End With
Application.EnableEvents = True

End Sub


With this macro I could filter out the important entries for today. Thats a good thing. However, What I cant figure out is how to tell my macro that the values of column C (headline: bug) and column E (headline: priority) are written in Table2 row 10 downwards. What my macro does now is that it writes the values into C4 and C5 in Table2 worksheet which are the same cell as in Table1. Thats a problem I'm facing and I wonder if you know how I can fix that.

Basically this is what I'm trying to do:

1. find current date for the entries in Table1
2. copy values in column C and E for current into Table2 starting with cell C10 downwards.

Has anybody any idea on how to do the second part?
Please find attached my excel file and my macro inside.

Thank you and happy easter!


15744

p45cal
03-23-2016, 01:24 PM
Attached file has 2 methods, a pivot table being a manual process of choosing the date, and an Advanced filter method being macro based (a one line macro).

Oryos
03-29-2016, 10:31 AM
Hi p45cal,

thanks for you reply. I had a look into your macro over easter holidays and I like especially option 1! Thanks a lot. I was amazed to see that this function works even with a one liner.
Could you just explain why the macro doesnt produce an output of the extract area is in some cells?

For example I put the extract in B4 and C4:

Sub Macro5()
Sheets("Table1").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Table2").Range("G16:G17"), CopyToRange:=Range("B4:C4"), Unique:=False
End Sub


and I get an error message.
Output area in B23 and C23 works flawless.


Sub Macro5()
Sheets("Table1").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Table2").Range("G16:G17"), CopyToRange:=Range("B23:C23"), Unique:=False
End Sub



Why is that?

Thanks for your help! Very much appreciated!

p45cal
03-29-2016, 11:00 AM
The error you get is "The extract range has a missing or illegal field name". There should be field names in the CopyToRange and no blank cells; this is what determines which fields are output. So put 'bug' and 'priority' in cells B4 and C4, make sure there's also room for the results below these cells and you should be good to go.