Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
nastech
 
Posts: n/a
Default 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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 10:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"