Concatenate 2 ranges with a countif formula.

[Xrull]

Good day,

Is there a way to concatenate 2 ranges and do a countif formula?
My problem is, I have data in column A and data in column B, both of them can be concatenated to give me a unique list in column D

=A7&B7

.

II used this formula I found on Chip Pearson's site:

=IF(MAX(COUNTIF(INDIRECT("D7:A"&(MAX((D7:D2000<>"")*ROW(D7:D2000)))),INDIRECT("A2:A"&(MAX((D7:D2000<>"")*ROW(D7:D2000))))))>1,"Duplicates","No Duplicates")

But is there a code or a more advanced formula that can be used to eliminate the helper column D?


Thanks,
Xrull

[zbor]

You can use COUNTIFS with XL 2007:

=COUNTIFS($A$7:$A$15,A7, $B$7:$B$15,B7)

[DonkeyOte]

It seems you want a unique count in which case

XL2007+

=SUMPRODUCT(($A$7:$A$15&$B$7:$B$15<>"")/COUNTIFS($A$7:$A$15,$A$7:$A$15&"",$B$7:$B$15,$B$7:$B$15&""))

Pre XL2007

=SUMPRODUCT(--($A$7:$A$15&$B$7:$B$15<>""),--(MATCH($A$7:$A$15&"@"&$B$7:$B$15,$A$7:$A$15&"@"&$B$7:$B$15,0)=(ROW($A$7:$A$15)-ROW($A$7)+1)))

edit:

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)

[Xrull]

DonkeyOte,

I tried your formula, but I need it to prevent a duplicate based on the two adjacent cells.
Column A has the persons name, and column C has a ticket number. If the person's name is in cell A1, and ticket number is in C1, and the person's name is in A500, and he/she uses the same number in C500 the formula would indicate that the employee had used that ticket number earlier.

I inserted another column so the formula now looks like this:

=SUMPRODUCT(--($A$7:$A$15&$C$7:$C$15<>""),--(MATCH($A$7:$A$15&"@"&$C$7:$C$15,$A$7:$A$15&"@"&$C$7:$C$15,0)=(ROW($A$7:$A$15)-ROW($A$7)+1)))

Thanks,
Xrull

[DonkeyOte]

I'm not sure if the above is a question or not ?

It would seem you have adapted the formula to use Column C rather than B - other than that they would appear to be identical, no ?

If your intention / requirement is to run a duplicate flag on each row I would suggest alternatives.

If needed post a sample file and outline desired results.

[Xrull]

DonkeyOte,
I've uploaded a file with an example.
Thanks,
Xrull

[DonkeyOte]

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]

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]

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