vba - Excel macro to transpose data from one sheet to another based column name and cell content -



vba - Excel macro to transpose data from one sheet to another based column name and cell content -

i have worksheet1 column labeled participant , columns labeled 1-20 (experiment id numbers).

i have worksheet2 info of participants (column) , experiments (column) participated in. in cells under experiment column, there multiple experiment id numbers participants have participated in multiple experiments. illustration cell read, "1,4,5".

what need place "x", on worksheet1, in corresponding cells worksheet2. example, cell reading "1,4,5" participant "003" worksheet2 place "x" in columns labelled 1, 4, 5 in worksheet1.

i hope clear. new macros need help possible.

this lucky day. i'm going walk through how this, code. have understand concepts in order benefit it. if do, can apply these ideas many projects.

first, need break downwards tasks manageable easy steps. macros aren't going help because of different scenarios. let's break downwards steps, address how accomplish each.

before started, consider sheets("worksheet1").cells(row#, col#) format. allows move through cells on sheet incrementing row number , column number using variables. in case, orow (original row), nrow (new row), etc.

look @ participant row on worksheet2. : participant = sheets("worksheet2").cells(orow, 1) copy participant worksheet1 column 1. : sheets("worksheet1").cells(nrow, 1) = participant split experiments cell list: split(sheets("worksheet2").cells(orow, 2), ",") loop through list items , each one, set "x" under column of same name. repeat steps 1-4 each row (participant entry) on worksheet2.

declare variables. in case, i'm declaring them outside of method because using them in 2 methods.

dim lastrow1 integer dim lastrow2 integer dim lastcol1 integer

now method.

sub experimentmover() dim explist() string dim nrow integer dim orow integer dim participant string dim icol integer phone call getlastrows nrow = lastrow1 + 1 'loop through contents on worksheet2 1 row @ time orow = 2 lastrow2 'get participant , store variable. participant = sheets("worksheet2").cells(orow, 1) 'create list of items in experiments cell, splitting using "," explist = split(sheets("worksheet2").cells(orow, 2), ",") 'set participant in worksheet1 sheets("worksheet1").cells(nrow, 1) = participant each experiment in explist 'loop through each column on worksheet1 icol = 2 lastcol1 'set column name , check see if matches experiment colname = sheets("worksheet1").cells(1, icol).text if colname = experiment sheets("worksheet1").cells(nrow, icol) = "x" exit end if next icol next experiment 'increment row number on worksheet1. should matching row on worksheet2 nrow = nrow + 1 next orow end sub

here getlastrows subroutine. separate because there many times need phone call in project. it's improve have method can test , verify works, if have alter something, alter 1 thing. instead of 15 calls , maybe miss one.

private sub getlastrows() lastrow1 = sheets("worksheet1").range("a65536").end(xlup).row lastrow2 = sheets("worksheet2").range("a65536").end(xlup).row lastcol1 = sheets("worksheet1").cells(1, columns.count).end(xltoleft).column end sub

edit: formatting prepare code indentation

excel vba excel-vba

Comments

Post a Comment

Popular posts from this blog

xslt - DocBook 5 to PDF transform failing with error: "fo:flow" is missing child elements. Required content model: marker* -

xslt - DocBook 5 to PDF transform failing with error: "fo:flow" is missing child elements. Required content model: marker* - i've inherited doc publishing process takes docbook , builds html , pdf output, using apache fop both. project had been started, never completed. lots of tweaking, i've able of doc sets build (20+), except handful, , fail severe code 1 of these 2 conditions: "fo:flow" missing kid elements. required content model: marker* "fo:block" not valid kid of "fo:root" the xml output comes out of docbook , docbook-fop checks out well-formed. the fop error in 1 book, "endecagloss", example, points end of string: <fo:flow flow-name="xsl-region-body" start-indent="4pc" end-indent="0pt"/> here’s log output in case: [java] severe: javax.xml.transform.transformerexception: file:/scratch/publishing/hudson/jobs/build-main-endeca-documentation/workspace/serverdoc/w...
Read more

mediawiki - How do I insert tables inside infoboxes on Wikia pages? -

mediawiki - How do I insert tables inside infoboxes on Wikia pages? - i want insert tables in infoboxes. example, have proteinbox i've developed using infobox template (http://health-and-medicine.wikia.com/wiki/template:proteinbox), , fasta field big fit comfortably in proteinbox. hence collapsible table within box. this tried: {{infobox | row 1 = {| class = "mw-collapsible mw-collapsed wikitable" |- | <!-- table content --> |} }} your problem is, putting nested table within template. pipe characters ( | ) in table syntax collides pipe usage in templates. the commonly used hack around create template called template:! , containing pipe character, , utilize when need set tables, parser functions or other stuff using pipe characters, within templates. table (with every | replaced {{!}} ): {{{!}} {{!}}- {{!}} a1 {{!}} b1 {{!}}- {{!}} a2 {{!}} b2 {{!}}} ...the equivalent of {| |- | a1 | b1 |- | a2 | b2 |} furthermore, hav...
Read more

Local Service User Logged into Windows -

Local Service User Logged into Windows - i created windows service tracks app server user connected through load balancer. it retrieves user logged windows on machine, cross references active directory (ad) business relationship homecoming first name, lastly name , e-mail (for notification through website created project) there 1 scheme on network users remote system, vista system, reason returning values "local service" logged machine, it's weird , security guys impossible "local service" business relationship logged windows. here how user logged windows managementscope ms = new managementscope("\\\\.\\root\\cimv2"); objectquery query = new objectquery("select * win32_computersystem"); managementobjectsearcher searcher = new managementobjectsearcher(ms, query); foreach (managementobject mo in searcher.get()) { _username = mo["username"].tostring(); } // remove domain part username string[] usernameparts = _...
Read more