Finding largest value on mutiple criteria

[Richtheman87]

Hello,

I have a spreadsheet which users populate. They enter customers and then select certain criteria from a drop down.

Once they have done this I have some calculations running on a different tab.

I am finding the highest x numbers within the data. I know the formula that I need to use and it does work. However I now want to amend this formula so that it only incudes figures from the same postcode once.

I have created a basic example attached. So on this example Richard has all of his scores in England and in Liverpool. Two of the scores however are in the same postcode. L24FT.

Using my formula it currently pulls out all of the scores within this postcode, however if a score has already come from this postcode, I then want to ignore any other scores from this postcode.

I hope this makes sense. The attached file may make more sense.

Many thanks in advance for any help.example.xlsx


Crossposted here: http://www.mrexcel.com/forum/excel-q...ml#post4314120

[JBeaucaire]

Don't get caught up in the formulas. Columns are free, so let the source data do the exclusions for you.

In the attached I've grouped the data together where the Post Codes are the same, then added another column to do a preliminary selection of the MAX for you. Then your original formulas continue to work for the final output ranking.

[Richtheman87]

Thank you for your response.

The spreadsheet I attached is just an example that I mocked up. It is a basic version of the problem I am facing. The spreadsheet that I need a formula for is something that is sent out to people and thy enter their data. A bunch of reports are built automatically for them o different tabs within the same workbook. They could enter data in any order, so asking the to group the data would not work in this instance.

Anymore suggestions would be greatly received.

Many thanks again.

[JBeaucaire]

Interesting. I don't see for certain how you would build array functions like that to work with people entering data willy nilly. But OK. We'll see if anyone has an idea for that.

[Richtheman87]

Thank you. Hopefully someone can work some magic.

[Richtheman87]

I have attached a different spreadsheet.

This is more subject specific. I have encountered an additional parameter that I need to consider. Detailed in the spreadsheet.

Any help as always appreciated.example2.xlsx

[AliGW]

Is this the same query as this?

http://www.mrexcel.com/forum/excel-q...xclusions.html

[kvsrinivasamurthy]

Pl see file . Six ARRAY formulas are used for 6 columns
One Eg

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Richtheman87]

Hello,

Yes this is the same query.

Thank you for your help. The example that you have posted doesn't get to the result that I am after. The top scores for Gerry should be 8, 6 ,5 on your spreadsheet. As C and E columns have the same course code (FKS), but the level three score should count. Result should then be 8,6,5 and not 8,7,5.

[samba_ravi]

=SUMPRODUCT(LARGE(INDEX($C$5:$G$7,MATCH($A13,$A$5:$A$7,0),)*(TRANSPOSE(FREQUENCY(MATCH($C$4:$F$4,$C$4:$F$4,0),MATCH($C$4:$F$4,$C$4:$F$4,0)))>0)*($C$3:$G$3=$C$11),COLUMNS($C13:C13)))

Please Login or Register  to view this content.

Try this and copy towards right
see attached file

[Richtheman87]

Many thanks for your help, I appreciate it greatly.

Your formula appeared to work, however I am unsure how it knows to take higher level qualification when the level row (2) is not referenced at all in the formula?

I have added a couple of columns to the end of the table on the attached file and put in two more courses with the same code. I copied the formula over but it doesn't appear to take the highest level score.

Any suggestions?

[jason.b75]

Why is second highest 5 (German), and not 6 (English)?

[Richtheman87]

Yes that is the point I am trying to make. The formula has taken the score 4 for English which is a level 6 and ignored the score of 6 which is a level 8 score.

If the formula could be amended to take the highest level score then I think it would be perfect.

[jason.b75]

Yes that is the point I am trying to make. The formula has taken the score 4 for English which is a level 6 and ignored the score of 6 which is a level 8 score.

If the formula could be amended to take the highest level score then I think it would be perfect.

My point was that you had German in your expected results in row 23, when your explanation dictates that it should be English Level 8, which you have omitted from your results completely.

So do we provide you with a solution based on your explanation, or your example results?

When your 2 pieces of information contradict each other we have to guess which one to follow. Inevitably when it requires a complex solution, the guess will always be wrong.

This is why we simply ask for accurate examples, so that we don't waste time on wrong answers.

[Richtheman87]

Sorry that is my mistake. The example expected results on row 23 was from the original example, prior to changes made to the formula.

I have attached an updated example with the new expected results on row 23.

Apologies again for my error, I should have checked the entire spreadsheet prior to posting it again.

[JBeaucaire]

Is this the same query as this?

http://www.mrexcel.com/forum/excel-q...xclusions.html

Moderator Side Note:
Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).



When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)

[Richtheman87]

Sorry, new to this I did amend my first post to include the link to the other site. I thought this was enough?

Apologies again.

[bebo021999]

Can we use helper row 8?

I use row 8 to mark 1 for higher level and 0 for lower level. (same subj, type, code)

C8=IF(C2=MAX(C2,(C$1&C$3&C$4=($C$1:$H$1)&($C$3:$H$3)&($C$4:$H$4))*$C$2:$H$2),1,0)

(...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.)

Drag accross.

Now for Gerry in C14: I tried to combine new record from old one (row 5) and condition row( row 8) to get new one with highest level then use LARGE to get priority one:

Please Login or Register  to view this content.

Ctrl-shift-enter

Drag accross

See attachment.

[Richtheman87]

Hello,

Thanks ever so much for your help.

This appears to work, however I have discovered that if one of the students does not have a result in the row that has a marker then it ignores their lowel level score.

The attachment shows the problem. I have removed the level 3 score in Spanish for Gerry, but the formula does not pick up the level 2 Spanish results.

I should maybe have mentioned that not every student will have a score in both levels, but when they do it needs to take the highest level score regardless.

I honestly really appreciate all your help guys. Nearly there!

[JBeaucaire]

Sorry, new to this I did amend my first post to include the link to the other site. I thought this was enough?

Apologies again.

Yes, your amendment of post #1 is great. Thanks for that. All good.

[bebo021999]

I use n helper rows for n names. It is inconvernience, but I think there is better solution outside.
You can hide helper rows if needed.

[Richtheman87]

Thank you so much.

I will try and get this to work on my master spreadsheet.

It will be a bit messy with the helper rows, but if that is the only way, I will need to make it work like that.

[Richtheman87]

Sorry I have just spotted something else on your solution. (again my fault for not mentioning)

If a student has two scores with the same level eg two level 2 Spanish results, the formula needs to take the highest score only. At present it appears to take both.

Sorry for being a pain!

I have attached an updated example.

example8.xlsx

[bebo021999]

See my amendment