Looking for duplicate ranges

[yungexec]

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

 

[Guest]

I don't have an answer but I do have a similar problem. I'm sure others have
faced it. My approach was firstly to think of a macro but I realised it would
take a long time for a macro to loop through the cells comparing values, for
every value! Then I thought of sorting the data so that duplicate records
would sort one above the other, and I can do this with my records. Can you
sort your data in this way? I heven't done it yet, but I was then going to
try applying conditional formatting to the cells so that any record identical
to the one above would be highlighted in red.
But does anyone else have a better way?

 

[Biff]

Maybe this:

=SUMPRODUCT(--(C2:C100&D2100&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

 

[Guest]

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]

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

 

[yungexec]

Biff,

Thanks for the information! I'm finding that having so many formulas
calculating at one time is bogging down my worksheet. Tell me if this
is possible. I still need to count duplicate ranges. Lets say I have
the following 100 rows of data that are similar to the following 5
rows:

1,2,4,5,7
2,3,5,7,8
1,2,4,5,6
5,7,9,11,12
1,2,4,5,7

I would like to count how many duplicate ranges containing all 5
numbers(2). How can I get a total count into one cell so that my
worksheet doesn't get bogged down? Again, I'm just looking to count
the total number of duplcate ranges in 100 rows of data.

 

[Biff]

I'm finding that having so many formulas

calculating at one time is bogging down my worksheet.

I only suggested one formula! But reading between the lines I think I know
what you did. But even then, 100 of those formulas shouldn't be very
resource expensive.

So, what you want is to count duplicates in general rather than duplicates
based on a specific criteria (which is how I interpreted your post)

Hmmm....

Well, the only way I can see how to do this requires a helper column where
you concatenate each row of data:

H2 = =C2&D2&E2&F2&G2

Copy down 100 rows, then, to count the dupes:

=SUMPRODUCT(--(H2:H101<>""),--(COUNTIF(H2:H101,H2:H101)>1))

Biff