+ Reply to Thread
Results 1 to 16 of 16

match and index for more than one record if data is the duplicated

  1. #1
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    match and index for more than one record if data is the duplicated

    have a slight issue which i had thought of

    but my sheet has duplicates as it needs to be audited and updated but am unable to see them when i search on the search tab, have had a look on google and came across this

    Please Login or Register  to view this content.
    but im not sure where i would fit this in to my current index and match formula if you kind and clever people could have a look and help me out that would be bosting

    cheers
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: match and index for more than one record if data is the duplicated

    One way, in H2, which tells your where the next/previous duplicate is to befound:

    =IF(COUNTIF(B:B,B2)>1,IF(COUNTIF($B$2:B2,B2)=1,"Duplicated in row "&LOOKUP(10^300,ROW($B$2:$B$200)/($B$2:$B$200=B2)),"Duplicated from row "&MATCH(B2,B:B,0)),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: match and index for more than one record if data is the duplicated

    Auditing would be better from the PC list as this is the source data

    Attaching a sheet which is protected is also frustrating for anyone trying to help you

    however the hidden column G when the small function is used could be modified to finding the 2nd, 3rd, 4th smallest and they could appear in the lines below in the table. I might have pasted a change for you if i could have written the formula on the sheet

  4. #4
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: match and index for more than one record if data is the duplicated

    Quote Originally Posted by davsth View Post
    Auditing would be better from the PC list as this is the source data

    Attaching a sheet which is protected is also frustrating for anyone trying to help you

    however the hidden column G when the small function is used could be modified to finding the 2nd, 3rd, 4th smallest and they could appear in the lines below in the table. I might have pasted a change for you if i could have written the formula on the sheet
    apologies i thought i had removed the protection the password is unlock

  5. #5
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: match and index for more than one record if data is the duplicated

    Quote Originally Posted by Glenn Kennedy View Post
    One way, in H2, which tells your where the next/previous duplicate is to befound:

    =IF(COUNTIF(B:B,B2)>1,IF(COUNTIF($B$2:B2,B2)=1,"Duplicated in row "&LOOKUP(10^300,ROW($B$2:$B$200)/($B$2:$B$200=B2)),"Duplicated from row "&MATCH(B2,B:B,0)),"")
    cheers, that is great but wanted the match on the search to show the duplicated records

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: match and index for more than one record if data is the duplicated

    I don't follow you. What EXACTLY do you want to see, and where do you want to see it??

  7. #7
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: match and index for more than one record if data is the duplicated

    Quote Originally Posted by Glenn Kennedy View Post
    I don't follow you. What EXACTLY do you want to see, and where do you want to see it??
    ok sorry if not been very clear

    if the situation shown in picture called "data" occurs then i would like ( in an ideal world ) for the search page to show both but as you can see in the other picture it only shows one, the first record it comes to
    Attached Images Attached Images
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: match and index for more than one record if data is the duplicated

    For some reason... the formula in G6

    =IFERROR(SMALL(IF(ISNUMBER(SEARCH($I$4,'PC List'!$B$2:$G$391)),ROW('PC List'!$B$2:$G$391)),ROWS(H$6:H6)),)

    becomes

    =IFERROR(SMALL(IF(ISNUMBER(SEARCH($I$4,'PC List'!$G$2:$G$391)),ROW('PC List'!$G$2:$G$391)),ROWS(H$6:H7)),)

    in G7 and below. Copy the first formula down the column... Isn't that what you want??

  9. #9
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: match and index for more than one record if data is the duplicated

    Quote Originally Posted by Glenn Kennedy View Post
    For some reason... the formula in G6

    =IFERROR(SMALL(IF(ISNUMBER(SEARCH($I$4,'PC List'!$B$2:$G$391)),ROW('PC List'!$B$2:$G$391)),ROWS(H$6:H6)),)

    becomes

    =IFERROR(SMALL(IF(ISNUMBER(SEARCH($I$4,'PC List'!$G$2:$G$391)),ROW('PC List'!$G$2:$G$391)),ROWS(H$6:H7)),)

    in G7 and below. Copy the first formula down the column... Isn't that what you want??

    Sir thanks, i feel rather silly right now sorry all but thanks for input and help

    edit

    ok i did that and then this happens ????

    attached is updated spreadsheet
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by murray83; 03-12-2018 at 09:32 AM.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: match and index for more than one record if data is the duplicated

    No problem. We all do silly things.... the trick is to learn from the goof-ups.

  11. #11
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: match and index for more than one record if data is the duplicated

    Quote Originally Posted by Glenn Kennedy View Post
    No problem. We all do silly things.... the trick is to learn from the goof-ups.
    cheers but can you see my edit

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: match and index for more than one record if data is the duplicated

    Not for me.... I am away for an hour or so. Did you array enter the formula?

  13. #13
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: match and index for more than one record if data is the duplicated

    Quote Originally Posted by Glenn Kennedy View Post
    Not for me.... I am away for an hour or so. Did you array enter the formula?
    yes thats ok im here all day till 18:00

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: match and index for more than one record if data is the duplicated

    In G7, copied down (array):

    =IFERROR(IF($I$4="","",SMALL(IF(ISNUMBER(SEARCH($I$4,'PC List'!$B$2:$G$391)),ROW('PC List'!$B$2:$G$391)),ROWS(G$7:G7))),"")


    I also changed the 3 lookups to include IFERROR, e.g. I7:

    =IFERROR(INDEX('PC List'!$A:$G,$G7,MATCH(I$6,'PC List'!$A$1:$G$1,0)),"")
    Attached Files Attached Files

  15. #15
    Forum Contributor murray83's Avatar
    Join Date
    05-05-2016
    Location
    Daventry
    MS-Off Ver
    365
    Posts
    146

    Re: match and index for more than one record if data is the duplicated

    Quote Originally Posted by Glenn Kennedy View Post
    In G7, copied down (array):

    =IFERROR(IF($I$4="","",SMALL(IF(ISNUMBER(SEARCH($I$4,'PC List'!$B$2:$G$391)),ROW('PC List'!$B$2:$G$391)),ROWS(G$7:G7))),"")


    I also changed the 3 lookups to include IFERROR, e.g. I7:

    =IFERROR(INDEX('PC List'!$A:$G,$G7,MATCH(I$6,'PC List'!$A$1:$G$1,0)),"")
    sir you are indeed a Guru many many thanks

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: match and index for more than one record if data is the duplicated

    Lol. You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 06-17-2017, 03:28 AM
  2. [SOLVED] get record from other sheets ...use index match
    By hktom in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-13-2016, 08:55 AM
  3. Having issues with Same data being duplicated when using Index and match
    By jasonwallace in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-07-2014, 03:59 PM
  4. [SOLVED] Index Match for one record with 4 possible results
    By onemoremile in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-03-2013, 04:24 PM
  5. find 2nd instance of a duplicated value in an index/match formula?
    By Bobbo Jones in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 06-14-2013, 09:42 AM
  6. Replies: 3
    Last Post: 07-10-2012, 07:37 AM
  7. Problem of using INDEX/MATCH function with duplicated values
    By offshore-safety in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2010, 05:45 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1