Morning, Hoping to get some help on a piece of working I'm putting together. So: In Column R, I have a list of ROLES responsible for that ROW, IF > 1 they're on a new line. I am currently COUNTING them by using a hidden column with the following =IF([@Role]="",0,LEN(R7)-LEN(SUBSTITUTE(R7,CHAR(10),""))+1) This gives me the correct roles per row. What I need help with is: on my MASTER COUNT I need it to be unique roles. Currently, if R2 & R3 were the same 3 roles, it would COUNT 6 when I need it to be 3. Hope that makes sense.

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

COUNT Unique Values in a Column where CELLS contain CHAR(10)

Thread starter MrOaks
Start date Sep 25, 2018
Tags

column count roles row unique

MrOaks

New Member

Sep 25, 2018

Morning,

Hoping to get some help on a piece of working I'm putting together.

So:
In Column R, I have a list of ROLES responsible for that ROW, IF > 1 they're on a new line.

I am currently COUNTING them by using a hidden column with the following

Code:

=IF([@Role]="",0,LEN(R7)-LEN(SUBSTITUTE(R7,CHAR(10),""))+1)

This gives me the correct roles per row.

What I need help with is: on my MASTER COUNT I need it to be unique roles.

Currently, if R2 & R3 were the same 3 roles, it would COUNT 6 when I need it to be 3.

Hope that makes sense.