excel - Vlookup to search non-unique values -



excel - Vlookup to search non-unique values -

i have 2 excel sheets below values

sheet1

empid appname appid [only appid unique]

sheet2

empid appname requestnum [only requestnum unique]

empid repeated since there multiple apps associated 1 users appname repeated since there multiple instances of single app associated 1 users

now, want requestnum sheet2 sheet1, returns me 1st value in case of duplicates. concatnating empid & appname not work since not create unique combination. how accomplish that? possible combination of formulae or need macro?

any help much appreciated :)

pasting sample sheets below, thx quick response:

sheet1:

empid appname appid a123 app1 uniqueid001 a123 app2 uniqueid002 b444 app66 uniqueid003 b898 app1 uniqueid004 h123 app33 uniqueid005 a123 app1 uniqueid006 b444 app33 uniqueid007 l001 app2 uniqueid008 h123 app1 uniqueid009

sheet2:

empid appname requestnum a123 app1 uniquereq001 a123 app2 uniquereq002 b444 app66 uniquereq003 b898 app1 uniquereq004 h123 app33 uniquereq005 a123 app1 uniquereq006 b444 app33 uniquereq007 l001 app2 uniquereq008 h123 app1 uniquereq009 a123 app1 uniquereq010 a123 app2 uniquereq011 b444 app66 uniquereq012 b898 app1 uniquereq013 h123 app33 uniquereq014 a123 app1 uniquereq015 b444 app33 uniquereq016 l001 app2 uniquereq017 h123 app1 uniquereq018

if sheet1's appid unique , sheet2's requestnum unique i'm not sure how determine empid & appname send sheet2's requestnum here 1 possible solution.

       

the formula in d2 =iferror(index($h$2:$h$9,small(index(row($1:$8)+(($f$2:$f$9<>$a2)+($g$2:$g$9<>$b2))*1e+99,,),countifs($a$2:$a2,$a2,$b$2:$b2,$b2))),""). fill downwards necessary.

excel excel-vba excel-formula

Comments

Post a Comment

Popular posts from this blog

Delphi change the assembly code of a running process -

Delphi change the assembly code of a running process - i'm trying alter address 00741fa5 has push test.009e721c . alter push test.009e71c8 . procedure callback; asm force $9e71c8 end; procedure tform2.btn1click(sender: tobject); var ppid :dword; pprocess : cardinal; begin getwindowthreadprocessid(findwindow(nil,pchar('test')), @ppid); pprocess := openprocess(process_all_access, false, ppid); injectasm(pprocess, $741fa5, 10, integer(@callback), 0); end; here injectasm function (with error checking) function injectasm(process: longword; injectaddress, injectsize, codeaddress, codesize : integer): pointer; var csize, rsize : integer; replaced : array of byte; jmp : array [0..4] of byte; jmpaddress : integer; nopv : byte; : integer; nbr: ulong_ptr; begin //injectsize must equal or greater 5, because need space our //(far) jmp wwxxyyzz instruction //if there's no space, inject in place of few instructions if injectsize ...
Read more

json - Hibernate and Jackson (java.lang.IllegalStateException: Cannot call sendError() after the response has been committed) -

json - Hibernate and Jackson (java.lang.IllegalStateException: Cannot call sendError() after the response has been committed) - hello have problem hibernate , jackson. have 2 pojos @entity @table(name = "users") public class user implements serializable { static final long serialversionuid = 4235637833262722256l; @id @generatedvalue private int id; @column(name = "creation_date") private date creationdate; @column(name = "first_name") private string firstname; @column(name = "last_name") private string lastname; @version @column(name = "version") private int version; @column(name = "e_mail") private string email; @column(name = "enabled") private boolean enabled; @column(name = "login") private string login; @column(name = "password") private string password; @jsonmanagedreference("role") @onetomany(mappedby = "user", cascade = cascadetype.all,fetch = fetchtyp...
Read more

C++ 11 "class" keyword -

C++ 11 "class" keyword - i've started using c++ 11 little bit more , have few questions on special uses of class keyword. know it's used declare class, there's 2 instances have seen don't understand: method<class t>(); and class class_name *var; why precede typename keyword class in first example, , keyword pointer in sec example? that known elaborated type specifier , necessary when class name "shadowed" or "hidden" , need explicit. class t { }; // love of god don't t t; t t2; if compiler smart, give these warnings: main.cpp:15:5: error: must utilize 'class' tag refer type 't' in scope t t2; ^ class main.cpp:14:7: note: class 't' hidden non-type declaration of 't' here t t; ^ if class not defined, deed forwards declaration. for example: template <typename t> void method() { using type = typename t::value_type; } method...
Read more