+ Reply to Thread
Results 1 to 11 of 11

Need to remove duplicate entries from an index

  1. #1
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    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
    Attached Files Attached Files
    Last edited by Lacaycer; 03-15-2012 at 03:48 PM. Reason: Problem solved

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    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. #4
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    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. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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...
    Last edited by NBVC; 03-15-2012 at 04:18 PM.

  6. #6
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    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. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Need to remove duplicate entries from an index

    That's not what you originally asked.

  8. #8
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    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. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #10
    Forum Contributor darknation144's Avatar
    Join Date
    01-24-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    552

    Re: Need to remove duplicate entries from an index

    Did that formula work for you?
    If someone helped give them rep using the star button.

    If you have received a satisfactory solution please mark the thread solved. If not Fotis will come for you at night :P

  11. #11
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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 (?)
More Information
Template Usage (32):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (11)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (11)postbit_legacy
  • (11)postbit_onlinestatus
  • (11)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
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
  • ./showthread.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_facebook.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_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • 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
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1