How to Return Column Data Based on All Populated Cells in Row Range and Match Data to Other Worksheet Based on Output

[antman2988]

Hi Everyone,

I need help with an issue I can't seem to find an exact answer for. Basically, I need to return the column data for all populated cells with the letter "X" in the row. Then return the column header to different cells in the row.

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]User ID[/TD]
[TD]Job 1 [/TD]
[TD]Job 2[/TD]
[TD]Job 3[/TD]
[TD]Job 4[/TD]
[TD]Output 1[/TD]
[TD]Output 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jonh.Smith[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]Job 1[/TD]
[TD]Job 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob.Lang[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]Job 1[/TD]
[TD]Job 2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula will be applied to about 500 rows of data with about 30 columns. I would like all headers to be returned if it has an x in the column for each row. I would also like all the data to be next to each other in regards to the columns (e.g. no blank cells between output data). I assume this can be taken care of with one or two formulas that can be copied to the other cells.

In addition, I have another issue. After I get this data, I want to do something a bit more complex. There's another sheet in which I have all of the classes that each user should be taking based on what their job is. Each user will be taking multiple classes. The next thing I need help with is matching the user's job to the classes they should be taking. The above data will be match to the below data.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Class 1[/TD]
[TD]Class 2[/TD]
[TD]Class 3[/TD]
[TD]Class 4[/TD]
[/TR]
[TR]
[TD]Job 1[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Job 2[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Job 3[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 4[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody></tbody>[/TABLE]

The other sheet contains data that shows which class a user should be taking denoted by the letter "X" in each row.

The final output should be something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]User ID[/TD]
[TD]Job 1 [/TD]
[TD]Job 2[/TD]
[TD]Job 3[/TD]
[TD]Job 4[/TD]
[TD]Output 1[/TD]
[TD]Output 2[/TD]
[TD]Output 3[/TD]
[TD]Output 4[/TD]
[TD]Output 5[/TD]
[/TR]
[TR]
[TD]Jonh.Smith[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]Job 1[/TD]
[TD]Job 4[/TD]
[TD]Class 2[/TD]
[TD]Class 4[/TD]
[TD]Class 3[/TD]
[/TR]
[TR]
[TD]Bob.Lang[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]Job 1[/TD]
[TD]Job 2[/TD]
[TD]Class 1[/TD]
[TD]Class 2[/TD]
[/TR]
</tbody>[/TABLE]
Class 4

I am not an advanced user of Excel so any help is greatly appreciated.

 

[antman2988]

Another output that would be useful is this:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]User ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Output 1[/TD]
[TD][/TD]
[TD]Output 3[/TD]
[TD]Output 4[/TD]
[TD]Output 5[/TD]
[/TR]
[TR]
[TD]Jonh.Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Job 1
Job 4[/TD]
[TD][/TD]
[TD]Class 2
Class 1[/TD]
[TD]Class 4
Class 2[/TD]
[TD]
Class 3[/TD]
[/TR]
[TR]
[TD]Bob.Lang[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Job 1
Job 2[/TD]
[TD][/TD]
[TD]Class 2
Class 1

[/TD]
[TD]Class 4
Class 4[/TD]
[/TR]
</tbody>[/TABLE]

In this case, duplicates are fine. If you can give me answers to both output formats, that would be amazing.

 

[antman2988]

Note: The classes and jobs should be in separate cells.