COUNTIF and Partial text arrays

[jpfuse]

I'm a bit new at using arrays in excel formulas. I need to check for duplicates in a range of cells. The tricky part is i want to check just a part of the text in the cells. I started with the following:

A
1 HS
2 HS
3 LS
4 LS
5 TERT

I used the following formula: =IF(SUMPRODUCT((COUNTIF(A1:A5,A1:A5)>1)+0)>1,"Duplicates exist","NO Duplicates")
The next range of cells is what i need to lookup.

A
1 HS BKR 1
2 HS BKR 2
3 LS BKR 1
4 LS BKR 2
5 TERT

I want to check to see if there are more than one cell with "HS" or "LS" in it, regardless of what is after.

 

[Domenic]

Try...

=IF(SUMPRODUCT((COUNTIF(A1:A5,{"HS","LS"}&"*")>1)+0)>1,"Duplicates exist","NO Duplicates")

 

[T. Valko]

Try...

=IF(SUMPRODUCT((COUNTIF(A1:A5,{"HS","LS"}&"*")>1)+0)>1,"Duplicates exist","NO Duplicates")

Not real sure what the OP is wanting to do...but...

With this data:

Book1
	A
2	HS BKR
3	_
4	LS BKR
5	LS BKR
6	TERT
Sheet2

That formula returns "No Duplicates".

The LS entry is a dupe.

Maybe something like this...

=IF(OR(COUNTIF(A2:A6,"HS*")>1,COUNTIF(A2:A6,"LS*")>1),"Duplicates","No Duplicates")

 

[Domenic]

Thanks Biff... That should have been...

=IF(SUMPRODUCT((COUNTIF(A1:A5,{"HS","LS"}&"*")>1)+0)>0,"Duplicates exist","NO Duplicates")

 

[Ron Coderre]

With your list in A1:A5
this regular formula tests if there is more than 1 instance of text beginning with "HS" or "LS"...

B1: =IF(MAX(COUNTIF(A1:A5,{"HS*","LS*"}))>1,"Dupes","No dupes")

Does that help?

 

[jpfuse]

wow, thanks guys. works great. it raises another questions for me though. originally i had tried to define a name which was an array of the first two letters of these rows. i would then use that name to compare with, but it kicked back some errors and i couldn't figure it out.

Here is the name:
_name = =LEFT('Data Entry'!A1:A5,2)
And then the formula:
=IF(SUMPRODUCT((COUNTIF(_name,_name)>1)+0)>1,"Duplicates exist","NO Duplicates")
This gives a #VALUE error and i can't figure out why.

 

[T. Valko]

wow, thanks guys. works great. it raises another questions for me though. originally i had tried to define a name which was an array of the first two letters of these rows. i would then use that name to compare with, but it kicked back some errors and i couldn't figure it out.

Here is the name:
_name = =LEFT('Data Entry'!A1:A5,2)
And then the formula:
=IF(SUMPRODUCT((COUNTIF(_name,_name)>1)+0)>1,"Duplicates exist","NO Duplicates")
This gives a #VALUE error and i can't figure out why.

The COUNTIF function won't work with a defined name like that.

 

[Domenic]

The COUNTIF function only accepts a Range object as its first argument, not an array. Try something like this instead...

=IF(SUM(IF(FREQUENCY(IF(A1:A5<>"",MATCH(LEFT(A1:A5,2),LEFT(A1:A5,2),0)),ROW(A1:A5)-ROW(A1)+1)>1,1)),"Duplicates","No Duplicates")

...confirmed with CONTROL+SHIFT+ENTER.

 

[jpfuse]

awww... ok... yeah i am still learning where i can and can't use arrays. they are very powerful tools to use, but there are definitely restrictions as to how they can be applied and manipulated. thanks again for everyone's input.

 

[Domenic]

awww... ok... yeah i am still learning where i can and can't use arrays. they are very powerful tools to use, but there are definitely restrictions as to how they can be applied and manipulated. thanks again for everyone's input.

Also, array formulas can take longer to calculate, and are not as efficient. For this reason, it may be better to use Biff's solution.