Need a Formula that will count the duplicate values only once relating to unique ID number and show it only once

[Leicester City Fox]

Hi All

Good Morning

Need a Formula that will count the duplicate values only once relating to unique ID number and show it only once

I am looking for a Formula which will only show the Indicator (Column B) once in there are duplicates values of if there are no duplicates just show result in Column C for each User ID (Column A).

I only want it to count the indicator once looking for result of the formula in Column C if it relevant to the specific User id.

A	B	C
User ID	Indicator	Result Wanted ( Formula needed)
A123	Happy	Happy
A123	Happy
A123	Sad	Sad
B234	Good	Good
B234	Good
C345	Fair	Fair
C345	Good	Good
C345	Fair

Many Thanks for you ideas

The Leicester Fox

 

[Fluff]

How about
=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,B2,"")

 

[Leicester City Fox]

Hi Fluff

Afternoon

Thank you for the Formula , worked a treat .. your a star

Stay Safe

The Leicester Fox

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Leicester City Fox]

How about
=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,B2,""

Hi Fluff

That.s just what I need

In relation to my original request I have another issue in that not only do I want the duplicate to show once but it must be the one with the latest date that matches the user id see table below:

A	B	C	D	Comments
User ID	Indicator	Dates	Result Wanted ( Formula needed with latest date )
A123	Happy	01/01/2020
A123	Happy	30/01/2020	Happy	picks latest date indicator
A123	Sad	30/01/2020	Sad	only match date correct
B234	Good	01/01/2020
B234	Good	30/01/2020	Good	picks latest date indicator
C345	Fair	01/01/2020
C345	Good	01/01/2020	Good	only match date correct
C345	Fair	30/01/2020	Fair	picks latest date indicator

I am looking for results in column D owing to latest date relating to the user id .

Any ideas ?

Thanks

Leicester Fox

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use.

 

[Leicester City Fox]

What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use.

Hi Fluff

Good Morning

My version is MS Office professional 2019.

Your formula works I just need it to pick any duplicates with the latest start date.

Many Thanks

Leicester Fox

 

[Amit Tandon]

Enter below formula in cell D2 and copy down

=IF(ROWS($1:1)=LOOKUP(2,1/((A$2:A$10=A2)*(B$2:B$10=B2)),ROW(A$2:A$10)-ROW(A$2)+1),B2,"")

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D
1	User ID	Indicator	Dates	Result Wanted ( Formula needed with latest date )
2	A123	Happy	01/01/2020
3	A123	Happy	30/01/2020	Happy
4	A123	Sad	30/01/2020	Sad
5	B234	Good	01/01/2020
6	B234	Good	30/01/2020	Good
7	C345	Fair	01/01/2020
8	C345	Fair	30/01/2020	Fair
9	C345	Fair	30/01/2020	Fair
Data

Cell Formulas
Range	Formula
D2:D9	D2	=IF(MAXIFS(C:C,A:A,A2,B:B,B2)=C2,B2,"")

Please remember to update your account details to show your version of office, as this affects what functions you have available.

 

[Leicester City Fox]

What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use.