Count Unique

[mole999]

I want to do a count of unique numbers in column C
7797
7800
7428
7797
where the Font is not RED
and returns a count against a user ID in Col J
IR1-
IR2-
IR3-
Any Ideas
If RED Font is in issue in 2013 then ignore that

 

[JackDanIce]

Re: Flumoxed - Count Unique

UDF acceptable?

1 suggestion, read range into array, remove any with red font, then use a dictionary of user IDs to count against the non red numbers?

To be honest, it's a little vague! Can you expand on the example - how does the unique number map to an ID? (assuming count of rows in column C is > column J?)

 

[Aladin Akyurek]

Re: Flumoxed - Count Unique

Numbers in C and ID's in J, right?

If so, create an additional range with colors say in K, if there isn't one.


Now invoke, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($J$2:$J$100=P2,IF($K$2:$K$100=R2,$C$2:$C$100)),$C$2:$C$100),1))

where P2 houses an ID of interest and R2 a color of interest.

 

[mole999]

Re: Flumoxed - Count Unique

Thanks Aladin, I'm away from the machine that has the file currently. Just a quick rough example. IR1- would get a 1 count, due to the duplicate values, and ignoring the Red Font. IR2- would have a 3 count as all unique, and I will place the sums in a row above, expecting to do =IR1- type reference so i get a unique count against an ID

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1								IR1-	IR2-
2								1	3
3
4		Not Count	4567							IR1-
5			4985							IR2-
6			3456							IR1-
7			1267							IR2-
8			2345							IR2-
9		Not Count	3456							IR1-
Sheet1

 

[mole999]

Re: Flumoxed - Count Unique

UDF acceptable?

1 suggestion, read range into array, remove any with red font, then use a dictionary of user IDs to count against the non red numbers?

To be honest, it's a little vague! Can you expand on the example - how does the unique number map to an ID? (assuming count of rows in column C is > column J?)

I currently have eight unique User IDs, the numbers in column C would be reports against the same machine. Red already indicates there is a problem with the machine that isn't quickly rectifiable so not being penalised for not resolving. It also eliminates over counting of what needs to be resolved

 

[GJSBED]

Re: Flumoxed - Count Unique

My Solution to this is to sort the table by the numbers then insert a column in which you then use an if statement
=IF(C2=C1,0,1)

Thus bringing up a 1 for each unique number, and a zero for a repeated one.

Copy and paste values then sort the table back the way it was before

Count of unique = the sum of the numbers in the inserted column

 

[mole999]

Re: Flumoxed - Count Unique

My Solution to this is to sort the table by the numbers then insert a column in which you then use an if statement
=IF(C2=C1,0,1)

Thus bringing up a 1 for each unique number, and a zero for a repeated one.

Copy and paste values then sort the table back the way it was before

Count of unique = the sum of the numbers in the inserted column

sorry but no, sorting and sorting is not an option, the issues are date ordered for a reason

 

[Aladin Akyurek]

Re: Flumoxed - Count Unique

Create in column D either manually or with code the color coding. Once this done, you can run the array formula I forwarded.

 

[mole999]

Re: Flumoxed - Count Unique

i look forward to doing that tomorrow, thank you

how would D be referenced in =SUM(IF(FREQUENCY(IF($J$2:$J$100=P2,IF($K$2:$K$100=R2,$C$2:$C$100)),$C$2:$C$100),1))

 

[Aladin Akyurek]

Re: Flumoxed - Count Unique

i look forward to doing that tomorrow, thank you

how would D be referenced in =SUM(IF(FREQUENCY(IF($J$2:$J$100=P2,IF($K$2:$K$100=R2,$C$2:$C$100)),$C$2:$C$100),1))

Book1
	B	C	D	E	F	G	H	I	J
1							IR1-	IR2-
2							1	3
3
4	Not Count	4567	red						IR1-
5		4985	black						IR2-
6		3456	black						IR1-
7		1267	black						IR2-
8		2345	black						IR2-
9	Not Count	3456	black						IR1-
Sheet1

In H2 control+shift+enter, not just enter, copy across to I2...

=SUM(IF(FREQUENCY(IF($J$2:$J$100=H$1,IF($D$2:$D$100<>"red",$C$2:$C$100)),$C$2:$C$100),1))