Distinct count with lookup

[carlidee]

Hello,

I have one tab with raw data and I'm looking to create an additional summary tab in the file that creates a distinct count of 'cases' by name

Thank you for your help,
Carli

Example:

Tab 1: Table with raw data

Name	Case
Chip Jones	ABC1
Chip Jones	ABC1
Chip Jones	DEF2
Sam Smith	GHI3
Sam Smith	GHI3
Tim Sanders	JKL4
Tim Sanders	MNO5
Tim Sanders	MNO5
Tim Sanders	PQR6

Tab 2: Summary, desired output:

Name	Distinct count of cases
Chip Jones	2​
Sam Smith	1​
Tim Sanders	3​

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Name	Case			Name	Distinct count of cases
2	Chip Jones	ABC1			Chip Jones	2
3	Chip Jones	ABC1			Sam Smith	1
4	Chip Jones	DEF2			Tim Sanders	3
5	Sam Smith	GHI3
6	Sam Smith	GHI3
7	Tim Sanders	JKL4
8	Tim Sanders	MNO5
9	Tim Sanders	MNO5
10	Tim Sanders	PQR6
Lists

Cell Formulas
Range	Formula
F2:F4	F2	=SUMPRODUCT((IF($A$2:$A$10=E2,1/COUNTIFS($A$2:$A$10,E2,$B$2:$B$10,$B$2:$B$10))))

 

[carlidee]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Name	Case			Name	Distinct count of cases
2	Chip Jones	ABC1			Chip Jones	2
3	Chip Jones	ABC1			Sam Smith	1
4	Chip Jones	DEF2			Tim Sanders	3
5	Sam Smith	GHI3
6	Sam Smith	GHI3
7	Tim Sanders	JKL4
8	Tim Sanders	MNO5
9	Tim Sanders	MNO5
10	Tim Sanders	PQR6
Lists

Cell Formulas
Range	Formula
F2:F4	F2	=SUMPRODUCT((IF($A$2:$A$10=E2,1/COUNTIFS($A$2:$A$10,E2,$B$2:$B$10,$B$2:$B$10))))

This is perfect - thank you for the quick reply!

 

[JEC]

Map1
	A	B	C	D	E	F
1	Name	Case			Name	Case
2	Chip Jones	ABC1			Chip Jones	2
3	Chip Jones	ABC1			Sam Smith	1
4	Chip Jones	DEF2			Tim Sanders	3
5	Sam Smith	GHI3
6	Sam Smith	GHI3
7	Tim Sanders	JKL4
8	Tim Sanders	MNO5
9	Tim Sanders	MNO5
10	Tim Sanders	PQR6
Blad1

Cell Formulas
Range	Formula
F2:F4	F2	=SUMPRODUCT(($A$2:$A$10=E2)/COUNTIF($B$2:$B$10,$B$2:$B$10))
E2:E8	E2	=IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$10),0)),"")

 

[Fluff]

This is perfect - thank you for the quick reply!

You're welcome & thanks for the feedback.

 

[carlidee]

Map1
	A	B	C	D	E	F
1	Name	Case			Name	Case
2	Chip Jones	ABC1			Chip Jones	2
3	Chip Jones	ABC1			Sam Smith	1
4	Chip Jones	DEF2			Tim Sanders	3
5	Sam Smith	GHI3
6	Sam Smith	GHI3
7	Tim Sanders	JKL4
8	Tim Sanders	MNO5
9	Tim Sanders	MNO5
10	Tim Sanders	PQR6
Blad1

Cell Formulas
Range	Formula
F2:F4	F2	=SUMPRODUCT(($A$2:$A$10=E2)/COUNTIF($B$2:$B$10,$B$2:$B$10))
E2:E8	E2	=IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$10),0)),"")

I've always wondered if there was a way to pull out the distinct names, per your instruction for cell E2! This is fantastic to learn!! Thank you

 

[Fluff]

@JEC try changing B5 to ABC1

 

[JEC]

Lol take Fluff's formula for distinct count