Check occurrence/duplicates against a list

[ukguy]

Hi guys,

I'm trying to determine if each value in a list (a, b, c) appears against different types. For example, I can see that value a) is duplicated (type 1, type 2). I can also see value b is duplicated (2, 4). If a value (a is appearing twice against the same type then it can be ignored). I need to be able to spit out what type each one matches (see below)


Clean, Type, Duplicated
a 1 1,3
b 2 3,4
c 2 no
a 3 no
b 4 no
a 1 no

[Pepe Le Mokko]

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

[ukguy]

Anyone?

[biex]

See my sample

I avoid using any volatile formula

A few helper column

example on how i remove the duplicate is on cell E2

=IF(COUNTIF(A$1:A2,IF(COUNTIF(A:A,A2)>1,A2,""))>1,"",IF(COUNTIF(A:A,A2)>1,A2,""))