Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003: how to match records of 2 tables according to 2 columns?
I'd like to add 1 to column E in table 1 whenever the data in columns
B & C are identical to the data in columns A & E in table 2 Table 1: A B C D E F G 1 39174 200101 -173 30/06/07 1 31/03/09 510911019 2 39184 200101 -4231 30/06/07 1 31/03/09 510911019 3 39188 200101 -1343 30/06/07 1 31/03/09 510911019 4 39188 200101 -1733 30/06/07 1 31/03/09 510911019 5 39191 200101 -785 30/06/07 1 31/03/09 510911019 6 39191 200101 -17675 30/06/07 1 31/03/09 510911019 7 39142 200111 -394 30/06/07 1 31/03/09 510853468 8 39189 200111 -935 30/06/07 1 31/03/09 510853468 9 39203 200111 -328 30/08/07 1 31/03/09 510853468 10 39231 200111 -35 31/07/07 1 31/03/09 510853468 11 39149 200117 -53 30/06/07 1 31/03/09 511193898 12 39222 200117 -173 31/08/07 1 31/03/09 511193898 13 39238 200117 -162 30/09/07 1 31/03/09 511193898 14 39148 200129 -704.91 30/06/07 1 31/03/09 511687402 15 39141 200135 -25048 30/04/07 1 31/03/08 510917388 16 39145 200140 -1107 31/05/07 1 12/01/07 513751974 Table 2: A B C E G J 1 200101 39174 dfggf -173 39295 1 2 200101 39184 teet -4231 39295 1 3 200101 39188 v -1733 39295 1 4 200101 39191 nvn -785 39295 1 5 200101 39191 bcbd -17675 39295 1 6 200101 39188 g -1343 39295 1 7 200111 39142 dgg -394 39278 1 8 200111 39189 gdgd -935 39278 1 9 200111 39231 dgdgd -35 39278 1 10 200111 39203 -328 39278 1 11 200117 39149 gdgd -53 39278 1 12 200117 39222 jlj -173 39278 1 13 200117 39222 bnm -173 39278 1 14 200117 39238 byr -162 39278 1 15 200117 39238 y -162 39278 1 16 200129 39148 bfh -704.91 39278 1 17 200135 39141 wr -25048 39278 1 18 200140 39145 dgg -1107 39278 1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003: how to match records of 2 tables according to 2 column
Hi nk,
if identical, add 1? pls verify below On table 1 A B C D E F G 13 39238 200117 -162 30/09/07 1 31/03/09 511193898 then on table 2 A B C E G J 12 200117 39222 jlj -173 39278 1 13 200117 39222 bnm -173 39278 1 -------------- What shall be your correct result in the above sample...1+2=3 or 1+1=2 or only 1 Table 2 shows 2times of the identical criteria...by Column with group of rows regards , driller -- ***** birds of the same feather flock together.. "nk" wrote: I'd like to add 1 to column E in table 1 whenever the data in columns B & C are identical to the data in columns A & E in table 2 Table 1: A B C D E F G 1 39174 200101 -173 30/06/07 1 31/03/09 510911019 2 39184 200101 -4231 30/06/07 1 31/03/09 510911019 3 39188 200101 -1343 30/06/07 1 31/03/09 510911019 4 39188 200101 -1733 30/06/07 1 31/03/09 510911019 5 39191 200101 -785 30/06/07 1 31/03/09 510911019 6 39191 200101 -17675 30/06/07 1 31/03/09 510911019 7 39142 200111 -394 30/06/07 1 31/03/09 510853468 8 39189 200111 -935 30/06/07 1 31/03/09 510853468 9 39203 200111 -328 30/08/07 1 31/03/09 510853468 10 39231 200111 -35 31/07/07 1 31/03/09 510853468 11 39149 200117 -53 30/06/07 1 31/03/09 511193898 12 39222 200117 -173 31/08/07 1 31/03/09 511193898 13 39238 200117 -162 30/09/07 1 31/03/09 511193898 14 39148 200129 -704.91 30/06/07 1 31/03/09 511687402 15 39141 200135 -25048 30/04/07 1 31/03/08 510917388 16 39145 200140 -1107 31/05/07 1 12/01/07 513751974 Table 2: A B C E G J 1 200101 39174 dfggf -173 39295 1 2 200101 39184 teet -4231 39295 1 3 200101 39188 v -1733 39295 1 4 200101 39191 nvn -785 39295 1 5 200101 39191 bcbd -17675 39295 1 6 200101 39188 g -1343 39295 1 7 200111 39142 dgg -394 39278 1 8 200111 39189 gdgd -935 39278 1 9 200111 39231 dgdgd -35 39278 1 10 200111 39203 -328 39278 1 11 200117 39149 gdgd -53 39278 1 12 200117 39222 jlj -173 39278 1 13 200117 39222 bnm -173 39278 1 14 200117 39238 byr -162 39278 1 15 200117 39238 y -162 39278 1 16 200129 39148 bfh -704.91 39278 1 17 200135 39141 wr -25048 39278 1 18 200140 39145 dgg -1107 39278 1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003: how to match records of 2 tables according to 2 column
nk,
not quite sure, maybe not this trial formula, but just for a re-starter... I'd like to add 1 to column E in table 1 whenever the data in columns B & C are identical to the data in columns A & E in table 2 *whenever* =SUMPRODUCT((B1=Sheet2!$A$1:$A$18)*(C1=Sheet2!$E$1 :$E$18)) Table 1 on e.g. current sheet Table 2 on e.g. Sheet2 -- ***** birds of the same feather flock together.. "nk" wrote: I'd like to add 1 to column E in table 1 whenever the data in columns B & C are identical to the data in columns A & E in table 2 Table 1: A B C D E F G 1 39174 200101 -173 30/06/07 1 31/03/09 510911019 2 39184 200101 -4231 30/06/07 1 31/03/09 510911019 3 39188 200101 -1343 30/06/07 1 31/03/09 510911019 4 39188 200101 -1733 30/06/07 1 31/03/09 510911019 5 39191 200101 -785 30/06/07 1 31/03/09 510911019 6 39191 200101 -17675 30/06/07 1 31/03/09 510911019 7 39142 200111 -394 30/06/07 1 31/03/09 510853468 8 39189 200111 -935 30/06/07 1 31/03/09 510853468 9 39203 200111 -328 30/08/07 1 31/03/09 510853468 10 39231 200111 -35 31/07/07 1 31/03/09 510853468 11 39149 200117 -53 30/06/07 1 31/03/09 511193898 12 39222 200117 -173 31/08/07 1 31/03/09 511193898 13 39238 200117 -162 30/09/07 1 31/03/09 511193898 14 39148 200129 -704.91 30/06/07 1 31/03/09 511687402 15 39141 200135 -25048 30/04/07 1 31/03/08 510917388 16 39145 200140 -1107 31/05/07 1 12/01/07 513751974 Table 2: A B C E G J 1 200101 39174 dfggf -173 39295 1 2 200101 39184 teet -4231 39295 1 3 200101 39188 v -1733 39295 1 4 200101 39191 nvn -785 39295 1 5 200101 39191 bcbd -17675 39295 1 6 200101 39188 g -1343 39295 1 7 200111 39142 dgg -394 39278 1 8 200111 39189 gdgd -935 39278 1 9 200111 39231 dgdgd -35 39278 1 10 200111 39203 -328 39278 1 11 200117 39149 gdgd -53 39278 1 12 200117 39222 jlj -173 39278 1 13 200117 39222 bnm -173 39278 1 14 200117 39238 byr -162 39278 1 15 200117 39238 y -162 39278 1 16 200129 39148 bfh -704.91 39278 1 17 200135 39141 wr -25048 39278 1 18 200140 39145 dgg -1107 39278 1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Does Excel 2000/2003 have a max number of records like '98 has? | Excel Discussion (Misc queries) | |||
Import Text file into Excel and match up records | Excel Discussion (Misc queries) | |||
Find duplicate records in Excel 2003 | Excel Discussion (Misc queries) | |||
Can you match records from two different worksheets | Excel Worksheet Functions | |||
Excel 2003 - Find records | Excel Worksheet Functions |