excel - vba: Identify row using 2 criteria and return other data from row -

-

i trying find code looks @ 2 criteria in spreadsheet1 , finds row corresponds in spreadsheet2 , returns third piece of data in spreadsheet2 spreadsheet1. need in vba because loops, because done again , again, , because data spreadsheet2 imports database , change on time. if possible nice if code allowed identifying 3rd criteria on spreadsheet2.

example is: spreadsheeet 1

 product id  actcode: a0003  11111  12345  22222  ...

spreadheet 2

productid  activitycode   datedue  11111     a0001          7/15/15  11111     p7530          7/30/15  11111     a0003          8/1/15  12345     a0003          12/15/15  12345     a0007          1/1/15  22222     a0001          2/1/15   ...

where want spreadsheet1 end up:

spreadsheeet 1

 product id  actcode: a0003  11111       8/1/15  12345       12/15/15  22222         -   ...

i have tried ton of things on past few days. 1) vlookup/index/match combos have never worked, 2) filtering spreadsheet2 productid , activitycode , copying spreadsheet1 visible cells - works slow. doing many activity codes, need faster (i can post code if want see it). trying loop within loop. not sure if best way here code have far. copy dates over, not right ones - bit slow.

sub test()  application.screenupdating = false   sheets("spreadsheet1").select    range("a2").select ' select = column product id in   ' set loop stop when empty cell reached.   until isempty(activecell)      dim conceptact  string     conceptact = "a0003"      dim productid     productid = activecell.value     dim concdue          sheets("spreadsheet2").select         range("a2").select 'the column productid in          until isempty(activecell)          if activecell.value = productid , activecell.offset(0, 1).value = conceptact              concdue = activecell.offset(0, 2).value              exit         end if            activecell.offset(1, 0).select         loop       sheets("spreadsheet1").select       activecell.offset(0, 1) = concdue       ' step down 1 row present location.      activecell.offset(1, 0).select    loop  application.screenupdating = true  end sub

why won't index/match work? able get, think, solution index/match formula entered array. here's screenshot of everything:

enter image description here

index/match can use multiple criteria looking things up, connect these &, both in first , second parts of match(), , hit ctrl+shift+enter enter array. formua @ product id, actcode, return date.

is looking for?


Comments

Post a Comment

Popular posts from this blog

python - pip install -U PySide error -

-
does know how avoid following error when running pip install -u pyside per official website instructions here: https://pypi.python.org/pypi/pyside/#installing-pyside-on-a-mac-os-x-system note have done brew install qt successfully. you using pip version 7.0.3, version 7.1.2 available. should consider upgrading via 'pip install --upgrade pip' command. collecting pyside using cached pyside-1.2.4.tar.gz installing collected packages: pyside running setup.py install pyside complete output command /applications/anaconda/bin/python -c "import setuptools, tokenize;__file__='/private/var/folders/dy/ttgqhqqx3g9bsbsnqnxsk2z80000gq/t/pip-build-cpxemt/pyside/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /var/folders/dy/ttgqhqqx3g9bsbsnqnxsk2z80000gq/t/pip-0r7hri-record/install-record.txt --single-version-externally-managed --compile: ...
Read more

arrays - C++ error: a brace-enclosed initializer is not allowed here before ‘{’ token -

-
simple question: i'm trying initialize array within c++ class declaration: using namespace std; #include <string> class myclass{ public: string myarray[] = {"a","b","c"}; }; and i'm getting error: error: brace-enclosed initializer not allowed here before ‘{’ token no, without complied c++11 compiler, cannot initialize member array in declaration. have initialize array member in constructor. , don't use open array if know number of elements initialize array.
Read more

apache - setting document root in antoher partition on ubuntu -

-
i place apache document root inside partition of ubuntu hard drive, keep getting forbidden message, when place home directory woking find, how be? group or owner affected ? here mysite.conf , apache2.conf when place document root in home folder (working) #site-available/mysite.conf documentroot /home/jono/www #/etc/apache2/apache2.conf <directory /home/jono/www/> options indexes followsymlinks allowoverride none require granted </directory> bu when change document root partition keep getting forbidden messasge #site-available/mysite.conf documentroot /media/jono/website_data/www #/etc/apache2/apache2.conf <directory /media/jono/website_data/www/> options indexes followsymlinks allowoverride none require granted </directory> is owner/group access affected ? or there problem ? at last it's working, have grant access www:data accesing whole directory @mark-b, using chown -r www-data:www-data whole directory is...
Read more