pull data from separate tabs to "dashboard" tab, removing duplicates and counting % of cells poplualted, etc

[kenton71]

I have a spreadsheet with six tabs (one for each team member) and one additional tab to be used as a dashboard. I'm trying to pull data from each tab and have it populate the dashboard tab.

Each team member has the same layout on their tab and uses the same status names. So, Column E has a unique project number (which may or may not be repeated several times on their tab, depending upon the size of the project), columns C and I are always the same for Column E (showing due date and project name). Columns F, G, and H will have unique data on every line as they relate to the individual items on each project. F, G, and H will either have information or not. And we just need to capture how many times for the project a cell in those columns is populated.

What I want to end up with on a new tab:
Status from column A ("not started", "in progress", "complete", "on hold", or "cancelled" -- each team member uses the exact same words), Project Due Date (column C), Project # (column E), Project Name (column I), Team Member Name (from column B), % of cells in column F for that project with an entry, % of cells in column G for that project with an entry, % of cells in column H for that project with an entry. I think the trickiest thing is that some projects only have one line on the spreadsheets and some have more than 70.


Example of how the end product should look:
In Progress 2/1/2019 ABC123 Project Lion Carla 50% 75% 10%
On Hold 3/5/2020 BDG425 Project Tiger Bill 46% 58% 100%

 

[kenton71]

Looks good, but a few issues:
1) still getting some duplicates, even though they share the same project name and project number.
2) not sure what column H is pulling from on the dashboard, as it doesn't relate to the data. it's saying 100%, when there isn't a date but "n/a" -- perhaps we can make is easier on the people entering the data by making F, G, and H pull over a % if there is any thing (date, text, number) in that column.

 

[frank_AL]

kenton, I think I fixed the issue with Column H. I now check to see if the input is a Date rather than just checking that it is not blank. Give this new code a try and let me know if this fixes that issue.

Option Explicit


Sub SumData()
Dim ws As Worksheet
Dim ctws As Worksheet
Dim lastrow As Long
Dim nextrow As Long
Dim i As Long
Dim data(1 To 9) As Variant
Dim projcnt As Long
Dim phasecnt As Long
Dim sapcnt As Long
Dim shipcnt As Long


Set ctws = Worksheets("Dashboard")
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Dashboard" Then
        lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
        Range("A2:I" & lastrow).Sort key1:=Range("E1")
        projcnt = 1
        phasecnt = 0
        sapcnt = 0
        shipcnt = 0
        For i = 2 To lastrow
            If ws.Cells(i, "F") = "yes" Then phasecnt = phasecnt + 1
            If IsNumeric(ws.Cells(i, "G")) Then sapcnt = sapcnt + 1
            If IsDate(ws.Cells(i, "H")) Then shipcnt = shipcnt + 1
            If ws.Cells(i + 1, "E") <> ws.Cells(i, "E") Then
                data(1) = ws.Cells(i, "A")
                data(2) = ws.Cells(i, "C")
                data(3) = ws.Cells(i, "E")
                data(4) = ws.Cells(i, "B")
                data(5) = ws.Cells(i, "I")
                data(6) = phasecnt / projcnt
                data(7) = sapcnt / projcnt
                data(8) = shipcnt / projcnt
                data(9) = projcnt
                projcnt = 1
                phasecnt = 0
                sapcnt = 0
                shipcnt = 0
                nextrow = ctws.Cells(Rows.Count, "A").End(xlUp).Row + 1
                ctws.Range("A" & nextrow & ":I" & nextrow) = data
                projcnt = 0
            End If
            projcnt = projcnt + 1
        Next i
    End If
Next
ctws.Columns("F:H").NumberFormat = "0.0%"
ctws.Columns("I").NumberFormat = "0"
ctws.Columns("A:I").EntireColumn.AutoFit
End Sub

 

[frank_AL]

kenton I did some additional testing this morning and made one more correction to the code to ensure the sort command was being implemented against the individual team member's Worksheet.

Option Explicit


Sub SumData()
Dim ws As Worksheet
Dim ctws As Worksheet
Dim lastrow As Long
Dim nextrow As Long
Dim i As Long
Dim data(1 To 9) As Variant
Dim projcnt As Long
Dim phasecnt As Long
Dim sapcnt As Long
Dim shipcnt As Long


Set ctws = Worksheets("Dashboard")
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Dashboard" Then
        lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
        ws.Range("A2:I" & lastrow).Sort key1:=ws.Range("E1")
        projcnt = 1
        phasecnt = 0
        sapcnt = 0
        shipcnt = 0
        For i = 2 To lastrow
            If ws.Cells(i, "F") = "yes" Then phasecnt = phasecnt + 1
            If IsNumeric(ws.Cells(i, "G")) Then sapcnt = sapcnt + 1
            If IsDate(ws.Cells(i, "H")) Then shipcnt = shipcnt + 1
            If ws.Cells(i + 1, "E") <> ws.Cells(i, "E") Then
                data(1) = ws.Cells(i, "A")
                data(2) = ws.Cells(i, "C")
                data(3) = ws.Cells(i, "E")
                data(4) = ws.Cells(i, "B")
                data(5) = ws.Cells(i, "I")
                data(6) = phasecnt / projcnt
                data(7) = sapcnt / projcnt
                data(8) = shipcnt / projcnt
                data(9) = projcnt
                projcnt = 1
                phasecnt = 0
                sapcnt = 0
                shipcnt = 0
                nextrow = ctws.Cells(Rows.Count, "A").End(xlUp).Row + 1
                ctws.Range("A" & nextrow & ":I" & nextrow) = data
                projcnt = 0
            End If
            projcnt = projcnt + 1
        Next i
    End If
Next
ctws.Columns("F:H").NumberFormat = "0.0%"
ctws.Columns("I").NumberFormat = "0"
ctws.Columns("A:I").EntireColumn.AutoFit
End Sub

 

[kenton71]

I'm still having the issue of duplicates. Of 78 projects, there are 10 that are duplicates. Now, it is possible that two team members would run the same project, and that should show up twice, but if it's only one team member, then a project number should only show up once.

 

[frank_AL]

kenton, are you testing with the very latest version of the code? This morning I did find an issue where when I sort each worksheet on Project ID I wasn't explicitly sorting the active Worksheet and I fixed that. That issue could have allowed duplicate projects per manager. Using the data you posted I am not seeing any duplicates on the Dashboard.

 

[kenton71]

Yes. Only three duplicates though.

All coming from the last person's tab. And interestingly the data is inconsistent. On one duplicate project has two lines on the dashboard: one with 4 items, one with 7, but the project is only 10 items. On another one, it breaks it into a line of 4 and one of 6 (this project DOES have 10 items, so this is correct, except it should only be one line). On the final one, it breaks into two lines of 4 (again, correct, as there are 8 items in the program, but it should be on one line).

 

[kenton71]

hmm... and I just noticed that it's not grabbing all the records. for example, I have some projects that don't have a project number yet (not all will). It isn't pulling those over.

 

[kenton71]

sorry, one more thing. would like to add a column showing the number of days until (since) the in market date. I was trying to just put in the formula in a new column on the dashboard =today()-B1 but it is coming up with an answer that is too large to display.

 

[kenton71]

column F should pull from F -- anything should be counted (text, number, date)
column G should pull from G -- anything should be counted (text, number, date) (this one seems to pulling from somewhere else currently)
column H should pull from H -- anything should be counted (text, number, date)

 

[frank_AL]

column F should pull from F -- anything should be counted (text, number, date)
column G should pull from G -- anything should be counted (text, number, date) (this one seems to pulling from somewhere else currently)
column H should pull from H -- anything should be counted (text, number, date)

kenton I've made the change to count anything in columns F, G and H but that is a change from original requirements.