Count Unique based on two/three different criteria

[Melimob]

Hi There

I have a data set with multiple columns where I need to count the unique fields in a seperate tab based on multiple criteria.

[TABLE="width: 372"]
<tbody>[TR]
[TD]PNR[/TD]
[TD]Booking Date[/TD]
[TD]Booking Agent[/TD]
[TD]MAX Booking Agent ID[/TD]
[/TR]
[TR]
[TD]AAVBHD01[/TD]
[TD="align: right"]10/09/2018[/TD]
[TD]AO[/TD]
[TD="align: right"]99355[/TD]
[/TR]
[TR]
[TD]AAWTZK01[/TD]
[TD="align: right"]05/09/2018[/TD]
[TD]BM[/TD]
[TD="align: right"]86788[/TD]
[/TR]
[TR]
[TD]ABDYNH01[/TD]
[TD="align: right"]10/09/2018[/TD]
[TD]GM[/TD]
[TD="align: right"]23023[/TD]
[/TR]
[TR]
[TD]ABGRLL01[/TD]
[TD="align: right"]05/09/2018[/TD]
[TD]SW[/TD]
[TD="align: right"]25238[/TD]
[/TR]
[TR]
[TD]ADVQJQ01[/TD]
[TD="align: right"]04/09/2018[/TD]
[TD]AO[/TD]
[TD="align: right"]99355[/TD]
[/TR]
[TR]
[TD]AENSSR01[/TD]
[TD="align: right"]10/09/2018[/TD]
[TD]JP[/TD]
[TD="align: right"]14647[/TD]
[/TR]
[TR]
[TD]AFFGNM01[/TD]
[TD="align: right"]05/09/2018[/TD]
[TD]PB[/TD]
[TD="align: right"]28180[/TD]
[/TR]
[TR]
[TD]AGJTEJ01[/TD]
[TD="align: right"]04/09/2018[/TD]
[TD]SD[/TD]
[TD="align: right"]23509[/TD]
[/TR]
[TR]
[TD]AGJTEJ01[/TD]
[TD="align: right"]04/09/2018[/TD]
[TD]SD[/TD]
[TD="align: right"]23509


[/TD]
[/TR]
</tbody>[/TABLE]

1 - I want to only take the first 6 characters from PNR column
2 - I want to summarise how many unique times column A (PNR) + column D (MAX ID) appear together
3 - I also want to summarise point 2 plus month of booking date

I can do it by copying to a diff sheet, removing duplicates and then using countif formulas but in case I wanted to just read from the raw data tab without copying/removing, is there any formula I can use?

Many thanks
Melimob

 

[Aladin Akyurek]

What does 1 mean - a listing of the first six items from PNR?

What is the expected value for 2?

Item 3 needs some elaboration what it amounts to.

 

[Melimob]

Hi Aladin

Point 1 means I only want to analyse unique codes based on the first 6 characters (i.e. exclude last 2 digits..01)
Point 2 so for example, this set should say: Max ID 23509 = 1 PNR since Max ID 23509 = 1 PNR since AGJTEJ should only be counted once as unique for this MAXID
Point 3 is: In Sept, Max ID 23509 = 1 PNR since AGJTEJ should only be counted once

Obviously my data has a whole load more where PNRs may appear multiple times for the same MAXID (counted as one) or different MAXID's (counted per MAXID).

Many thanks

What does 1 mean - a listing of the first six items from PNR?

What is the expected value for 2?

Item 3 needs some elaboration what it amounts to.

 

[Melimob]

Hi - thanks all but I found something on internet and works a treat!
=SUM(IF([@[Max ID ]]=Table2[MAX Booking Agent ID], 1/(COUNTIFS(Table2[MAX Booking Agent ID], [@[Max ID ]], Table2[PNR Stripped], Table2[PNR Stripped])),0))

CTRL+SHFT+ENTER

Thank you

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I
1	PNR	Booking Date	Booking Agent	MAX Booking Agent ID		pnr	7		9/1/2018
2	AAVBHD01	9/10/2018	AO	99355		AAVBHD01	mba id	pnr #	pnr #/month
3	AAWTZK01	9/5/2018	BM	86788		AAWTZK01	14647	1	1
4	ABDYNH01	9/10/2018	GM	23023		ABDYNH01	23023	1	1
5	ABGRLL01	9/5/2018	SW	25238		ABGRLL01	23509	1	1
6	ADVQJQ01	9/4/2018	AO	99355		ADVQJQ01	25238	1	1
7	AENSSR01	9/10/2018	JP	14647		AENSSR01	28180	1	1
8	AFFGNM01	9/5/2018	PB	28180		AFFGNM01	86788	1	1
9	AGJTEJ01	9/4/2018	SD	23509		AGJTEJ01	99355	2	2
10	AGJTEJ01	9/4/2018	SD	23509
Sheet1

In F2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(IF($A$2:$A$10<>"",MATCH(LEFT($A$2:$A$10,6),LEFT($A$2:$A$10,6),0)),ROW($A$2:$A$10)-ROW($A$2)+1),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS($1:1))),"")

In G1 control+shift+enter:

=SUM(IF(FREQUENCY(D2:D10,D2:D10),1))

In G3 control+shift+enter and copy down:

=IF(ROWS($1:1)>$G$1,"",MIN(IF(ISNA(MATCH($D$2:$D$10,$G$2:G2,0)),$D$2:$D$10)))

In H3 control+shift+enter and copy down:

=IF($G3="","",SUM(IF(FREQUENCY(IF($D$2:$D$10<>"",IF($D$2:$D$10=$G3,MATCH($A$2:$A$10,$A$2:$A$10,0))),ROW($A$2:$A$10)-ROW($A$2)+1),1)))

In I3 control+shift+enter and copy down:

=IF($G3="","",SUM(IF(FREQUENCY(IF($D$2:$D$10<>"",IF($D$2:$D$10=$G3,IF($B$2:$B$10-DAY($B$2:$B$10)+1=$I$1,MATCH($A$2:$A$10,$A$2:$A$10,0)))),ROW($A$2:$A$10)-ROW($A$2)+1),1)))