Array formula to display 1 if concatentaed values differ..

  • Thread starter Thread starter bony_tony
  • Start date Start date
B

bony_tony

Hi,
I'm having a bit of trouble creating an array formula
I have some data in my sheet like this;

Reference Type
11KING DD
11KING CC
13MANA DD
13MANA DD
23WES1 CH
2BENTE DD
2BENTE DD
2BENTE CH
2SIMP1 DD

Next to each row I want a '1' displayed if the reference has more than
1 type.
i.e a 1 should be put next to the accounts '11KING', and '2BENTE'.

Cheers
Tony
 
Maybe:-

With your list in column A put this in B1 and drag down

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

Mike
 
This would give a 1 for multiple occurrences of a reference even where the
type was the same, e.g. 13MANA (both type DD), which I think the OP doesn't
want.
 
This would give a 1 for multiple occurrences of a reference even where the
type was the same, e.g. 13MANA (both type DD), which I think the OP doesn't
want.
Click to expand...

Correct, but I could concatenate the 2 values to see if there is more
than one.. should have thought of that myself...
Thanks
Tony
 
Back
Top