Hi!

Thanks for the feedback!

It works in my case

One thing to note about this method (and what I was alluding to when I said:
Maybe this)

Take this example:

..........A..........B..........C
1......11..........1...........
2........1..........1..........1

Using A1 & B1 & C1 as the criteria, this formula will return a result of 2
while the entries clearly do not match:

=SUMPRODUCT(--(A1:A5&B1:B5&C1:C5=A1&B1&C1))

The criteria would be 111 (=A1&B1&C1)

And both rows would evaluate as 111

To prevent "false positives" you can concatenate a unique character between
ranges like this:

=SUMPRODUCT(--(A1:A5&"^"&B1:B5&"^"&C1:C5=A1&"^"&B1&"^"&C1))

The unique character should be one (or more than one: "^^") that is not
likely to appear in the data.

So now the criteria would be:

11^1^

And row 2 would evaluate to:

1^1^1

Those do not match now!

Biff

"Kasama" wrote in message
...
That's brilliant Biff. It works in my case, and I'm surprised to see it
works
for records that include text in some columns. I put your formula in the
equivalent of yungexec's H2 position with absolute references for ranges
before the equals sign, and filled down. This gives in colH '1' for
records
that occur only once and '2' or higher for records that occur more than
once.
I can use conditional formatting to highlight in red any cells with values
higher than 2. Thanks. I guess youngexec then just needs a formula in I2
to
work out the number of duplicated records.

"Biff" wrote:

Maybe this:

=SUMPRODUCT(--(C2:C100&D2:D100&E2:E100&F2:F100&G2:G100=C2&D2&E2& F2&G2))

This assumes the entries are in the exact same order:

1...2...3...4...5
1...2...3...4...5

Won't work if they're not.

1...2...3...4...5
2...1...4...5...3

Biff

"yungexec" wrote
in
message ...

I have 100 rows of data. The first record occupies range C2:G2. The
next record occupies row C3:G3 and so on through C1000:G1000. I would
like to count the number of duplicate records found as you proceed down
the list.

For example, if C2:G2 contains 1,2,3,4,5; I would like to count how
many records (thru the next 999) are also 1,2,3,4,5. I would like to
place this count in cell I2.

Can anyone help on this?

Thanks :)


--
yungexec
------------------------------------------------------------------------
yungexec's Profile:
http://www.excelforum.com/member.php...o&userid=22593
View this thread:
http://www.excelforum.com/showthread...hreadid=565444