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

 

[Leicester City Fox]

Hi Fluff,

Very Grateful your a star

Stay Safe

Leicester Fox

 

[Leicester City Fox]

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,"")

Hi Amit

Hi Fluff,

Very Grateful for your idea thanks

Stay Safe

Leicester Fox

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Leicester City Fox]

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.

Hi Fluff

I have notice one issue the formula works fine picking the latest date but if there two ID with the same indicators & dates it comes up showing both indicators, when it should still just show one indicator like below no duplicates :
(A) (B) (C) (D)
A123 Happy 01/01/2020 Happy
A123 Happy 01/01/2020
A123 Fair 01/01/2020 Fair

Any ideas how to tweak it so it only pick one indicator, if the indicator and date are the same?


Many Thanks

Leicester Fox

Leicester Fox

 

[Fluff]

Which indicator should it pick?
Also please update your account details to show which version of Excel you are using.

 

[CA_Punit]

Try This

Book1
	A	B	C	D	E
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	Happy
4	A123	Happy	30/01/2020
5	A123	Sad	30/01/2020	Sad	Sad
6	B234	Good	01/01/2020
7	B234	Good	30/01/2020	Good	Good
8	C345	Fair	01/01/2020
9	C345	Good	01/01/2020	Good	Good
10	C345	Fair	30/01/2020	Fair	Fair
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=IF(COUNTIFS($A$1:A2,A2,$B$1:B2,B2,$C$1:C2,MAX(($A$2:$A$10=A2)*($B$2:$B$10=B2)*($C$2:$C$10)))=1,B2,"")

Considering you have two duplicate MAX Date.

 

[Leicester City Fox]

Which indicator should it pick?
Also please update your account details to show which version of Excel you are using.

Hi Fluff

It should pick up the latest date for Column C "Indicator" only once no Duplicate, but sometimes the duplicate Indicator has same date and it end up showing both records instead of only one.

I am getting this result with your current formula example:

A	B	C	D
User id	Indicator	Date	Current result =IF(MAXIFS(C:C,A:A,A2,B:B,B2)=C2,B2,"")
A123	Happy	01/01/2020	Happy
A123	Happy	01/01/2020	Happy (Don't want two "Happy" just One)

I only want one result if the indicators (B) the same for a user id , in this case one Happy not two but if the dates in column c are the same.

Hope this helps any idea, because the formula great it just when the indicator match with same date its showing both in column D.

Many Thanks

Leicester Fox

 

[CA_Punit]

Have you tried from post 16???

 

[Fluff]

@Leicester City Fox
Please update your account details to show which version of Excel you are using.
If you cannot be bothered to help us by doing this, then why should we help you?

 

[Leicester City Fox]

@Leicester City Fox
Please update your account details to show which version of Excel you are using.
If you cannot be bothered to help us by doing this, then why should we help you?

Hi Fuluff

Afternoon

My Apologies I thought I already updated my account , I have done so and saved it this time.

Thanks

Leicester Fox