• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problems and Ninjas

P

Peacedout

Member
-There are many problems.
-There are currently three Ninjas.
-In the "real" spreadsheet, the Ninja's name next to the problem number he's solved are returned by a match/index formula.
-Ninjas solve more than one problem.
-The Output I want: a list with one instance of each unique Ninja.
-The formula must allow for the addition of new Ninjas, whose names are currently unknown.

I'm sure it's simple, but I just.don't.get.it.

Thanks in advance :)
 

Attachments

  • Ninjas and Problems.xlsx
    13.4 KB · Views: 18
Peacedout said:
-There are many problems.
-There are currently three Ninjas.
-In the "real" spreadsheet, the Ninja's name next to the problem number he's solved are returned by a match/index formula.
-Ninjas solve more than one problem.
-The Output I want: a list with one instance of each unique Ninja.
-The formula must allow for the addition of new Ninjas, whose names are currently unknown.

I'm sure it's simple, but I just.don't.get.it.

Thanks in advance :)
Click to expand...

Do you want the ninja's name together with the number of problems he/she has solved? That's not clear in question
 
You may want to have a read of :
http://chandoo.org/forum/threads/fo...t-duplicate-unique-strings-as-an-array.11155/

Where a number of techniques are discussed

I'd use:
=IFERROR(T(OFFSET(B$1,SMALL(IF(FREQUENCY(MATCH(sdata,sdata,0),MATCH(sdata,sdata,0))>=1,ROW(sdata)), ROW(OFFSET(A$1,,,SUM(1/COUNTIF(sdata,sdata)))))-1, 0)),"") Ctrl+Shift+Enter
where you have a Named Formula sdata: =Table1[Ninja]

To apply:

Copy the above formula
Select a number of cells in a Column
Say H1:H10
Paste (Ctrl V)
press Ctrl+Shift+Enter not just Enter
 
Last edited:
one more formula - IFERROR(INDEX(sdata,MATCH(0,COUNTIF($I$14:I14,sdata),0)),"")

Again array enter..

Assuming the same defined name range as mentioned by Hui..
 
The Pivot Table seems to be the quickest solution (I tried it. It worked.) mainly because I'm not understanding how to implement the first two options. (On the upside, I do know how to make a formula an array formula.)

Now, a few questions so I can continue working on Hui's and Asheesh's formulas and trying to understand the 007 Challenge (008?) that Hui referred me to:

1) What is the difference between "distinct" and "unique"?
2) What do you mean by a "Named Formula sdata: =Table1[Ninja]"? My table is Table1 and the Ninja names are in a range named [Ninja]. But what's a Named Formula?

I did attach a simple file with my original post, if that makes it any easier.

Thank you all for patience and your quick responses.
 
Actually, while the pivot table option will work with my sample data (because the data is already entered), it WON'T work in my real application (because I'm setting up a blank spreadsheet that someone else will fill in with data later), so I will have to figure out the formulas above.
 
'Tis a thing of beauty, Narayank991!

Thank you all, gentlemen, for your input. I have lots of information to digest in the links you've directed me to, as well.

I might not be very good at this stuff, but I really enjoy it! :)

Thanks again.
 
You must log in or register to reply here.
Back
Top