Need to remove duplicate entries from an index

I need to be able to index the name column on a separate tab and remove all duplicated results. I've already spent WAY too much time trying to figure this out... all the great excel minds in my office were stumped with this one.

=INDEX('Audits and Scores'!A1:R4180,,6) - This is as far as I was able to make it, in terms of indexing.

And this is where I ended up with removing the duplicates but this formula doesn't work and I can't figure out why -
=INDEX('Audits and Scores'!F2:F39,,SMALL(IF(MATCH('Audits and Scores'!F2:F39,'Audits and Scores'!F2:F39,0)=ROW(INDIRECT(“1:”&ROWS('Audits and Scores'!F2:F39))),MATCH('Audits and Scores'!F2:F39,'Audits and Scores'!F2:F39,0),””),ROW(INDIRECT(“1:”&ROWS('Audits and Scores'!F2:F39)))))

If anyone could point me in the right direction it would be much appreciated

Try this formula in a new sheet, starting in second row, like in A2:

=IFERROR(INDEX('Audits and Scores'!$F$2:$F$101,MATCH(0,INDEX(COUNTIF($A$1:$A1,'Audits and Scores'!$F$2:$F$101),0),0)),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

To try to explain the formula:

This part: INDEX(COUNTIF($A$1:$A1,'Audits and Scores'!$F$2:$F$101),0) creates an array of numbers, it performs a COUNTIF comparing all of range 'Audits and Scores'!$F$2:$F$101 to the range above where you inserted the formula, so to start it only checks against A1, and as you copy down it checks with A1:A2, A1:A3, A1:A4, etc... So Countif will return either a 1 if it finds matches above (because it found only 1 above) or 0 if no matches are found....

Now MATCH(0,INDEX(COUNTIF($A$1:$A1,'Audits and Scores'!$F$2:$F$101),0),0) will find the first match of 0 in this array, that first 0 matches to a text string (name) that hasn't been yet brought in above, so that position is return and indexed against the same range of names 'Audits and Scores'!$F$2:$F$101 to pull first occurance of the new name.

The IFERROR() simply returns a blank when an error has occurred, which means no more unique names found to return 0 in the array.

Hope that helps...

That's not what you originally asked.

Not that simple....

I would suggest you add a helper column to the Audits and Scores sheet, e.g. in S2:

=IF(F2="","",IF(COUNTIF($F$2:$F2,F2)=1,COUNTIF($F$2:$F$101,"<"&F2),""))

copied down

Then in the new sheet use formula:

=IFERROR(INDEX('Audits and Scores'!$F$2:$F$101,MATCH(SMALL('Audits and Scores'!$S$2:$S$101,ROWS($A$2:$A2)),'Audits and Scores'!$S$2:$S$101,0)),"")

copied down

Did that formula work for you?