Ranking numbers with additional criteria

[needhelpp]

I'm not bad with excel in general, but this one has really stumped me.

I have a list of 20 and I need the column next to this list to produce a character (doesn't matter which) where the number is in the top 10. However this is where it gets complicated, the 20 numbers are technically separated into 10, 5 and 5. I need at least 4 numbers from the first 10 and 2 numbers in each of the next two groups of 5.

Eg.

1
2 yes
2 yes
2
2
4 yes
2
4 yes
1
1
--
6 yes
3
5 yes
5 yes
5 yes
--
2
2
4 yes
5 yes
1

FYI I can't use macros or vba for this, and only have these 2 columns plus 1 more to work with. Help would be extremely appreciated!

[:) Sixthsense :)]

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

[needhelpp]

Hopefully this helps. I'll be very impressed if this gets solved.

[needhelpp]

It could also help if someone is able to explain how to select a standard top 10 from the whole list, where the 10th and 11th numbers are the same.

[needhelpp]

really?? not one genuine response? If it's too hard just say so.

[JBeaucaire]

really?? not one genuine response? If it's too hard just say so.

I'm having trouble understanding the point of this post. Are you double-dog daring someone to answer?

[needhelpp]

I'm having trouble understanding the point of this post. Are you double-dog daring someone to answer?

Lol, no I'm starting to question whether it's actually possible or not... so if anyone out there is looking at it and doesn't see a solution it'd be very much appreciated if they could let me know, might have to just give up on this one.

[:) Sixthsense :)]

From my point of view this one is possible...

Because I seen more complicated queries are solved easily by the Gurus/Experts/Members of this forum so nicely without going for vba approach.

So please wait for some time so that everyone will have a look at it and share their thoughts

[:) Sixthsense :)]

I need at least 4 numbers from the first 10 and 2 numbers in each of the next two groups of 5.

Thanks for the attachment file and explanation, but one question from my end according to your above statement it will be 4+2+2 = 8 then how you can expect top 10? I am little bit confused on it

[needhelpp]

Thanks for the attachment file and explanation, but one question from my end according to your above statement it will be 4+2+2 = 8 then how you can expect top 10? I am little bit confused on it

"At least" are the key words... so it's basically those 8 plus the next two highest numbers out of the whole list.

[vlady]

the condition are really odd to me

columns F and G are the only ones available to be edited. Need to mark top 10 numbers in column F, however the "first 10" must have at least 4 marked, the "first 5" must have at least 2 marked, and the "second 5" must have at least 2 marked.

1.in your first condition you said FIRST 10 must have atleast 4 but you marked the the 2's in there.
2. in your second condition says atleast 2 marked why the 3 there not marked with x

look at the file below.I used helper columns G and H final result is in Column I
help.xlsx

[needhelpp]

the condition are really odd to me

1.in your first condition you said FIRST 10 must have atleast 4 but you marked the the 2's in there.
2. in your second condition says atleast 2 marked why the 3 there not marked with x

look at the file below.I used helper columns G and H final result is in Column I
Attachment 258318

Sorry for the confusion. When I say at least 4, i mean at least 4 numbers must be marked out of the first 10, and so on for the other 2 sub-groups.

[TheCman81]

You could use 3 seperate formulas in each section

Please review the attached and let me know if it works for you

NeedhelpPP v1.xlsx

[needhelpp]

You could use 3 seperate formulas in each section

Please review the attached and let me know if it works for you

Attachment 258326

I like what you've done to allow for ties, so it has solved the first part. The second part is the remaining 2 numbers, which are supposed to be the next 2 highest numbers but can be chosen from the entire list (to complete the 10).

Thanks for your help!

[XOR LX]

Hi,

Those of a sensitive disposition should look away now, but try this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in F5 and copy down:

=IF(SUM((ROW(E5)<=ROW($E$14))*(ISNUMBER(MATCH(E5+1/ROW(E5),(LARGE($E$5:$E$14+1/ROW($E$5:$E$14),ROW(INDIRECT("1:4")))),0))),((ROW(E5)>ROW($E$14))*(ROW(E5)<=ROW($E$19))*(ISNUMBER(MATCH(E5+1/ROW(E5),(LARGE($E$15:$E$19+1/ROW($E$15:$E$19),ROW(INDIRECT("1:2")))),0)))),((ROW(E5)>ROW($E$19))*(ISNUMBER(MATCH(E5+1/ROW(E5),(LARGE($E$20:$E$24+1/ROW($E$20:$E$24),ROW(INDIRECT("1:2")))),0)))),--ISNUMBER(MATCH(E5+1/ROW(E5),(LARGE(IF((ROW($E$5:$E$24)<=ROW($E$14))*(ISNUMBER(MATCH($E$5:$E$24+1/ROW($E$5:$E$24),(LARGE($E$5:$E$14+1/ROW($E$5:$E$14),ROW(INDIRECT("1:4")))),0)))+((ROW($E$5:$E$24)>ROW($E$14))*(ROW($E$5:$E$24)<=ROW($E$19))*(ISNUMBER(MATCH($E$5:$E$24+1/ROW($E$5:$E$24),(LARGE($E$15:$E$19+1/ROW($E$15:$E$19),ROW(INDIRECT("1:2")))),0))))+((ROW($E$5:$E$24)>ROW($E$19))*(ISNUMBER(MATCH($E$5:$E$24+1/ROW($E$5:$E$24),(LARGE($E$20:$E$24+1/ROW($E$20:$E$24),ROW(INDIRECT("1:2")))),0))))=0,$E$5:$E$24+1/ROW($E$5:$E$24)),ROW(INDIRECT("1:2")))),0)))=1,"X","")

Regards

[needhelpp]

WOW that is extremely impressive. Can I ask what the logic used is, just in plain terms? Otherwise thanks a lot!!

EDIT: Just realised I need it to work on google docs. Any other way of creating an "Array formula"?

[XOR LX]

Sorry - have absolutely no knowledge of Google Docs.

The logic is, basically:

1) Since 4 numbers must come from the first group (of 10), then find the 4 largest numbers in that group. Mark with "X".
2) Since 2 numbers must come from the second group (of 5), then find the 2 largest numbers in that group. Mark with "X".
3) Since 2 numbers must come from the third group (of 5), then find the 2 largest numbers in that group. Mark with "X".
4) Find the 2 largest numbers in the entire set that do not already form part of either 1), 2) or 3) above. Mark with "X".

Regards

[needhelpp]

I was under the impression it was almost identical, but I guess it does have differences. Thanks again for your help!

[daddylonglegs]

Seems like you don't mind having one helper column so why not use CMan81's approach to get your first 8 "x"s in column G then use this formula in F5 copied down to get those 8 and the additional 2

=IF(OR(G5="x",COUNTIFS(G$5:G$24,"<>x",E$5:E$24,">"&E5)+COUNTIFS(G$5:G5,"<>x",E$5:E5,E5)<3),"x","")

......note that if this is just an intermediate step on the way to summing the "top ten scores" (based on your criteria) then there are easier ways than individually identifying those ten

[needhelpp]

I actually realised that I forgot one condition involving the second helper column, so it cannot be used. When the second column has a "K" in it, the adjacent cell must be selected. I tried to add this in myself but I ended up with 11 numbers selected when the "K" cell wouldnt have been in the original 10.

Again help would be greatly appreciated

[daddylonglegs]

How would that condition fit in with the others? Does it take precedence over the other conditions? For example if you have "K" against every entry in the first set of 5 you wouldn't be able to have at least 4 from the set of ten and also 2 from the second set of 5. How would that be resolved?

Are you saying that you can't have any helper columns - why not? You could hide any helper columns or put them far over to the right......or even on another worksheet.

[needhelpp]

The way it's set up I'd prefer to just use the one column for the formulae. The "K" condition will only be adjacent to 1 cell and does take precedence.

[JBeaucaire]

Please stop quoting everyone's entire post into your replies. Just use the Reply Box below. Thanks.

[XOR LX]

Hi,

I'm still not clear about this new criterion.

If a "K" appears in the first group of 10, say, does that mean this now reduces the 'need-to-mark' for that group from 4 to 3?

Or is it that all these first three conditions still apply, but that, from the final two choices to make up the 10, one of these must be that corresponding to the "K"?

Regards

[needhelpp]

If a "K" appears in the first group of 10, say, does that mean this now reduces the 'need-to-mark' for that group from 4 to 3?

Or is it that all these first three conditions still apply, but that, from the final two choices to make up the 10, one of these must be that corresponding to the "K"?

Well it doesn't really reduce it from 4 to 3, but it also doesn't have to come from the final 2. Basically the difference in output would be if the cell adjacent to the "K" isn't in the 10 that would've been marked originally, it would then need to be marked instead of the 10th number (the 2nd number in the last step), however if the "K" cell is already marked, then the output would be the same. (But the minimum of 4 - 2 - 2 always applies).

[XOR LX]

A further condition like that is going to at least double, if not triple, the length of the already monstrous solution I gave you.

I have to say that, like daddylonglegs and others, I'm curious to know the reason you aren't able to use a single helper column, especially given that: a) there are several thousand of them available (unless you're using every single one, which I very much doubt); b) they can be hidden (if desired); c) you would be looking at a far simpler (and more elegant) solution.

And unless you have an extremely good reason for not doing so, I'm starting to wonder if JBeaucaire wasn't on to something when he hinted that this might be, more than anything else, some sort of 'challenge' question?

I've no doubt you have your reasons, but if I could just ask you to share them with me, I would be very grateful. Just what is this reason that is evidently so important that you can't use one single, hidden, helper column?

Regards

[needhelpp]

Not a challenge question I can assure you. I'm creating a spreadsheet which will be used by amateurs so I wanted to minimise the complexity of the sheet (I know this maximises the column's complexity). To be honest I hadn't heard it from you so I assumed you didn't have too much issue using one column, but if it helps i'm happy to use helper columns and just hide them.

I really appreciate your help, I'm lost without it.

[XOR LX]

Ok,

So based on the fact that you'll accept helper column(s), I'll let the others come back with their solutions if they wish, which will be far more elegant than mine, since I haven't got time to look into theirs right now and so I'll stick with my beast...

I assume column F is now going to house your "K", so in column G, change the first formula I gave you to:

=IF(SUM((ROW(E5)<=ROW($E$14))*(ISNUMBER(MATCH(E5+1/ROW(E5),(LARGE($E$5:$E$14+1/ROW($E$5:$E$14),ROW(INDIRECT("1:4")))),0))),((ROW(E5)>ROW($E$14))*(ROW(E5)<=ROW($E$19))*(ISNUMBER(MATCH(E5+1/ROW(E5),(LARGE($E$15:$E$19+1/ROW($E$15:$E$19),ROW(INDIRECT("1:2")))),0)))),((ROW(E5)>ROW($E$19))*(ISNUMBER(MATCH(E5+1/ROW(E5),(LARGE($E$20:$E$24+1/ROW($E$20:$E$24),ROW(INDIRECT("1:2")))),0))))),"X",IF(--ISNUMBER(MATCH(E5+1/ROW(E5),(LARGE(IF((ROW($E$5:$E$24)<=ROW($E$14))*(ISNUMBER(MATCH($E$5:$E$24+1/ROW($E$5:$E$24),(LARGE($E$5:$E$14+1/ROW($E$5:$E$14),ROW(INDIRECT("1:4")))),0)))+((ROW($E$5:$E$24)>ROW($E$14))*(ROW($E$5:$E$24)<=ROW($E$19))*(ISNUMBER(MATCH($E$5:$E$24+1/ROW($E$5:$E$24),(LARGE($E$15:$E$19+1/ROW($E$15:$E$19),ROW(INDIRECT("1:2")))),0))))+((ROW($E$5:$E$24)>ROW($E$19))*(ISNUMBER(MATCH($E$5:$E$24+1/ROW($E$5:$E$24),(LARGE($E$20:$E$24+1/ROW($E$20:$E$24),ROW(INDIRECT("1:2")))),0))))=0,$E$5:$E$24+1/ROW($E$5:$E$24)),ROW(INDIRECT("1:2")))),0))=1,"Y",""))

Then, in column H, this (also array-entered):

=IF(INDEX($G$5:$G$24,MATCH("K",$F$5:$F$24,0))<>"",IF(G5="","","X"),IF(F5="K","X",IF(G5="X","X",IF(AND(G5="Y",SUM(IF($G$5:$G$24="Y",$E$5:$E$24))=2*E5,ROW(E5)=MIN(IF($G$5:$G$24="Y",ROW($E$5:$E$24)))),"X",IF(AND(SUM(IF($G$5:$G$24="Y",$E$5:$E$24))<>2*E5,G5="Y",MAX(IF($G$5:$G$24="Y",$E$5:$E$24))=E5),"X","")))))

Or put the first in a different column somewhere and hide if you wish.

Regards

[needhelpp]

Ok that works I think! Hopefully the last question, if I were to add a third "Y" number how would I go about that? (making the it a top 11).

Thanks a lot for your help XOR LX! This should be the last thing.

[needhelpp]

I was in the middle of attempting to add the additional "Y" (shown below) and I think I found a problem with your formula. The "K" factor - when it's not already included in the 10 - shouldn't always replace a "Y" cell, it sometimes needs to replace an "X" cell. In some occasions it's better off replacing an "X" cell from the same group that's required by the other rules, because the "Y" cells may actually be higher values.

Looking over my reply to this topic before I think I may have been unclear on this, so I apologise for that.


**Attempt at adding the additional "Y"**
=IF(INDEX($F$5:$F$24,MATCH("K",$H$5:$H$24,0))<>"",IF(F5="","","X"),IF(H5="K","X",IF(F5="X","X",IF(AND(F5="Y",SUM(IF($F$5:$F$24="Y",$E$5:$E$24))=3*E5,OR(ROW(E5)=MIN(IF($F$5:$F$24="Y",ROW($E$5:$E$24))),ROW(E5)=SMALL(IF($F$5:$F$24="Y",ROW($E$5:$E$24)),2))),"TEST1",IF(AND(F5="Y",SUM(IF($F$5:$F$24="Y",$E$5:$E$24))<>3*E5,E5=MAX(IF($F$5:$F$24="Y",$E$5:$E$24))),"TEST2",IF(AND(F5="Y",SUM(IF($F$5:$F$24="Y",MIN($E$5:$E$24)),IF($F$5:$F$24="Y",SMALL($E$5:$E$24,2)))=2*E5,ROW(E5)=MIN(IF($F$5:$F$24="Y",ROW($E$5:$E$24)))),"TEST3",IF(AND(F5="Y",IF($F$5:$F$24="Y",SMALL($E$5:$E$24,2))<>IF($F$5:$F$24="Y",SMALL($E$5:$E$24,1)),IF($F$5:$F$24="Y",SMALL($E$5:$E$24,2))<>IF($F$5:$F$24="Y",SMALL($E$5:$E$24,3)),IF($F$5:$F$24="Y",SMALL($E$5:$E$24,3))<>IF($F$5:$F$24="Y",SMALL($E$5:$E$24,1)),OR(E5=IF($F$5:$F$24="Y",SMALL($E$5:$E$24,2)),E5=IF($F$5:$F$24="Y",SMALL($E$5:$E$24,3)))),"TEST6","")))))))

[XOR LX]

"if the cell adjacent to the "K" isn't in the 10 that would've been marked originally, it would then need to be marked instead of the 10th number"

Your previous statement.

I'm afraid I'll have to drop out of this one now, but hopefully I've given you something to get you going and best of luck with it.

Regards

[needhelpp]

Ok I've been close to getting it myself but I'm still coming across a number of issues, so if anyone still wants to help it would be appreciated.