How do I compare the contents of a series of columns in each row for exact
matches in other rows?

e.g. for row 2 compare the contents of columns B,C,D & F against all the
other rows' columns B,C,D & F and if an exact match is found highlight, or
spike, the contents. Then check row 3, row 4, etc.

I was thinking maybe to concatenate each of the row's columns into a string
and the do the same with the other rows to find a match.

Please help, I really need to do this exercise on a large database.

can you provide an example of what you want to count exactly?

Jarek, sure thing,

It's an invoicing database for time/expense. The columns are laid out as
listed below and the columns of the rows I want to check are B, F, G, and I
(B - Person, F - Job ID, G - Item and I - Week Commencing); I want find
duplicates of instances where a person (B) has spent time (G) on a project
(F) within a given week (I). Logically, each person should only have one
entry per project, per week.

Columns A-L
A Ref:
B Person
C Title
D Project Title
E Location Ref
F Job ID
G Item
H NEC3 Ref
I Week Commencing
J Quantity
K Rate
L Qty x Rate
"Jarek Kujawa" wrote:

can you provide an example of what you want to count exactly?

would:

=SUM(IF(($B$1:$B$1000="Bob Dylan")*($F$1:$F$1000="Job ID")*($G$1:$G
$10000)*($I$1:$I$1000="Week number"),1,0))

help?

copy down the formula then apply filter
otherwise send me a file

Hi

An other solution assuming headings in row 1. The formula goes in row 2 in
an unused column and to be copied down, then apply an autofilter:

=IF(SUMPRODUCT(($B$2:$B$1000=B2)*1,($G$2:$G$10000 )*1,($I$2:$I$1000=I2)*1,($F$2:$F$1000=F2)*1)1,"Du plicate","")

Regards,
Per

"Dylan" skrev i meddelelsen
...
Jarek, sure thing,

It's an invoicing database for time/expense. The columns are laid out as
listed below and the columns of the rows I want to check are B, F, G, and
I
(B - Person, F - Job ID, G - Item and I - Week Commencing); I want find
duplicates of instances where a person (B) has spent time (G) on a project
(F) within a given week (I). Logically, each person should only have one
entry per project, per week.

Columns A-L
A Ref:
B Person
C Title
D Project Title
E Location Ref
F Job ID
G Item
H NEC3 Ref
I Week Commencing
J Quantity
K Rate
L Qty x Rate
"Jarek Kujawa" wrote:

can you provide an example of what you want to count exactly?

Hi Jarek,

This doesn't solve it, I'm trying to check for duplicate rows checking
whether the contents of cells B, F, G & I match these same cells in other
rows, i.e. A row cannot be entered twice and the cells B, F, G & I contains
the data I want to test.

Please find enclosed copy of the sheet I'm trying to check, it may make it a
bit clearer.

Regards
Dylan


"Jarek Kujawa" wrote:

would:

=SUM(IF(($B$1:$B$1000="Bob Dylan")*($F$1:$F$1000="Job ID")*($G$1:$G
$10000)*($I$1:$I$1000="Week number"),1,0))

help?

copy down the formula then apply filter
otherwise send me a file

Thanks Per, this does what I want.

Thanks Jarek for your advice also.

Regards
Dylan

"Per Jessen" wrote:

Hi

An other solution assuming headings in row 1. The formula goes in row 2 in
an unused column and to be copied down, then apply an autofilter:

=IF(SUMPRODUCT(($B$2:$B$1000=B2)*1,($G$2:$G$10000 )*1,($I$2:$I$1000=I2)*1,($F$2:$F$1000=F2)*1)1,"Du plicate","")

Regards,
Per

"Dylan" skrev i meddelelsen
...
Jarek, sure thing,

It's an invoicing database for time/expense. The columns are laid out as
listed below and the columns of the rows I want to check are B, F, G, and
I
(B - Person, F - Job ID, G - Item and I - Week Commencing); I want find
duplicates of instances where a person (B) has spent time (G) on a project
(F) within a given week (I). Logically, each person should only have one
entry per project, per week.

Columns A-L
A Ref:
B Person
C Title
D Project Title
E Location Ref
F Job ID
G Item
H NEC3 Ref
I Week Commencing
J Quantity
K Rate
L Qty x Rate
"Jarek Kujawa" wrote:

can you provide an example of what you want to count exactly?

Hi Dylan

Thanks for your reply.

Glad I could help.

Regards,
Per

"Dylan" skrev i meddelelsen
...
Thanks Per, this does what I want.

Thanks Jarek for your advice also.

Regards
Dylan

"Per Jessen" wrote:

Hi

An other solution assuming headings in row 1. The formula goes in row 2
in
an unused column and to be copied down, then apply an autofilter:

=IF(SUMPRODUCT(($B$2:$B$1000=B2)*1,($G$2:$G$10000 )*1,($I$2:$I$1000=I2)*1,($F$2:$F$1000=F2)*1)1,"Du plicate","")

Regards,
Per

"Dylan" skrev i meddelelsen
...
Jarek, sure thing,

It's an invoicing database for time/expense. The columns are laid out
as
listed below and the columns of the rows I want to check are B, F, G,
and
I
(B - Person, F - Job ID, G - Item and I - Week Commencing); I want
find
duplicates of instances where a person (B) has spent time (G) on a
project
(F) within a given week (I). Logically, each person should only have
one
entry per project, per week.

Columns A-L
A Ref:
B Person
C Title
D Project Title
E Location Ref
F Job ID
G Item
H NEC3 Ref
I Week Commencing
J Quantity
K Rate
L Qty x Rate
"Jarek Kujawa" wrote:

can you provide an example of what you want to count exactly?