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
$1000>0)*($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$1000>0)*1,($I$2:$I$1000=I2)*1,($F$2:$F$1000=F2)*1)>1,"Duplicate","")

Regards,
Per

"Dylan" <(E-Mail Removed)> skrev i meddelelsen
news:66C49514-D11F-4B8A-ADF7-(E-Mail Removed)...
> 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
> $1000>0)*($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$1000>0)*1,($I$2:$I$1000=I2)*1,($F$2:$F$1000=F2)*1)>1,"Duplicate","")
>
> Regards,
> Per
>
> "Dylan" <(E-Mail Removed)> skrev i meddelelsen
> news:66C49514-D11F-4B8A-ADF7-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> skrev i meddelelsen
news4350561-836E-4A02-810E-(E-Mail Removed)...
> 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$1000>0)*1,($I$2:$I$1000=I2)*1,($F$2:$F$1000=F2)*1)>1,"Duplicate","")
>>
>> Regards,
>> Per
>>
>> "Dylan" <(E-Mail Removed)> skrev i meddelelsen
>> news:66C49514-D11F-4B8A-ADF7-(E-Mail Removed)...
>> > 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?
>> >>
>>
>>