Unique entries across multiple worksheets

[m_smith_solihull]

Afternoon,

I would be grateful of your assistance please.

To set the scene, I'm trying to find a formula/ solution to count how many times a project task has been rescheduled. Presently, I have multiple worksheets (each representing a single day) in a single workbook. Ultimately I want to keep the data for each day in a separate spreadsheet so the solution will need to loop through each as appropriate.

Each worksheet (which is data from a single day) contains a list of Projects (Column D) and a list of Project Tasks (Column F).

For example:

Spreadsheet 1: Monday	Spreadsheet 1: Tuesday	Spreadsheet 1: Wednesday	Count
Project A: Task 1	Project A: Task 1		2
Project A: Task 2	Project A: Task 2	Project A: Task 2	3
Project B: Task 1	Project B: Task 1		2
		Project C: Task 2	1

Obviously the data can change from day to day, as additional projects and tasks are created / deleted etc.

Any assistance would be very welcomed as I'm hitting a brick wall.

 

[JamesCanale]

I don't love this, but it works:

MrExcelPlayground22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Tab: Main Tab					Tab: Monday			Tab: Tuesday			Tab: Wednesday			Tab: Thursday			Tab: Friday
2	Project	Task	Count			Project	Task		Project	Task		Project	Task		Project	Task		Project	Task
3	A	1	2			A	1		A	1		A	2		A	2		B	1
4	A	2	4			A	2		A	2		C	2		C	1		C	2
5	B	1	3			B	1		B	1
6	C	1	1
7	C	2	2
8
Sheet17

Cell Formulas
Range	Formula
A3:B7	A3	=LET(a,Table3,b,Table4,c,Table5,d,Table6,e,Table7,f,VSTACK(a,b,c,d,e),g,UNIQUE(f),h,SORT(SORT(g,2),1),h)
C3:C8	C3	=LET(a,Table3,b,Table4,c,Table5,d,Table6,e,Table7,f,VSTACK(a,b,c,d,e),g,FILTER(f,(CHOOSECOLS(f,1)=A3)*(CHOOSECOLS(f,2)=B3)),IFERROR(ROWS(g),""))
Dynamic array formulas.

 

[m_smith_solihull]

Thanks - now I need to be able to loop this through different workbooks, and worksheets within each workbook - is this possible please?

 

[JamesCanale]

You sure can do that. Put the tables wherever they need to be. Excel will manage them. Separate workbooks is a little bit more pesty - requiring refreshes - but excel can manage it.

The key to making this easy is to use the "Format as Table" feature.

 

[m_smith_solihull]

Thank-you James. Have a good day!