Match / Index duplicate value problem

[muppetthumper]

Hi all,

I have a table with 3 columns, the first being static text "issue types" and the second two dynamic values created from counting instances of each issue type from another sheet.

I want to create a table of the top 5 issues which updates automatically. I can use the LARGE function to list the top 5 values, and then need to match these to the issue type.

Where I'm having problems is when there are duplicate numbers for results in the top 5, it will match the first instance 'issue type' for all duplicated entries. Instead I need to put a check in place so that it moves down the table rows to the next instance of the matched number and indexes that issue type instead.

I know that probably isn't very clear, so please see the attached spreadsheet example for ready made tables and formulas.

Thanks!

[NBVC]

Try:

=INDEX($A$2:$A$16,SMALL(IF(C$2:C$16=$F4,ROW($A$2:$A$16)-ROW($A$2)+1),COUNTIF($F$4:$F4,$F4)))

and confirm it with CTRL+SHIFT+ENTER not just ENTER and copy down

[muppetthumper]

Try:

=INDEX($A$2:$A$16,SMALL(IF(C$2:C$16=$F4,ROW($A$2:$A$16)-ROW($A$2)+1),COUNTIF($F$4:$F4,$F4)))

and confirm it with CTRL+SHIFT+ENTER not just ENTER and copy down

Although I have failed in my attempts to understand how the logic is working, it works a treat! Thanks very much

[NBVC]

Please Login or Register  to view this content.

is like a vlookup for multiple matches... It uses the Index() function which requires you to index the table or column to extract from and it requires the Row number to extract from which is gotten from this part: SMALL(IF(C$2:C$16=$F4,ROW($A$2:$A$16)-ROW($A$2)+1)

The Small() function just allows us to step up and extract one match at a time, starting from the first match found. It will extract only if a match to F4 is found in range C2:C16 and then it will return the corresponding row number within the range. The -ROW($A$2)+1 is added for robustness (incase you insert rows above, then the result won't skew).

The last part: COUNTIF($F$4:$F4,$F4)) is the k factor for the Small() function, which is like a step factor in a For...next loop in VBA. It "loops" through each consecutive match (i.e. it is counting the number of times the current item in F matches from the start of column F to the current position and uses that to determine the k factor)

The CSE confirmation is because it is an array formula and you have to confirm with those keys to make these formulas work.

Then you copy down...

Hope this clarifies a bit.

[JimLau]

I have to thank you for this formula. I have a thread open that was about the same thing and this solved my problem. Thanks again

[yahiatlm]

Thanks a lot for this solution it the best one that I found since now.

Best regards.

[amopro]

Wow great solution ever. Thanks a lot.

[Livvi]

I am currently having a similar issue with some data that I am using. I have been trying to manipulate the formula for my own information but I'm having no success. I think I won't be able to use the "Small" function given that my duplicate information is in text.

Logically the formula provided is what I'd like to use with my data, so that if for example there are duplicate names, the formula moves down to the next instance. However instead of duplicate numbers I have duplicate text.

Can this formula be tweaked to apply to duplicate text?

See my sample file below attached.

[FDibbins]

Livvi, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[Livvi]

Opps! Apologies!

[FDibbins]

Not a problem

[soledad]

I am currently having a similar issue with some data that I am using. I have been trying to manipulate the formula for my own information but I'm having no success. I think I won't be able to use the "Small" function given that my duplicate information is in text.

Logically the formula provided is what I'd like to use with my data, so that if for example there are duplicate names, the formula moves down to the next instance. However instead of duplicate numbers I have duplicate text.

Can this formula be tweaked to apply to duplicate text?

See my sample file below attached.

Try this for C14 cell

Please Login or Register  to view this content.

Copy down then right

[FDibbins]

soledad Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction then send a private message to them asking for clarification.

[sancv90]

Thanks a lot for this solution