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

I have a worksheet 1 in which there are columns with labeled labels and columns containing 1-20 labels (experiment ID number).

There is another workbook 2 with the data of my participant (column) and those experiments (columns). In cells under the experiment column, there are several experiment ID numbers because participants have participated in many experiments. For example, a cell can read, "1,4,5"

What do I need to do? In the cells related to worksheet 2 on worksheet 2, for example "X" for example, read a cell for "003" participant from worksheet 2 "1,4,5" worksheet 1 In the column called 1, 4, 5, there will be an "X".

I hope this is clear. I'm completely new to macros, but need help with this as soon as possible.

This is your lucky day. I'm going to walk you with some code, how to do this. But you have to understand the concepts to take advantage of it. If you do this, you can apply these ideas in many projects.

First of all, you have to break the task in easy easy steps due to all the different scenarios, macros are not helping you, let's break it down in the steps, then find out how to complete each one.

Before starting, consider the sheet ("worksheet 1"). Room (line #, call #) format. This allows you to move the row number and column number on the keys through a variable to a sheet. In this case, ORO (original line), NRO (new line) etc.

1. Look at the participant row on Worksheet 2: Participant = Sheet ("Worksheet 2"). Room (Oro, 1)
2. Copy the Worksheet 1 column to the participant. Column 1: Sheets ("Worksheet 1"). Room (NRO, 1) = Participant
3. Split the experiment cell into a list: Split (Sheets ("Worksheet 2"). Room (ORO, 2), ",")
< Li> Repeat steps 1-4 for each row (partner entry) on Worksheet 2 with a list item and loop one "x"
4. below the column of the same name for each.

Announce your variable In this case, I am declaring them out of the law because I am using them in two methods.

  Mute ending like 1 integer dim as last line 2 like integer last last call as 1 integer  

now the method of use.

  Use sub (dim) as dim dimension () as the string dim narov integer dim ORU integer as a random participant string as the integer call as ICL call getLastRows nRow = lastRow1 + 1 'loop on the worksheet via the content once for each row, get the previous row' 2 'for the ORO = 2 and store it as a variable participants = sheets ("worksheet 2"). Cell (ORO, 1) "Create a list of items in the cell of experiments," using the partition, "Excl. = Split (Sheets (" Worksheet 2 "). Room (ORO, 2)", ") 'Worksheet 1 Sheets Setup the participant in ("worksheet 1"). Room (NRO, 1) = Participant for each experiment, set column name for 'IOCL = 2 to last call 1' in the 'loop' And then check to see if The experiment matches colName = sheets ("worksheet 1"). Cell (1, iCol) .team if colName = use sheet ("worksheet 1"). Room (nRow, iCol) = "X" for end Exit if the next Icicle is to increase the line number on the next experiment 'Worksheet 1. It should match the row on worksheet 2 nrow = nRow + 1 next ORO and sub  

Here is the GetLastRows subroutine I like to be separated because sometimes I have to call in a project. This is just one way Is that you can work and verify, if you change something, you can change one thing instead of 15 calls and maybe a miss.

  Private sub GetLastRows () lastRow1 = Sheets ("Worksheet 1"). Range ("A65536") End (XLEP). Array = 2 = Sheets "Worksheet 2"). Range ("A65536") End (XLEEP). Last Last Call 1 = Sheets ("Worksheet 1"). Cell (1, column.account). End (XLTOLET) Column end sub  

Editing: Formatting Fixes with Code Indentant