Unique count based on different cells

[Sahitya]

I need to get unique value of Suppliers based on the different columns

Input:
[TABLE="class: grid, width: 807"]
<tbody>[TR]
[TD]Functions[/TD]
[TD]Suppliers[/TD]
[TD]Sites[/TD]
[TD]Due Date[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Apple[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Orange[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]4/13/2020[/TD]
[/TR]
[TR]
[TD]End User Services[/TD]
[TD]Watermelon[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Pineapple[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Kiwi[/TD]
[TD]Mulgrave[/TD]
[TD="align: right"]4/13/2018[/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD]Fig[/TD]
[TD]Mulgrave[/TD]
[TD="align: right"]4/13/2018[/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD]Kiwi[/TD]
[TD]Mulgrave[/TD]
[TD="align: right"]4/13/2018[/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD]Kiwi[/TD]
[TD]California[/TD]
[TD="align: right"]4/13/2018[/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD]Kiwi[/TD]
[TD]California[/TD]
[TD="align: right"]4/13/2018[/TD]
[/TR]
[TR]
[TD]Research[/TD]
[TD]Banana[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]3/30/2012[/TD]
[/TR]
[TR]
[TD]Operations[/TD]
[TD]Pear[/TD]
[TD]NA[/TD]
[TD="align: right"]9/15/2019[/TD]
[/TR]
[TR]
[TD]Software Services[/TD]
[TD]Berry[/TD]
[TD]Sydney[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Software Services[/TD]
[TD]Berry[/TD]
[TD]Sydney[/TD]
[TD="align: right"]7/9/2018[/TD]
[/TR]
[TR]
[TD]Software Services[/TD]
[TD]Grape[/TD]
[TD]Sydney[/TD]
[TD="align: right"]7/9/2018[/TD]
[/TR]
[TR]
[TD]Software Services[/TD]
[TD]Berry[/TD]
[TD]Arizona[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Software Services[/TD]
[TD]Berry[/TD]
[TD]Arizona[/TD]
[TD="align: right"]12/12/2017[/TD]
[/TR]
</tbody>[/TABLE]


Output:

[TABLE="class: grid, width: 437"]
<tbody>[TR]
[TD][/TD]
[TD]2019+[/TD]
[TD]2018[/TD]
[TD]2017-[/TD]
[TD]Not known[/TD]
[/TR]
[TR]
[TD]Unique Suppliers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Condition for output :
A = Function (Specific), B = Unqiue, C = different, D = date

For Example : Suppliers unique count when 'Function' is 'Operations' , 'Sites' are different and 'Due Date' as per the column header (2019+, 2018, 2017-, #N/A to be mapped under Not Known.

 

[Sahitya]

X,#N/A - it is considered as two sites. Even x, y is 2 sites

 

[Aladin Akyurek]

X,#N/A - it is considered as two sites. Even x, y is 2 sites

operations,apple,#N/A,1/7/2018
operations,apple,london,1/12/2018

What do we have here: 1 or 2 unique records?

 

[Sahitya]

operations,apple,#N/A,1/7/2018
operations,apple,london,1/12/2018

What do we have here: 1 or 2 unique records?

Yes Aladin sir. You are absolutely correct.

Read blog about you. Hats off you to you sir.

 

[Aladin Akyurek]

Yes Aladin sir. You are absolutely correct.

Read blog about you. Hats off you to you sir.

Thnaks for the kind words. Just to be sure: Do the formulas I proposed satisfy your goal?

 

[Sahitya]

operations,apple,#N/A,1/7/2018
operations,apple,london,1/12/2018

What do we have here: 1 or 2 unique records?

we will have 2 unique records.

 

[Aladin Akyurek]

we will have 2 unique records.

G3: Control+shift+enter (CSE)...

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)>=2019,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

G5: CSE and copy down...

=IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)>=2019,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

H3: CSE...

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)=2018,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

H5: CSE and copy down...

=IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)=2018,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

I3: CSE...

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)<=2017,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))


I5: CSE and copy down...

=IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)<=2017,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

J3: CSE...

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNA($D$2:$D$17),IF($A$2:$A$17=$F$1,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0)))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

J5: CSE and copy down...

=IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNA($D$2:$D$17),IF($A$2:$A$17=$F$1,MATCH($B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),$B$2:$B$17&"|"&IF(ISNA($C$2:$C$17),"#",$C$2:$C$17),0)))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

 

[Sahitya]

Sir, your formula is working perfectly alright but i have small query, currently formula is defined for fixed cells like B2:B17. But rows will increasing is it possible to derive the formula as B:B

 

[Sahitya]

@Aladin Akyurek
Sir, i have two issues now
1)
currently formula is defined for fixed cells like B2:B17. But rows will increasing is it possible to derive the formula as B:B
2) If operations,apple,#N/A,1/7/2018
operations,apple,london,1/12/2018 - it considered as two
But if data is like
operations,apple,#N/A,1/7/2018
operations,apple,Sweden,1/12/2018
operations,apple,london,1/12/2018 - it needs to be considered as 3 but still considered as two

 

[Aladin Akyurek]

Sir, your formula is working perfectly alright but i have small query, currently formula is defined for fixed cells like B2:B17. But rows will increasing is it possible to derive the formula as B:B

01. What follows assumes that the data is located in Sheet1. Adjust the sheet name if necessary.

02. Define Lrow in the Name Manager as referring to...

=MATCH(REPT("z",255),Sheet1!$A:$A)

03. Define Functions as...

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

04. Define Suppliers as...

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

05. Define Sites as...

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrow)

06. Define DueDates as...

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,Lrow)

07. Define Ivec as...

=ROW(Functions)-ROW(INDEX(Functions,1,1))+1

The foregoing enables to install a dynamic set up, i.e. a set up which adjusts itself to record addition or deletion.

The formulas can now be rewritten in terms of the dynamic named ranges specified in 1 to 7.

08. G3, control+shift+enter…

=SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)>=2019,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),1))

09. G5, control+shift+enter and copy down...

=IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)>=2019,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),Ivec),ROWS($1:1))),"")

10. H3, control+shift+enter…

=SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)=2018,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),1))

11. H5, control+shift+enter and copy down...

=IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)=2018,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),Ivec),ROWS($1:1))),"")

12. I3, control+shift+enter…

=SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)<=2017,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),1))

13. I5, control+shift+enter and copy down...

=IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNUMBER(DueDates),IF(Functions=$F$1,IF(YEAR(DueDates)<=2017,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0))))),Ivec),Ivec),ROWS($1:1))),"")

14. J3, control+shift+enter…

=SUM(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNA(DueDates),IF(Functions=$F$1,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0)))),Ivec),1))

15. J5, control+shift+enter and copy down...

=IFERROR(INDEX(Suppliers,SMALL(IF(FREQUENCY(IF(1-(Suppliers=""),IF(ISNA(DueDates),IF(Functions=$F$1,MATCH(Suppliers&"|"&IF(ISNA(Sites),"#",Sites),Suppliers&"|"&IF(ISNA(Sites),"#",Sites),0)))),Ivec),Ivec),ROWS($1:1))),"")