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:

User ID	Job 1	Job 2	Job 3	Job 4	Output 1	Output 2
Jonh.Smith	X			X	Job 1	Job 4
Bob.Lang	X	X			Job 1	Job 2

<tbody>
</tbody>

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.

	Class 1	Class 2	Class 3	Class 4
Job 1		X		X
Job 2	X		X	X
Job 3		X	X
Job 4	X	X	X

<tbody>
</tbody>

<tbody></tbody>

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:

User ID	Job 1	Job 2	Job 3	Job 4	Output 1	Output 2	Output 3	Output 4	Output 5
Jonh.Smith	X			X	Job 1	Job 4	Class 2	Class 4	Class 3
Bob.Lang	X	X			Job 1	Job 2	Class 1	Class 2

<tbody>
</tbody>

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:

User ID					Output 1		Output 3	Output 4	Output 5
Jonh.Smith					Job 1 Job 4		Class 2 Class 1	Class 4 Class 2	Class 3
Bob.Lang					Job 1 Job 2		Class 2 Class 1	Class 4 Class 4

<tbody>
</tbody>

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.