Countif and match error on formula

[Jonces]

Hi All,

I would appreciate your assistance with the following problem. Please see attached excel spread sheet with the data.


Task: I would like to create a formula that allows me to select individual members based on specific criteria as follows: members with largest account balance(cell b) but the formula would specifically exclude members who have been selected in previous selection(Column A). This would mean if one member has been selected in my previous selection, he would be skipped this time and the next member, is selected instead.


I have attempted to use previous formula created by my co-worker but it keeps coming up with a error and i'm not sure where i'm going wrong with it. For reference, i included the formula in the spreadsheet for your review. I would appreciate if you could review it and see where is the issue in creating it?

Any form of assistance or direction is greatly appreciated!

Cheers!

[63falcondude]

It would be better to see the desired results (which you can enter manually) rather than the non-working formula.

[maras_mak]

Test the array formula. Drag down.

Please Login or Register  to view this content.

Regards.

[jason.b75]

the formula would specifically exclude members who have been selected in previous selection(Column A). This would mean if one member has been selected in my previous selection, he would be skipped this time and the next member, is selected instead.

In your table, it appears as if every member is included in the 'Previous selection', so everyone should be excluded and the 'New selection' should be empty

[maras_mak]

It The best will be:

It would be better to see the desired results (which you can enter manually) rather than the non-working formula.

Regards.

[AliGW]

Maras - what does your last post mean? Please explain.

[Jonces]

I've attached updated version of the problem that includes the outcome desired.

[Jonces]

This doesn't appear to be working in my excel, it shows an error at $B$41;SMALL, and $B$41);"";ROW

[jason.b75]

That formula is in the wrong format, you would need to change the semicolons to commas.

Your second example is not showing up, I think you might have uploaded it but not attached it.

[maras_mak]

Jonces, sorry. Replace ';' to ','
Formula table

Please Login or Register  to view this content.

Regards.

[Jonces]

Oh, i missed it! please see attached now.

[Jonces]

Jonces, sorry. Replace ';' to ','
Formula table

Please Login or Register  to view this content.

Regards.

It doesn't seem to work, it shows up with an error - see attached

[jason.b75]

Try this one in A44 and fill down, Array confirmed with Shift Ctrl Enter

=INDEX($B$2:$B$41,MATCH(0,COUNTIF($A$2:A43,$B$2:$B$41),0))

[Jonces]

Try this one in A44 and fill down, Array confirmed with Shift Ctrl Enter

=INDEX($B$2:$B$41,MATCH(0,COUNTIF($A$2:A43,$B$2:$B$41),0))

Thanks Jason,

It works, thanks!

If you have a chance, would you please explain how this works?

If not ill try to find out it my self!

Cheers!!!!!!!1

[jason.b75]

For each balance listed in column B, it counts the occurences in column A above the row holding the formula, and looks for the firstone with a count of 0.

If you follow through the steps of the formula using the evaluation tool with this in mind it will probably be easier to follow.

As a thought, if it might be a potential problem. If the same balance appears twice in column B, it will only ever appear once in column A.

edit:- If you do need duplicate entries to be considered as individual then try maras formula instead. That will duplicate entries if they appear twice in column B, but only if the first match is in the new selection. A single match in the previous selection will still cause it to be omitted.

[maras_mak]

Hi Jonces,
my Array must be confirmed with Shift Ctrl Enter and then works well.
Regards.

[maras_mak]

Hi Jonces, Array must be confirmed with Ctrl Shift Enter and then works well.
Regards.

[Jonces]

Thank you for your help guys, will have to learn the formula and the set up!