+ Reply to Thread
Results 1 to 7 of 7

Find highest duplicate and vlookup those duplicate.

  1. #1
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    2020
    Posts
    5

    Find highest duplicate and vlookup those duplicate.

    Hi, this is my first time using this forum (so apologizes if i'm not following the guideline)

    I have multiple rows which consists of numbers from 1 to 5 (row in yellow).

    I have a 5 by 4 array (array in green). They show the values that correspond to the values for each element in the yellow array. So for example the first 5 (under the A) corresponds to A5 from the green array or 2 (under the D) corresponds to 2D from the green array.

    What I would like to do is locate how many duplicates there in the yellow row (in the case below, there are two 5's) and then VLOOKUP those exact values from the green array and then print out the final result as (5A 5B).

    I know how to do it for say one value at a time, but I don't know how to do it for multiple values (but it has to start from the highest possible duplicate).

    I have attached the work book.

    Any advice/solution will be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,881

    Re: Find highest duplicate and vlookup those duplicate.

    I know you've tried to be clear, but I am horribly confused. I assume that the values in the green array are not 5A, 5B, etc, but something else. Otherwise there would be no need to look at the green array. You could get 5A and 5B from the yellow grid.

    I also assume that Excel 2020 is a Mac thing. I hope that this means you have access to TEXTJOIN.

    Finally, I am sure that I have HUGELY overcomplicated this, but we'll see!!

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    2020
    Posts
    5

    Re: Find highest duplicate and vlookup those duplicate.

    Hi Glenn, sorry I don't have excel 2020, I meant 365 for windows. I'm curious to know the logic behind the rule? Does it look for the highest possible duplicates first?
    Last edited by bob112233; 12-04-2020 at 04:58 PM.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,881

    Re: Find highest duplicate and vlookup those duplicate.

    Hi.

    1. Please amend your profile to show O365, as solutions are very version-specific.

    2. The salmon pink box whows the steps I went through to reach a solution. You can delete it. I left it there as an aide memoire, in case I needed to unpick the formula. The bit in H2: =AGGREGATE(14,6,B2:E2/(COUNTIF(B2:E2,B2:E2)>1),1) identifies the highest duplicate value.

    3. In the event that there are NO duplicates, it currently returns an ugly error message. Wrapping it in an IFERROR makes it return a blank, instead. See sheet.

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    2020
    Posts
    5

    Re: Find highest duplicate and vlookup those duplicate.

    THanks Glenn, I learnt something today

  6. #6
    Valued Forum Contributor
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    518

    Re: Find highest duplicate and vlookup those duplicate.

    I use Glenn Kennedy moderator custom function, please see the attached G14 cell custom formula
    Attached Files Attached Files

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,208

    Re: Find highest duplicate and vlookup those duplicate.

    Please try

    =TEXTJOIN(" ",,REPT(B6:E10,(MAX(MODE.MULT(B2:E2))=B2:E2)*(A6:A10=MAX(MODE.MULT(B2:E2)))))
    Attached Files Attached Files

+ 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. [SOLVED] VBA to find duplicate value in a string across entire row and delete one duplicate
    By Hillster in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 12-09-2019, 04:33 PM
  2. Find duplicate and compare which one of them is the highest
    By Barieq in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 10-01-2019, 03:43 AM
  3. Replies: 1
    Last Post: 10-12-2018, 12:19 PM
  4. How to vlookup for a single column to find duplicate values
    By JayeshG in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-27-2014, 12:14 PM
  5. Replies: 7
    Last Post: 05-28-2014, 03:19 AM
  6. Replies: 9
    Last Post: 02-08-2012, 12:08 PM
  7. Replies: 1
    Last Post: 01-26-2012, 10:06 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