# Need to remove duplicate entries from an index

1. ## 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

2. ## Re: Help - Need to remove duplicate entries from an index

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.

3. ## Re: Need to remove duplicate entries from an index

@Lacaycer:

in case you were not pressured to use formulae to eradicate the duplicates, you could do it using a built-in function.

copy the entire Employee Name column, for instance, onto a new sheet. highlight the entire column and proceed to > Data tab > Data Tools group > Remove Duplicates function > follow prompts.

and, in case you had already evaluated this option and discarded it in favour of a formula, my apologies for drudging up the past...

4. ## Re: Need to remove duplicate entries from an index

I probably should have given the reason I'm doing this as it might have led to a completely different resolution.

I wanted to capture the total number of audits and average scores for each employee who has been audited....on an ongoing basis. But we do not have an accurate employee list to run the countifs, averageifs off of. And rather then rely on whoever is auditing to enter new employees into a separate table every time someone new is added I wanted to have a formula that added new entries to a separate table.

If you can think of a better way to accomplish this I'm all ears. But the supplied formula worked well...although I wish I understood why it works.

5. ## Re: Need to remove duplicate entries from an index

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...

6. ## Re: Need to remove duplicate entries from an index

OK, so this all works fine, but how do I return the result in Alphabetical order?

7. ## Re: Need to remove duplicate entries from an index

That's not what you originally asked.

8. ## Re: Need to remove duplicate entries from an index

I realize it's not what I originally asked. But I cannot sort my indexed return. I was hoping there would be a small addition to the formula string that would alphabetize the return.

9. ## Re: Need to remove duplicate entries from an index

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

10. ## Re: Need to remove duplicate entries from an index

Did that formula work for you?

11. ## Re: Need to remove duplicate entries from an index

Which the one to remove the duplicates or the one to return an alphabetized index? Either way the answer is yes. They both worked.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.07580 seconds
• Memory Usage 9,114KB
• Queries Executed 15 (?)
Template Usage (32):
• (1)footer
• (1)forumrules
• (1)gobutton
• (11)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (11)postbit_legacy
• (11)postbit_onlinestatus
• (11)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (47):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete
• forumrules