Formula for comparing rows

[Oeysbrei]

Hi,

I need help with a formula for comparing rows in same colums.
Samplefile attached.

Any help will be much appreciated.

Oeysbrei

[Richard Buttrey]

Hi,

Perhaps a more elegant formula for column L would be

=SUMPRODUCT((A2:J2)*COLUMN(A2:J2))

then alongside in M2 copied down
=COUNTIF($M$2:$M$32,M2)

Then filter column M for values > 1 to see duplicates

[Oeysbrei]

Hi,

I´ve tested youre formula, but couldent make it work
I´ve attached a new samplefile, so if you have time, I would be a happy man if you could arrange it for me to study
I also forgot saying that I need to know the possition of matching rows

Thanks for youre help so far

Best regards
Oeysbrei

[Richard Buttrey]

Sorry, that should have read

=COUNTIF($L$2:$L$32,L2)

in M2

[Oeysbrei]

Richard Buttrey,

I made it work, but it doesent tell the position of matching rows.
I need to know that because I will have up to 60 000 rows where i´m gonna use it.

Oeysbrei

[Richard Buttrey]

Hi,

You can tell the position by filtering column M for values > 1 and then marking them.

Regards

[Oeysbrei]

Richard Buttrey,

appreciate your help, but I'm an amateur at excel, and do not know how to do this

hope you have patience with me

Oeysbrei

[Richard Buttrey]

See attached which is already filtered with 'Data-->Filter' then choose the criteria i.e. >1

[Richard Buttrey]

....
Also just realised that with some combinations the original sumproduct would produce the same total but with different column values
For example
2 4 2 1 1 1 1 1 1 1
produces the same total as
2 1 4 1 1 1 1 1 1 1

A better sumproduct formula in L2 would be

=SUMPRODUCT((A2:J2)*COLUMN(A2:J2)*SUM(A2:J2))

[Oeysbrei]

Thank you,

I would never figured it out if it wasent for you, but now it is solved.
I will proberbly need help someday soon, so i´m glad there are people like you on this forum

Best regards
Oeysbrei