Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare row contents
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare row contents
can you provide an example of what you want to count exactly?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare row contents
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare row contents
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare row contents
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare row contents
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare row contents
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare row contents
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare the contents of one range of cells with the contents of a. | Excel Discussion (Misc queries) | |||
Compare Cells and disply contents | Excel Discussion (Misc queries) | |||
COMPARE CELL CONTENTS | Excel Worksheet Functions | |||
compare cell contents | Excel Worksheet Functions | |||
How to compare the contents of two rows is the same? | Excel Programming |