Concatenate 2 ranges with a countif formula.

**DonkeyOte** · 08-17-2010, 07:40 AM

Originally Posted by DonkeyOte post#3

If you're trying to ascertain as to whether or not duplicates exist you can test the output of the above to the count of non-blank/non-null entries*
(method for doing this will depend on whether range contains Nulls and whether or not if A is not blank B might be and vice versa)

Does the above not cover this ?

ie compare unique count to count of non-blanks - if not the same you have duplicates.

**DonkeyOte** · 08-17-2010, 07:43 AM

Another alternative to the above would be to use a Frequency Array

=COUNT(IF(FREQUENCY(IF(A7:A9&C7:C9<>"",MATCH(A7:A9&"@"&C7:C9,A7:A9&"@"&C7:C9,0)),ROW(A7:A9)-ROW(A7)+1)>1,1))
confirmed with CTRL + SHIFT + ENTER

(if greater than 0 then duplicates)

**snb** · 08-17-2010, 08:41 AM

A unique list in column D based on the values in columns A and C

Sub unique()
  [D1:D18] = [if(countif(offset(D1,,,row(A1:A18)-if(row(D1:D18)=1,0,1)),A1:A18 & C1:C18)=0,A1:A18 & C1:C18,"")]
End Sub

**Xrull** · 08-17-2010, 11:12 PM

DonkeyOte,
The last formula you gave me works perfectly. I didn't know I could use @ to concatenate 2 ranges.
Thanks,
Xrull

**DonkeyOte** · 08-18-2010, 01:39 AM

Concatenation is achieved with & ... the use of @ is to delimit the two values from one another so as to avoid false positives

Consider

A1: Car C1: pet
A2: Carpet C1: [blank]

Comparing A1:A2&C1:C2 it would appear that we have 2 instances of Carpet whereas in fact we have just one.

To avoid the false positive we delimit A & C with a character otherwise not found in the strings - this way Car@pet and Carpet@ do not get flagged as a match.