function - MS Excel: List all cell values that meet a certain criteria -


i have few worksheets transaction numbers refer each other. need formula lists, each transaction, other transactions relate it. each transaction number has four-letter code (for instance expe expenses, trav travel, etc.) , three-digit number. transaction identifier, , each transaction has unique identifier, ex. expe-001, trav-010, etc. each transaction can connected other transactions. so, instance, if expense related travel, expe-001 connected trav-010.

my data set tables below. related transactions column list transactions specific transaction related to, , other transaction #s column excel enter other transactions specific 1 related. cells in other transaction #s column need excel autopopulate me.

in expenses worksheet,

trans. #   date         cost    related transactions   other transaction #s expe-001   2016-07-10   $1.12   trav-010               ____, ____, ____ expe-002   2016-07-10   $18.41  trav-010               ____, ____, ____ expe-003   2016-07-10   $7.80   trav-010               ____, ____, ____

in travel worksheet,

trans. #   date         cost    related transactions   other transaction #s trav-001   2016-07-10   $180.01 expe-001, expe-002     ____, ____, ____ trav-002   2016-07-10   $31.16  expe-002, expe-003     ____, ____, ____ trav-003   2016-07-10   $190.49 expe-003               ____, ____, ____

as can see, each transaction can referred in number of other transactions, , each transaction can refer number of other transactions. it's big web. makes more confusing data in trans. # stored number, ex. 1, 2, 3, , displayed code , appropriate number of leading 0s.

i know how take number, add transaction code, , pad zeroes. use text (for instance, "*expe-"&text(a12, "000"&"*" asterisk wildcards substring can appear anywhere in cell).

anyway, i've been playing match function grab row number of specific string in range of rows. , i've been playing index, if, small functions too. confusing, partly because think many functions involved, trying make array function (by hitting ctrl+shift+enter) excel lists across multiple rows in multiple ranges. far attempts have gotten me #ref!, #n/a, #value, or straight-out error message excel. table 6 transactions in it, i'd use row range of 3-8 (there 2 rows of headers). in various pieces, i've got following don't work or fraction of job:

=index(a3:a8, small(if((index(a3:a8, , $c$3, 1)="expe" & text($a$3, "000")), row(a3:a8), ""), rows(a19:$a$19)), , 1) =match(a8, a3:a8) =index(a3:p37, match(a8, a3:a37))

but i'm absolutely lost on this. seems can't step 1 work. haven't figured out how on 1 worksheet let alone multiple ones. have idea how solve insane formula?

you're saying trans. # column formatted display 1, 2, 3 trav-001, trav-002, trav-003 respectively? if so, match match row 1, 2, 3.

i either enter data trav-001...or add column, enter 1 , have column correctly format transaction number (=expe-"&text(a12, "000"&"*"). or, match 1, 2, 5, etc.


Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more