Hi everyone,
I have two tables ( 900+ rows , 6 columns) I want to compare these two
tables and if table A has a record with a value in one of its specific
fields(exp "C") that matches with a record filed in table B remove its
record or at least eleminate it in the final report.
table A
A B C
bob fg 3
john ac 2
kim lk 1

table b
D B C
bob fg 1
edward ad 1
john ac 2

second record in table 1 and third record in table b are the same so I want
to delete it from final report
final report
H I G
bob ab 1
edward ad 1


--
Best regards,
Edward

Some thoughts, illustrated in this sample:
http://www.freefilehosting.net/download/3bkc9
Eliminate dups.xls

Source tables A & B in cols A to C & in cols E to G,
data from row1 down

In I1: =A1&"#"&B1&"#"&C1
In J1: =E1&"#"&F1&"#"&G1
In K1: =IF(ISNUMBER(MATCH(I1,J:J,0)),"",ROW())
In L1: =IF(ISNUMBER(MATCH(J1,I:I,0)),"",ROW())

In N1, copied to P1:
=IF(ROW()COUNT($K:$K),"",INDEX(A:A,SMALL($K:$K,RO W())))

In R1, copied to T1:
=IF(ROW()COUNT($L:$L),"",INDEX(E:E,SMALL($L:$L,RO W())))
Select I1:T1, copy down to cover the max expected extent of source data.
Cols N to P returns Table A less the items found in Table B. Cols R to T
returns similarly for Table B.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Edward" wrote:
Hi everyone,
I have two tables ( 900+ rows , 6 columns) I want to compare these two
tables and if table A has a record with a value in one of its specific
fields(exp "C") that matches with a record filed in table B remove its
record or at least eleminate it in the final report.
table A
A B C
bob fg 3
john ac 2
kim lk 1

table b
D B C
bob fg 1
edward ad 1
john ac 2

second record in table 1 and third record in table b are the same so I want
to delete it from final report
final report
H I G
bob ab 1
edward ad 1


--
Best regards,
Edward