How To: Extract unique values from 3 columns into 1 column.

Good Morning,

I have been going around this problem for the last 2 days and I have been unable to come up with a solution. Any and all help will be amazing.


I am working on a report that will tell us how our managers are ranking the interviewees.
For example: I want to know how Phillip is doing, (Look at the info from table section):

Capture3.JPG

What this report will ultimately tell us is if the managers need training in the interview process. For example if you view the pic above(look at the Example info), you can tell that Phillip is grading the interviewee's extremely low, which is potentially hurting our hiring, he will need to be retrained.

Here is what I am working with. (all info is dummy test info)

Capture.JPG

I need the 3 columns (Interviewer 1, Interviewer 2, Interviewer 3) all merged into a single column. The names on the Interviewer columns are interchangeable and names will be placed randomly with no order what so ever.

I have tried many different formulas but haven't been able to get it to work.
This is the closest I have been able to get, but I still can't get the other columns to work.

=IFERROR(INDEX($F$2:$F$30, MATCH(0,IF(ISBLANK(F2:F23),1,COUNTIF($R$1:R1, $F$2:$F$30)), 0)),"")

NOTE: The end goal is to get a report on how the managers are grading, and to know if the managers need to be retrained.

Good Afternoon Bernie,

First off, Thank you very much for taking your time to help me out, I really appreciate it.

I don't dabble much with macro's, but I did the following:
alt+f11
found the rawdata page and pasted the code you provided.
Saved macro.
went to cell T2 and copied entered the =Getuniques function and pressed ctrl+Shift+Enter
but the result was #Name?

Please advise as to what I should try next. Might there be a formula that I can try?

Thanks again.

Hi Bernie, I was able to make the code work, thanks but I ran into a problem. When I enter new information for new interviewees the formula doesn't auto update? is there a way for me to make it update automatically? I apologize for not being specific.

Thanks again

Interviews Score Sheet BETA.xlsm

I have provided the workbook, for this issue. I have found that using arrays doesn't auto populate new information. The problem is that I am not going to be the one that updates the workbook. so it needs to be as easy to understand as possible.

I am halfway with the first formula, but would like help finishing it up.

Thanks again.

Originally Posted by egarcia7

Attachment 452533

I have provided the workbook, for this issue. I have found that using arrays doesn't auto populate new information. The problem is that I am not going to be the one that updates the workbook. so it needs to be as easy to understand as possible.

I am halfway with the first formula, but would like help finishing it up.

Thanks again.

Any ideas of how to complete the formula?

This is the formula I have so far:

=IF(ROWS(P13:P13)>COUNTA(F13:F34),"",INDEX(F13:F34,AGGREGATE(15,6,(ROW(F13:F34)-ROW(F13)+1)/(F13:F34<>""),ROWS(P13:P13))))

The problem with the formula is as follows:
1)it lists all values and I need only the unique values.
2)I wasn't able to incorporate the other 2 columns into this formula.

Thanks for your help.