If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VLOOKUP 1 stop Answer: 3 steps: count dup's, compare dif's, merg
060212 VLOOKUP 1 stop Answer: 3 steps: count dup's, compare dif's, merge
figured out half, got half from posts, thanks.. working: (count duplicates, get "dif" from new to old sheet & merge data with VLOOKUP..) =IF(OR(V9={"",".",".sym."},COUNTIF($V$90:$V$3162,V 9)=1),"",COUNTIF($V$90:$V$3162,V9)) gets count same sheet =IF(OR(V244={"",".",".sym."}),"",IF(ISNA(VLOOKUP(V 1,[file.xls]sheet!$A$1:$A$3355,1,0)),"dif","")) gets dif from diff sheet (should sort whole sheet, to 1 continuous sort..) =VLOOKUP(V244,[file.xls]sheet!$A$1:$B$3355,2,FALSE) WORKS FINE, sort dif's out, else AGAIN: MUST SORT SHEET FOR GOOD RECORDS ONLY, WITH ABOVE top 2 EQ's alternate formulas =IF(ISNA(VLOOKUP(V244,[file.xls]sheet!$A$1:$B$3355,2,FALSE)),"",VLOOKUP(V244,[nasymbols.xls]a!$A$1:$B$3355,2,FALSE)) will get rid of n/a's, but do not want to copy blanks over other data anyways, etc. or: =IF(V124={"",".",".sym."},"",VLOOKUP(V124,[file.xls]sheet!$A$1:$B$3355,2,FALSE) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. |
#2
|
|||
|
|||
VLOOKUP 1 stop Answer: 3 steps: count dup's, compare dif's, merg
"nastech" wrote in message ... =IF(OR(V244={"",".",".sym."}),"",IF(ISNA(VLOOKUP(V 1,[file.xls]sheet!$A$1:$A$ 3355,1,0)),"dif","")) You don't need the OR, as there is only a single condition, even though it is testing two values. |
#3
|
|||
|
|||
VLOOKUP 1 stop Answer: 3 steps: count dup's, compare dif's,
oops, took out OR, should work, arrays.. added note to vlookup.. thanks
----------------- 060212 VLOOKUP 1 stop 3 steps: count dup's, compare dif's, merge figured out half, got half from posts, thanks.. working: (count duplicates, get "dif" from new to old sheet & merge data with VLOOKUP..) =IF(OR(V9={"",".",".sym."},COUNTIF($V$90:$V$3162,V 9)=1),"",COUNTIF($V$90:$V$3162,V9)) gets count same sheet =IF(V244={"",".",".sym."},"",IF(ISNA(VLOOKUP(V1,[file.xls]sheet!$A$1:$A$3355,1,0)),"dif","")) gets dif from diff sheet (should sort whole sheet, to 1 continuous sort..) =VLOOKUP(V244,[file.xls]sheet!$A$1:$B$3355,2,FALSE) WORKS FINE, sort dif's out, else AGAIN: MUST SORT SHEET FOR GOOD RECORDS ONLY, WITH ABOVE top 2 EQ's (use to bring in data from different sheet, copy-paste special: values of new info on top of old, by using a temporary work column) alternate formulas =IF(ISNA(VLOOKUP(V244,[file.xls]sheet!$A$1:$B$3355,2,FALSE)),"",VLOOKUP(V244,[file.xls]sheet!$A$1:$B$3355,2,FALSE)) will get rid of n/a's, but do not want to copy blanks over other data anyways, etc. or: =IF(V124={"",".",".sym."},"",VLOOKUP(V124,[file.xls]sheet!$A$1:$B$3355,2,FALSE) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pivot Table Access 2000? | Air-ron | General Discussion | 2 | October 29th, 2004 06:19 PM |