Need formula that will sort and display all duplicate values (bc each value has a unique corr. name)

[HolliIsAwesome]

Gawrsh I bombed that one. Did y'all go cross-eyed readin that title...

Mkay, so I have a spreadsheet set up that I use for scoring tests. It will automatically update each indice's numerical score for each unique corresponding name. So, often it has the same score - but because there are no duplicate names, I need to list each one.

For ex, here is a pic... in this example, there are 4 unique names for 2 sets of dups... the order that the dups are in does not matter, I just need both "77"'s to display each with their own name, and both "30"'s to display likewise.

https://drive.google.com/file/d/1H6Gq_pfRDhBdFfmnfJN1NfK0cBJOzC-0/view?usp=sharing

I've tried match, but that stops at the first finding and goes no further for me. I don't want to mess with finding a row number so the countifs or others that require a row number are not gonna work. I need a formula or formulas that are a one and done type thing.

Thank y'all *tips hat*

 

[Eric W]

Hi again!

Yeah, I did have to read the title a few times. But I think I finally figured it out.


I'm having a terrible time trying to show a formatted screen print. But based on your sample workbook, use these formulas:

AC3:

=LARGE($Z$3:$Z$11,ROWS($Z$3:$Z3))

AA3:

=INDEX($X$3:$X$11,SMALL(IF($Z$3:$Z$11=AC3,ROW($Z$3:$Z$11)-ROW($Z$3)+1),COUNTIF($AC$3:$AC3,$AC3)))

and confirm that one by pressing Control+Shift+Enter. Copy down both formulas as needed.


The LARGE formula is not too bad, but the INDEX formula is pretty tricky!

Hope this helps!

 

[HolliIsAwesome]

woooooooooooooooooooooooooow you DID IT oh Mylanta....

thank you!!! my goodness I would have never ever got that... not anytime soon that's for sure! you are ze best!

can you please 'xplain to me about the long formula (fit right in with my title quite nicely!) so I can have a take away from this? like an English translation

 

[Peter_SSs]

FWIW, if you don't want to worry about the C+S+E confirmation (& remember it if you edit the formula) you can use (for col AA) this slight variation that just requires normal entry.

Excel Workbook
	X	Y	Z	AA	AB	AC
3	Distemper		56	Alexithymia		81
4	Extraversion		30	Disinhibition		77
5	Neuroticism		67	Conscientiousness		77
6	Sel-Esteem		43	Neuroticism		67
7	Disinhibition		77	Distemper		56
8	Narcissism		39	Sel-Esteem		43
9	Alexithymia		81	Narcissism		39
10	Agreeableness		30	Extraversion		30
11	Conscientiousness		77	Agreeableness		30
List Dupes

Also, for future reference, you will get many more potential helpers if you post any sample data in small screen shots directly in your post. That way people do not have to go to another site to look and also they can copy your sample data to a worksheet to test (most helpers don’t like having to type out test data). My signature block has a link for several ways to do that. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

 

[Eric W]

I don't know how much you know about array formulas, but the basic idea is that they work look at a range of cells, which creates an internal array, then some additional functions look at the array and narrow down the results to a single value.

=INDEX($X$3:$X$11,SMALL(IF($Z$3:$Z$11=AC3,ROW($Z$3:$Z$11)-ROW($Z$3)+1),COUNTIF($AC$3:$AC3,$AC3)))

In this case, the part in red looks at every cell in the range Z3:Z11 and sees which ones match AC3, the current value returned by the LARGE formula. If they match, it returns the rows of the matching values, converted to an offset (blue). So for a value of 77, the array looks like:

{FALSE;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;9}

At this point, the COUNTIF kicks in, and it counts how many times we've seen 77 in column AC so far, (note the relative referencing), which will be 1 or 2 depending on the row. Say we're on row 5, so we count 77 2 times in AC3:AC5. Then the SMALL function takes the second smallest value from the array, ignoring non-numeric values, giving us 9, then the INDEX looks that up.

Hope this makes sense!