Array formula to display 1 if concatentaed values differ..
 

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

Array formula to display 1 if concatentaed values differ..
 

Maybe:-

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

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

Mike

"bony_tony" wrote:

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

Array formula to display 1 if concatentaed values differ..
 

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.

"Mike H" wrote in message
...
Maybe:-

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

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

Mike

"bony_tony" wrote:

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

Array formula to display 1 if concatentaed values differ..
 

On 12 Dec, 14:22, "Stephen" <none wrote:
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.

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