Find based on lowest occurance and condition, then ditribute evenly.

[drSwank]

Hello!
I've struggled with this one for a few days now.
I'm making a schedule based on day and time. I never have the same time twice, so it should be
only one condition needed for that.

I've tried using a INDEX-MATCH (see page2 of Excel-file) testing for the least occurence.
However it only matches if BOTH conditions are exaclty met on the first occurence. However,
in this case, I need to find at least one person availiable at the right time, and then pick the
one among those with the least occurences in the schedule. Trying this I only seem to get a
circular reference resulting in a 0.

I also tried a LOOKUP which only finds one value but not both. Page1 includes the actual schedule
I'm going to use. As you can see in the file it only picks "Kim" & "Wanda" all the time over and over
again.

Anyone have any ideas how to solve this?

testingSched.xlsx

[dilipandey]

Hello and welcome to the forum.

However it only matches if BOTH conditions are exaclty met on the first occurence.

Which both conditions... you are only checking MIN there ? suggest you to provide your expected results and the logic and I'll be happy to give a try. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[drSwank]

Thanks for taking the time. It's difficult to explain

This is what want to achieve. In "column K" I want to display the name in the list with the least occurencies. Meaning, I check to find the name with least occurences in column K, and when I find that one, I check if he/she is availiable at the time specified in Column J. If he/she is not availiable, find the one with second least occurences and see of he/she is availiable at the specified time. If he is, display it, if not. keep searching... Etc..

I tried

{=INDEX($A$2:$A$10;MATCH($J4;IF($F$3:$F$10=MIN($F$3:$F$10);$C$3:$C$10);0))}

but that'll only find one result and if it doesn't match on both conditions it fails. Also it will not work because of circular reference.

If not possible to do this with standard formulas, I'm willing to make a macro or custon function for it. But I'm not that great in VB...

I updated the file to see the condition i action.
testingSched2.xlsx

[dilipandey]

Hi drSwank,

- First, have the same naming convention in column i and row 2 for weekdays.
- I am not sure about this. Do you want only 4 days in week ?

=IF($J3="10.00","Wednesday",IF($J3="17.45","Wednesday",IF($J3="14.00","Monday",IF($J3="10.30","Saturday",""))))

- Now for K3, what name to be looked ? I believe its Anna being in similar row in A column. If so, then there is no name when we do K12. check this ?

Also suggest you to put manually what names should be there in column K and I'll try to obtain them using formulas. Thanks.



Regards,
DILIPandey
<click on below * if this helps>

[drSwank]

Thanks for trying to help! I solved the problem with a macro instead. THat seemed to be the only way not to geta circular reference and to sort the list based on to values or more.

[dilipandey]

Great... suggest you to mark this thread as [SOLVED] and please take a moment to provide your solution so that it can help others who may find this thread while searching the similar kind of solution..


Regards,
DILIPandey
<click on below * if this helps>

[drSwank]

I will, as soon as I get back from vacation