vba - Convert multiple rows to columns in Excel -


i have sheet containing clients , dependents. listed

ssn                   last name      first name      relationship
000-00-0000      smith              john                client
000-00-0000      smith              freddy            son
000-00-0000      smith              beth                daughter
111-11-1111       johnson          alan                client
111-11-1111       johnson          carla              spouse

i need listed clients row, , dependents listed beside (next columns), this:

ssn                   last name      first name      dep1 fn     dep1 rel     dep2 fn     dep2 rel
000-00-0000      smith              john                freddy         son              beth            daughter
111-11-1111       johnson          alan                 carla           spouse

ssn determining factor on this, since last names different. have seen vba questions have had similar answers, can't seem find modify specifically.

edit:

the code have tried is:

sub tgr()  dim wsinput worksheet dim wsoutput worksheet dim acell range dim arrresults() variant dim resultindex long dim scurrent string dim sline string  set wsinput = activeworkbook.sheets("input") set wsoutput = activeworkbook.sheets("output")  wsinput.range("a1").currentregion     .sort .resize(, 1), xlascending, .offset(, 1).resize(, 1), , xlascending, header:=xlguess     redim arrresults(1 .cells.count, 1 1)     each acell in .resize(, 1).cells         if acell.value & "|" & acell.offset(, 1).value <> scurrent             scurrent = acell.value & "|" & acell.offset(, 1).value             resultindex = resultindex + 1             arrresults(resultindex, 1) = scurrent         end if         arrresults(resultindex, 1) = arrresults(resultindex, 1) & "|" & acell.offset(, 2).value     next acell end  wsoutput.range("a1").resize(resultindex)     .parent.usedrange.clear     .value = arrresults     .texttocolumns .cells, xldelimited, other:=true, otherchar:="|" end end sub

i found on different question, listing out things how need them, except last names of dependents beside client. no relationship or first name or anything. know nothing vba, i'm not sure parts of code change.

any appreciated!

edit 2:

report comes our database:

enter image description here

how need report look:

enter image description here


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