Need to find unique name across multiple columns and then list the column header.

---------A----------B ----------C--------D
1-------R1---------TS---------RA-------DB
2-------7c*--------1-------------------455-5p
3-------7e*--------1207-3p-----------183
4-------100--------122----------------4446-3p
5-------100*-------1253---------------------
6-------101*------------------1254-------
7-------103-2*----------------1256-------
8-------103-as----------------1257-------

So my spreadsheet looks like this. And there are many multiples across the columns.
What i want is;

A: A formula that will list the unique values in a vertical column next to the data.
EDIT: Managed this on my own using
=INDIRECT(TEXT(MIN(IF(($B$14:$G$500<>"")*(COUNTIF($I$13:I499,$B$14:$G$500)=0),ROW($14:$500)*100+COLUMN($B:$G),7^8)),"R0C00"),)&""
I know none of my columns go over 500, nor are there more than 500 unique values, but if there is a cleaner way to do this please let me know.


B: A formula that will then vertically list the column headers from columns that contain this value. [Example 1]
So far I have this:
=INDEX($B$13:$G$13,MAX(IF($B$14:$G500=K14,COLUMN($B$14:$G$500)-COLUMN($B13)+1)))
But it only returns the first instance of the value - not all column headers. I know this is because I'm using max - but surely this can be adapted?

Any help would be greatly appreciated.

Second less important problem is that I have 2 or more rows that need to be merged, but the cells contain "lists" eg.
R1, TA, AB
R2, TA
And I want the cell to look like this in the end:
R1, TA, AB, R2
(The order is not important just the unique count)
[Example 2]

I have 40 or so spreadsheets that are 400 unique characters strong (I really need to automate this but I'm a beginner when it comes to excel)

Thanks so much for any help!
Zen

Hi there... and welcome to the Excel Forum. Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic