Compare row contents

[Dylan]

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.

 

[Jarek Kujawa]

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

 

[Dylan]

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]

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

 

[Per Jessen]

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]

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

 

[Dylan]

Thanks Per, this does what I want.

Thanks Jarek for your advice also.

Regards
Dylan

 

[Per Jessen]

Hi Dylan

Thanks for your reply.

Glad I could help.

Regards,
Per