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

kenton71 · Feb 8, 2018

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%

frank_AL · Feb 11, 2018

If possible, post your spreadsheet with any personally identifiable information removed/changed or at least one worksheet that represents one team member. I don't mind writing code I just hate trying to recreate data!!

kenton71 · Feb 11, 2018

dashboard: should show leader, project #, status, launch date, days to launch, phase complete %, SAP %, and shipped %

#

SAP %

Shipped %

Carla's tab:

Status	Leader	Launch Date	# of days from launch	Project Number	Phase Complete	SAP #	Ship Date
complete	Carla	4/10/17	307	INV012345	yes	155522	02/05/17
complete	Carla	4/10/17	307	INV012345	yes	153075	02/05/17
complete	Carla	9/11/2017	153	INV012346	yes	154326	6/13/2017
on hold	Carla	4/16/18	-64	INV009876	yes	not in system
on hold	Carla	4/16/18	-64	INV009876	yes	not in system
in progress	Carla	8/27/18	-197	INV022222	no	not in system
in progress	Carla	8/27/18	-197	INV022222	yes	not in system
in progress	Carla	8/27/18	-197	INV022222	no	not in system
in progress	Carla	1/14/2019	-337	INV033333	no	not in system
in progress	Carla	1/14/2019	-337	INV033333	no	not in system
in progress	Carla	1/14/2019	-337	INV033333	no	not in system

Bill's tab:

Status	Leader	Launch Date	# of days from launch	Project Number	Phase Complete	SAP #	Ship Date
complete	Bill	1/8/18	34	RBS036545	yes	154550	1/25/2018
complete	Bill	1/8/18	33	RBS036562	yes	154767	8/4/2017
complete	Bill	2/5/18	6	RBS066094	Yes	114433	2/7/2018
complete	Bill	2/5/18	6	RBS066094	Yes	123293	2/2/2018
complete	Bill	2/5/18	6	RBS066094	Yes	114432	6/2/2017
complete	Bill	2/5/18	6	RBS066094	Yes	143380	2/7/2018
in progress	Bill	5/21/2018	-99	INV077777	Yes	N/A	N/A
in progress	Bill	8/31/2018	-201	INV088888	Yes	N/A	N/A
complete	Bill	9/24/2018	-225	RBS054251	Yes	154663	10/26/2017
complete	Bill	9/24/2018	-225	RBS054251	Yes	154661	10/26/2017
complete	Bill	9/24/2018	-225	RBS054251	Yes	154674	11/9/2017
in progress	Bill	9/24/2018	-225	RBS065683

Kirby's tab:

Status	Leader	Launch Date	# of days from launch	Project Number	Phase Complete	SAP #	Ship Date
on hold	Kirby	6/5/2017	251		yes	not in system
on hold	Kirby	6/5/2017	251		yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	154561
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system	1/23/2018
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	154562
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	152154
on hold	Kirby	8/14/2017	181	POR053710	yes	152156
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	154560	1/23/2018
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
on hold	Kirby	8/14/2017	181	POR053710	yes	not in system
complete	Kirby	1/29/2018	13	FER034393	N/A	n/a
complete	Kirby	1/29/2018	13	FER034393	N/A	n/a
complete	Kirby	1/29/2018	13	FER034393	N/A	n/a
complete	Kirby	1/29/2018	13	FER034393	N/A	n/a
complete	Kirby	1/29/2018	13	FER034393	N/A	n/a
complete	Kirby	1/29/2018	13	FER034393	N/A	n/a
complete	Kirby	1/29/2018	13	FER034393	N/A	n/a
complete	Kirby	2/19/2018	-8	BEN035335	yes	155033	1/30/2018
complete	Kirby	2/19/2018	-8	BEN035335	yes	155035	1/30/2018
complete	Kirby	2/19/2018	-8	BEN035335	yes	155037	1/30/2018
complete	Kirby	2/19/2018	-8	BEN062348	yes	155461	1/30/2018
complete	Kirby	2/19/2018	-8	BEN062348	yes	155448	11/20/2017
complete	Kirby	2/26/2018	-15	BEN057582	yes	154912	1/23/2018
complete	Kirby	2/26/2018	-15	BEN057582	yes	155519	1/24/2018
complete	Kirby	2/26/2018	-15	BEN057582	yes	153412	1/30/2018
complete	Kirby	2/26/2018	-15	BEN057582	yes	153402	1/24/2018
complete	Kirby	2/26/2018	-15		yes	not in system	2/1/2018
complete	Kirby	2/26/2018	-15	BEN057582	yes	153414	1/22/2018
complete	Kirby	2/26/2018	-15	BEN057582	yes	155216	1/24/2018
complete	Kirby	2/26/2018	-15	BEN057582	yes	155393	1/23/2018
complete	Kirby	4/2/2018	-50	ROV070439	n/a	n/a
complete	Kirby	4/2/2018	-50	ROV070439	n/a	n/a
complete	Kirby	4/2/2018	-50	ROV070439	yes	n/a
complete	Kirby	4/2/2018	-50	ROV070439	n/a	n/a
complete	Kirby	4/2/2018	-50	ROV070439	n/a	n/a
complete	Kirby	4/2/2018	-50	ROV070439	n/a	n/a
complete	Kirby	4/2/2018	-50	ROV070439	n/a	n/a
complete	Kirby	4/2/2018	-50	ROV070439	n/a	n/a
in progress	Kirby	4/2/2018	-50	ROV070439	yes	155361
in progress	Kirby	4/2/2018	-50		yes	not in system
in progress	Kirby	4/2/2018	-50		yes	not in system
not started	Kirby	6/25/2018	-134	TES034424
not started	Kirby	6/25/2018	-134	TES034424
not started	Kirby	6/25/2018	-134	TES034424
not started	Kirby	6/25/2018	-134	TES034424
not started	Kirby	6/25/2018	-134	TES034424
in progress	Kirby	7/15/2018	-154		yes	not in system
not started	Kirby	7/23/2018	-162	TES034424
not started	Kirby	7/23/2018	-162	TES034424
not started	Kirby	7/23/2018	-162	TES034424
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	8/6/2018	-176	TOY060303	yes	not in system
on hold	Kirby	10/1/2018	-232	HON053688
not started	Kirby	10/8/2018	-239	LEX071423
complete	Kirby	12/25/18	-317	LEX071423	yes	not in system	1/23/2018
complete	Kirby	12/25/18	-317	LEX071423	yes	146696	1/31/2018
complete	Kirby	12/25/18	-317	LEX071423	yes	146695	1/31/2018
complete	Kirby	12/25/18	-317	LEX071423	yes	146698	1/31/2018
complete	Kirby	12/25/18	-317	LEX071423	yes	146701	1/31/2018
complete	Kirby	12/25/18	-317	LEX071423	yes	146700	1/24/2018
not started	Kirby	1/18/2019	-341	LEX071113
in progress	Kirby	2/11/2019	-365	LEX070432
in progress	Kirby	2/11/2019	-365	LEX070432
in progress	Kirby	2/11/2019	-365	LEX070432
in progress	Kirby	2/11/2019	-365	LEX070432
in progress	Kirby	2/11/2019	-365	LEX070432
not started	Kirby	4/2/2019	-415	BMW058837
not started	Kirby	6/3/2019	-477	BMW075098
not started	Kirby	6/3/2019	-477	BMW075098
not started	Kirby	9/6/2019	-572	BMW077827
cancelled	Kirby	cancelled	#VALUE!		yes	not in system

frank_AL · Feb 12, 2018

Kenton when I tried to Copy and Paste for “Carla” it put everything into one cell. Can you try to post your data again then check to ensure it will Copy and Paste in an acceptable manner?

Thanks. Frank_al

Fluff · Feb 12, 2018

@frank_AL
The data supplied copies & pastes ok for me.

frank_AL · Feb 12, 2018

Hmmm, not sure why it is a problem for me. Will try again later.

kenton71 · Feb 12, 2018

It would also help to have a "number of SKUs" column on the dashboard. That would just count the number of rows that use the same project number.

kenton71 · Feb 12, 2018

for percentages:
Phase complete = "yes", then count. If anything else, don't count.
SAP = if number, count. If text, don't count.
Ship Date = if date, count. If anything else, don't count.

frank_AL · Feb 12, 2018

kenton, I don't know why I had a problem copying the data earlier but I now have the data in a spreadsheet and will be working on a solution for you.

frank_al

frank_AL · Feb 12, 2018

kenton, give this code a try and let me know if you encounter any issues. Your sample data didn't have a Project Name in Column I so I added something just to ensure the code pulled data from that column to the dashboard.

Code:

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 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, "I")
                data(5) = ws.Cells(i, "B")
                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

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

kenton71

New Member

Excel Facts

frank_AL

Active Member

kenton71

New Member

frank_AL

Active Member

Fluff

MrExcel MVP, Moderator

frank_AL

Active Member

kenton71

New Member

kenton71

New Member

frank_AL

Active Member

frank_AL

Active Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Active Member

New Member

Active Member

MrExcel MVP, Moderator

Active Member

New Member

New Member

Active Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock