excel - Search for value in column that matches another column AND a date? -

-

i have data stored in 3 columns of excel

column a: serial number column b: date column c: value (e.g. cost)

i need value (column c) associated particular serial number (column a) , date (column b).

so example, in screenshot below, if want value associated serial number (t455) , date (dec 13, 2010), value should 8.

enter image description here

the method can come computationally inefficient, because go through cells each time value.

is there method, example, limit search area given serial number?

for example, if looking value serial number t455, how can limit code search date in rows (6-13) , find corresponding value in column c, rather searching whole table?

sub findvalue()  dim s string dim d date dim v integer  s = t455 d = dec 13, 2010  = 1 range("a1").end(xldown).row   if range("a" & i) = s , range("b" & i) < date - 7 , range("b" & i) < date + 7   ' way search date range rather specific date  v = range("c" & i).value  end if  end sub

i thought of while loops, or lookup functions, reached dead end.

non-vba solution may lot easier , less of headache.

column consists of formula, a1 = "=b1&c1"

cell g1 formula can seen in formula bar.

enter image description here

update here vba solution work faster, there notes based on wrote unsure of. also, see comments code work more want to.

sub findvalue()  dim s string, d date, v integer, rngfound range, cel range  s = "t455" 'needs quotes around string d = "dec 13, 2010" 'needs quotes around date  dim wks worksheet set wks = activesheet  wks       'always better autofilter loop when can!     .usedrange.autofilter 1, s     .usedrange.autofilter 2, ">" & d - 7, xland, "<" & d + 7      set rngfound = intersect(.usedrange, .columns(3)).specialcells(xlcelltypevisible)      'the thing here if have date range _         'you may return more 1 result _         'in case, don't know want v's      if not rngfound nothing         each cel in rngfound             v = cel.value         next     end if      .autofiltermode = false  end  end sub

Comments

Post a Comment

Popular posts from this blog

c# - SVN Error : "svnadmin: E205000: Too many arguments" -

-
i trying reposotries using c# code process svncommand = null; var psi = new processstartinfo("svnadmin"); psi.redirectstandardoutput = true; psi.redirectstandarderror = true; psi.useshellexecute = false; psi.arguments= @"dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump"; using (svncommand = process.start(psi)) { var myoutput = svncommand.standardoutput; var myerror = svncommand.standarderror; debug.write("output :" + environment.newline + environment.newline + myoutput.readtoend() + environment.newline + "error :" + environment.newline + environment.newline + myerror.readtoend()); svncommand.close(); } when use dump command commandline svnadmin dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump it works fine when try ...
Read more

c# - Copy ObservableCollection to another ObservableCollection -

-
how copy observablecollection item observablecollection without reference of first collection? here observablecollection item value changes affecting both collections. code private observablecollection<ratemodel> _allmetalrate = new observablecollection<ratemodel>(); private observablecollection<ratemodel> _metalrateondate = new observablecollection<ratemodel>(); public observablecollection<ratemodel> allmetalrate { { return this._allmetalrate; } set { this._allmetalrate = value; notifypropertychanged("metalrate"); } } public observablecollection<ratemodel> metalrateondate { { return this._metalrateondate; } set { this._metalrateondate = value; notifypropertychanged("metalrateondate"); } } foreach (var item in metalrateondate) allmetalrate.add(item); what causing , how can solve it? you need clone object referenced item before it's a...
Read more

All overlapping substrings matching a java regex -

-
is there api method returns (possibly overlapping) substrings match regular expression? for example, have text string: string t = 04/31 412-555-1235; , , have pattern: pattern p = new pattern("\\d\\d+"); matches strings of 2 or more characters. the matches are: 04, 31, 412, 555, 1235. how overlapping matches? i want code return: 04, 31, 41, 412, 12, 55, 555, 55, 12, 123, 1235, 23, 235, 35. theoretically should possible -- there obvious o(n^2) algorithm enumerates , checks substrings against pattern. edit rather enumerating substrings, safer use region(int start, int end) method in matcher . checking pattern against separate, extracted substring might change result of match (e.g. if there non-capturing group or word boundary check @ start/end of pattern). edit 2 actually, it's unclear whether region() expect zero-width matches. specification vague, , experiments yield disappointing results. for example: string line = "xx90xx"; strin...
Read more