How to Merge Rows Based on Cell Value While Keeping Unique Values and Keeping One of Each Repeated Value

[antman2988]

Hello,

I have a workbook that contains classes a person must take based on their roles in a system (job duties). Because each roles requires certain classes and all of the roles have repeated classes, I would like to combine the rows based on the user while keeping all unique values and keeping just one of the repeated values.

Example:
Smith, Bob AP Approver Intro Intro2 Voucher Processing
Smith, Bob AP Processor Intro Intro2 Suppliers
Smith, Bob AM Processor Intro Intro2 Asset Mgmt. Module

Result:
Smith, Bob Intro Intro2 Voucher Processing Suppliers Asset Mgmt. Module

Actual Data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]Araiza, Jennifer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]AM Processor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]FS101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FS102[/TD]
[TD]AM101[/TD]
[TD]AM112[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]Araiza, Jennifer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]AM Manager[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]FS101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FS102[/TD]
[TD]AM101[/TD]
[TD]AM112[/TD]
[TD]AM122[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]Araiza, Jennifer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]AM Approver[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]FS101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FS102[/TD]
[TD]AM101[/TD]
[TD]AM122[/TD]
[TD]AM132[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]Araiza, Jennifer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]AM Maintainer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]FS101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FS102[/TD]
[TD]AM101[/TD]
[TD]AM112[/TD]
[TD]AM142[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]Araiza, Jennifer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]AM Confidential User[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FS101[/TD]
[TD]FS102[/TD]
[TD]AM101[/TD]
[TD]AM112[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RESULT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]Araiza, Jennifer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FS101[/TD]
[TD]FS102[/TD]
[TD]AM101[/TD]
[TD]AM112[/TD]
[TD]AM122[/TD]
[TD]AM132[/TD]
[TD]AM142[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Note: Their roles do not need to be maintained. I just care about the classes. Also, the information is on the first three sheets. I was trying to figure out how to do it myself but couldn't.


Any assistance is appreciated!

Thanks,
Anthony Smith

 

[antman2988]

I figured out a way to do it using a few different formulas.