Combination of two iterative formula

Hi all,

Been having some problems with getting the results I want from the iterations.
So I have some names in Array 1 which correspond to a given number, and Cell M7:M11 will display the first name that doesn't appear within Array 2.
In Cells M14:17 I have a formula which then looks to see whether the name in cells M8:11 has already been used, and if so, to move to the next name on the list.
This is where I've hit the speed bump. I have been able to have it move along one column, but without manually typing out the iteration, can't get it to move any further.
Any help would be greatly appreciated.

Thanks,

Max

EDIT: Removed VBA from file, hoping should remove any issues.

Have you asked this question on any other forums? (e.g. Reddit)

If so, please follow the forum rule on cross-posting: https://www.excelforum.com/forum-rul...rum-rules.html

I have posted on reddit, tried to update but am not allowed to post links.

Hi, not sure if I fully understand but it seems your precedence logic is relatively convoluted.

If you can add a key, of sorts, it will help - I think...

Using your sample file:

Formula:

Please Login or Register  to view this content.

the results can then leverage the above, e.g.

Formula:

Please Login or Register  to view this content.

if the above isn't what you meant, or you cannot use a key (as outlined) post back ... but... if the logic is as complex as your desired results imply then this may get a bit convoluted.

edit: added attachment to illustrate above -- my results in M (i.e. adjacent to your expected results)... if you add to array 2 the results stay in sync, so I think it apes your logic (just not sure how useful)

so, in my file you will see Stephen listed twice (rows 15 & 18), so whilst top 5 is ok things go awry thereafter

reason for the above is the key - given this is looking above (only) and not below and left... so key in U8 should be 0, but isn't.

questions for you

1. if Craig were added to Array 2 what would your expected outcome be

2. just to confirm, order of precedence is: pick first valid person per row then, having iterated all rows, pick 2nd valid person per row if available etc...

question 2 is key - if you don't care about the precedence so much, i.e. just want 5 unique people, it's a lot (lot) simpler.

Hi,

Not sure I've fully understood, but perhaps this array formula** in N13:

=IFERROR(INDIRECT(TEXT(MMULT(10^{5,0},0+MID(TEXT(MIN(IF(C$2:I$6<>"",IF(COUNTIF(C$11:C$16,C$2:I$6)=0,IF(COUNTIF(N$12:N12,C$2:I$6)=0,10^5*COLUMN(C$2:I$6)+ROW(C$2:I$6))))),"R00000C00000"),{8;2},5)),"R00000C00000"),0),"")

https://excelxor.com/2014/08/22/adva...nd-discussion/

Regards

Originally Posted by Valont

Unfortunately not, in the instance that no other names would be available, the wanted response would be to move Trevor onto that job, but as Joanne hasn't been trained in job 4, there's no instance in which she can be assigned to it.

OK. But, in above scenario (with both Craig & Thomas in Array 2) could you assign Trevor to Job 4 given fact Trevor is first to be assigned to Job 2 ?

The reason I ask is that because if you need to somehow determine the "optimal mix" I'm not sure this is feasible.

To elaborate; with those names in Array 2 you may decide that the 'optimal' approach would be to assign Fred, rather than Trevor, to Job 2 such that Trevor can do Job 4 which would otherwise be empty, the only other Job Fred is trained on is Job 5 and Joanne would be 'first cab off the rank' for that.

If you take it a step further and then add Joanne to Array 2 it gets a whole lot more complicated again as Fred would now be first choice for Job 5 etc...

If, conversely, you wish only to identify the first valid person for each job, mindful of those who may have already been assigned to other "earlier" jobs, then:

N13:
=IFERROR(INDEX($C2:$I2,MATCH(1,INDEX(($C2:$I2<>"")*ISNA(MATCH($C2:$I2,$C$11:$C$16,0))*ISNA(MATCH($C2:$I2,N$12:N12,0)),0),0)),"")
copied down to N17 (i.e. Jobs 1-5)

but, seems over simplified ?

edit: and thanks XOR LX ... nice!

It would seem to me that things would be far more straightforward given a re-uploaded workbook with several permutations of names/exclusions together with expected results. As it stands, there are a dozen or so posts going back and forth in an attempt to establish the logic for various scenarios.

Regards

Thanks a lot. Now I understand!

Will take a look.

Regards

I need helper column

B11
=INDEX(C$11:C$15,MATCH(ROWS(B$11:B11),MOD(SMALL(($A$2:$A$6-MMULT(COUNTIF(A$11:A$15,$C$2:$I$6),{1;1;1;1;1;1;1}))*100+$B$2:$B$6,$B$2:$B$6),100),))

C11 helper

Formula:

Please Login or Register  to view this content.

This extra column brings back the right names but not in the correct place unfortunately

That's why I call it helper column.

And then use the formula in B11 to get the name in correct place.

Oh it goes right to left, I'm with you, my mistake!
That's interesting ok.
Let me see if I can get that to work!

Just tested it a little and have an undesirable response come back.
I added Craig to the first list and it changed Trevor.
Have updated my original expected book, attached here, to show the desired response.

Re your expected results, can you just clarify why you can't have Trevor as second pick in your 3rd and 4th examples (i.e. cells H12 and K12)?

Regards

Trevor isn't an appropriate response in H12 and K12 because he has to be in H14 and K14, as both of the other employees are unavailable for job 4, and he is the only name left. Therefore it's ok to move him from his normal job so job 4 isn't left unassigned.
However, he can stay in E12 because there is another staff member available for job 4 (E14) in the second example.
Hope that clears things up a little?

Please try again

B11
=INDEX(C$11:C$15,MATCH(ROWS(B$11:B11),MOD(SMALL((TEXT($A$2:$A$6-MMULT(COUNTIF(A$11:A$15,$C$2:$I$6),{1;1;1;1;1;1;1}),"[>1]"&$B$2:$B$6&";\0"))*100+$B$2:$B$6,$B$2:$B$6),100),))

C11 Helper column

Formula:

Please Login or Register  to view this content.

Originally Posted by valont

If you put just john's name in the exceptions, stephen moves from job 3 (when he shouldn't)

Re the above, if Stephen is to stay in Job 3, that forces Joanne to go into Job 1, no? Which means that she has been forced out of Job 5. Is that preferable to Stephen being moved and, if so, why?

Apologies if I'm not fully grasping the logic here.

Regards

Also, I just went back to check your original file to see if there was anything I'd missed. And then I noticed the TEXTJOIN function in there.

Your profile says Excel 2010, yet TEXTJOIN is an Office 365/Excel 2019 function. Can you clarify? This could make a potentially significant difference to proposed solutions.

Regards

So this has to work for 2010?

Regards

Still looking into the original problem. Hopefully have something back for you this evening.

Regards

Maybe try this

B11 DESIRED
=INDEX(C$11:C$15,MATCH(ROWS(B$11:B11),MOD(SMALL(FIND(1,MMULT(1-(COUNTIF(A$11:A$15,$C$2:$I$6)),TRANSPOSE(10^-COLUMN($C$2:$I$6))))*1000+$B$2:$B$6,$B$2:$B$6),1000),))

C11 helper

Formula:

Please Login or Register  to view this content.

Hi,

I believe I'm close to a final solution. However, would it be possible to have perhaps half a dozen or so more scenarios with expected results for testing? As varied and comprehensive as you can, if you could.

I'm referring to the original problem.

Regards

Thanks, but unfortunately that seems to contain identical examples to those in the workbook you supplied in post #23.

Regards

Array formula** in B11:

=IF(SUM(N((C2:I2<>"")-COUNTIF(A$11:A$15,C2:I2)=1))=1,T(INDEX(C2:I2,MATCH(1,(COUNTIF(B$10:B10,C2:I2)=0)*(COUNTIF(A$11:A$15,C2:I2)=0),0))),LOOKUP("Ω",T(INDEX(C2:I2,N(IF(1,MATCH({1,9},8*(C2:I2<>"")*(COUNTIFS(C$2:C$6,C2:I2,C$2:C$6,"<>"&C2)=0)+(COUNTIF(B$10:B10,C2:I2)=0)*(COUNTIF(A$11:A$15,C2:I2)=0)*(ISNA(MATCH(C2:I2,T(INDIRECT(TEXT(MODE.MULT(IF(IF(MMULT((C$2:I$6<>"")-COUNTIF(A$11:A$15,C$2:I$6),TRANSPOSE(COLUMN(C$2:I$6)^0))=1,COUNTIF(A$11:A$15,C$2:I$6))=0,10^5*ROW(C$2:I$6)+COLUMN(C$2:I$6)),10^5*ROW(C$2:I$6)+COLUMN(C$2:I$6)),"R0C00000"),0)),0))),0)))))))

and copied down as required.

Any blank cells within ARRAY 1 must be 'genuinely' empty, i.e. not contain the null string (""), e.g. as a result of formulas in those cells. With some additional work, I could amend it so that it works in those cases as well, if required, though I presumed that these were manually-entered values and so null strings would not be present.

Of the examples in your last file, my results concur with yours for all apart from those in D19:E23, for which JOANNE appears twice in ARRAY2, and those in M19:N23, though due perhaps only to a difference in preference: you move JOANNE and keep STEPHEN, I move STEPHEN and keep JOANNE.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Originally Posted by Valont

H11:15 should read either
Stephen, John, Andrew, Trevor, Joanne
OR
Joanne, John, Stephen, Trevor, Tony

Can you clarify why these are preferable to the output I generate of John, Fred, Stephen, Trevor, Joanne? All 3 options retain 4 of the 5 original entries in the first column.

Regards

Ok, thanks. Back to the drawing board on that one...

Hi,

Unfortunately your last example has made me aware of the full scope of the problem you are hoping to solve, and I do not believe that it is solvable using Excel formulas alone. Perhaps something could be achieved with VBA, looping through various scenarios and assigning an 'optimised' score to each until the necessary conditions are met.

And that - a problem in optimisation - is what I realised you're facing here after looking at the counter-example you gave in your last message. It's also what XLent seemed to grasp way before I did, to his/her credit.

The only way this can be feasibly approached using Excel formulas alone is to build a set of hierarchical logic steps to follow. Which is what myself and the other two posters have attempted. However, without some form of iteration (of which VBA looping could be considered an example), scenarios such as your last are virtually impossible to resolve: in order to know that John cannot be picked for the first row, we must see that Trevor is the only pick for the fourth row, which implies that John is the only possible pick for the the second row...

And this 'three-level' case is, I imagine, only a relatively simple example of the recursive logic inherent here. As the size of the dataset increases, I would imagine the number of permutations necessary to examine becoming exponentially large.

I have worked on optimisation problems in the past, sometimes thrashing them out in the worksheet initially, then moving onto VBA, before finally realising that Excel is just not the right tool. I hope I'm wrong in this case - it might be worth re-posting in the VBA section of the forum to see if anyone takes it up there. In any case, best of luck with it!

Regards

So is VBA something you'd consider? Would be happy to try to write some code myself if that's a route you'd like to go down.

Ah, English beer! Now that is one thing I definitely miss!

Originally Posted by Valont

I'll upload another sheet with some examples below for the simple sheet.

Ok, I'll wait for that and see if it looks solvable using worksheet formulas alone. If not, we'll look at some VBA.

Originally Posted by Valont

And yet all of the Brits want foreign beer!

Indeed!

Originally Posted by Valont

Hopefully this will be nice and simple!

Perhaps, but re:

Originally Posted by Valont

Would it be significantly easier if we made it so that the main drivers never move? So for example, if John were unavailable, it would come back with a 0 and leave both Stephen and Joanne in their 'preferred' jobs?

can you clarify your expected results in the new file?

Who are the "main drivers"?

Why, in your examples, do John and Craig return blanks in your 1st and 3rd examples respectively, but Trevor (in the 2nd and 4th examples) and Craig (in the 4th example) do not?

And why are the expected results for the 5th example all blank?

Regards

Thanks. Understood.

Need at least a dozen more scenarios for testing purposes, if you could.

Many thanks.

For example, what would you now expect the result to be for the previously problematic ARRAY 2 of Craig, Thomas, Fred?

Regards

Thanks a lot. Can you just double-check your expected results for 2 of those scenarios, i.e.

JOHN
CRAIG
JOANNE

and

STEPHEN
JOANNE
FRED

For the former I don't understand why you have ANDREW in the 3rd row and not STEPHEN. For the latter I don't understand why you have FRED in the 2nd row and not TREVOR.

Regards

Hi,

Unfortunately, this change does nothing to resolve the issue which caused my previous solution to fail, i.e. we can always construct scenarios such as this:

ARRAY 1

JOHN	CRAIG
JABRONY	ANDREW	THOMAS	TONY	JIM	TREVOR	STEPHEN
FRED	THOMAS	JIM	JOHN
STEPHEN
CRAIG	ANDREW

ARRAY 2

THOMAS

JABRONY

TONY

FRED

CRAIG

Here the best I can achieve is:

JOHN
JIM

STEPHEN
ANDREW

though of course you would expect:

JOHN
TREVOR
JIM
STEPHEN
ANDREW

Will have an initial look at some possible VBA set-ups.

Regards

Sorry, not sure what you mean. Can you clarify with reference to the example I posted?

Let me reiterate the issue with that example as I see it with respect to any formula-based approach:

ARRAY 1

JOHN	CRAIG
JABRONY	ANDREW	THOMAS	TONY	JIM	TREVOR	STEPHEN
FRED	THOMAS	JIM	JOHN
STEPHEN
CRAIG	ANDREW

ARRAY 2

THOMAS

JABRONY

TONY

FRED

CRAIG

Row 1 is obvious, so let's move to row 2:

JABRONY cannot be picked. (He's in ARRAY2)
ANDREW cannot be picked. (Only choice for row 5, since CRAIG is in ARRAY2)
THOMAS cannot be picked. (He's in ARRAY2)
TONY cannot be picked. (He's in ARRAY2)

which seemingly leads to JIM being a valid choice: he's not in ARRAY2, he doesn't occupy one of the current 'leader' positions, and he wasn't our choice for row 1.

However, as we have seen, this leads to row 3 being blank.

The problem is that, if only we had the foresight (in technical terms, if we were to see what the results in following rows would be if JIM were chosen), we might reject him in favour of TREVOR or STEPHEN.

But this then leads to the conclusion that the only feasible approach would be to analyse all permutations of people for any rows which are not immediately resolvable, and seeing which of those permutations satisfies the criteria.

How, for example, would your own formula-based approach seek to reconcile this problematic case?

Regards

If I've understood you correctly, you're saying that the solution I've devised which generates the output for the case we've been discussing, although sub-optimal, would be of use to you right now, at least whilst I look into potentially superior VBA set-ups?

If so, happy to give you that solution. If not, apologies if I've misunderstood.

Regards

This version also works with null strings ("") in ARRAY 1. In B11, array formula**:

=IF(SUM(N((C2:I2<>"")-COUNTIF(A$11:A$15,C2:I2)=1))=1,IFERROR(T(INDEX(C2:I2,MATCH(1,(COUNTIFS(C$2:C$6,C2:I2,C$2:C$6,"<>"&C2)=0)*(COUNTIF(B$10:B10,C2:I2)=0)*IF(C2:I2<>"",COUNTIF(A$11:A$15,C2:I2)=0,1),0))),""),IFERROR(T(INDEX(C2:I2,MATCH(1,(COUNTIFS(C$2:C$6,C2:I2,C$2:C$6,"<>"&C2)=0)*(COUNTIF(B$10:B10,C2:I2)=0)*IF(C2:I2<>"",COUNTIF(A$11:A$15,C2:I2)=0,1)*(ISNA(MATCH(C2:I2,T(INDIRECT(TEXT(MODE.MULT(IF(COUNTIF(C$2:C$6,C$2:I$6)=0,IF(IF(MMULT((C$2:I$6<>"")-IF(C$2:I$6<>"",COUNTIF(A$11:A$15,C$2:I$6),0),TRANSPOSE(COLUMN(C$2:I$6)^0))=1,IF(C$2:I$6<>"",COUNTIF(A$11:A$15,C$2:I$6),0))=0,10^5*ROW(C$2:I$6)+COLUMN(C$2:I$6))),10^5*ROW(C$2:I$6)+COLUMN(C$2:I$6)),"R0C00000"),0)),0))),0))),""))

Regards

Originally Posted by Valont

So I'm having trouble getting the formula to work within the actual workbook.

In what sense is it not working? Errors? If so, what type? Or just unexpected results?

Have you remembered to commit it as an array formula**?

Originally Posted by Valont

I'm assuming the problem is working across sheets within the same workbook?

Sorry, what precisely do you mean by this?

Originally Posted by Valont

Either that or that I'm using named arrays instead of C$3:AA$3 for example.

No, that's a perfectly good idea.

Regards

P.S. VBA's coming along, if slowly. Will let you know as soon as I have something finalised.

Have you resolved the issue?

Regards

Sorry I didn't get back to you yesterday.
Sadly not, I managed to get the first name back in some cases, but when adding names to the exceptions, nothing changes.
Will have a look around today, see if I can work something out.

EDIT
Have now updated the cells to match (small mistake on my part) and it has stopped bringing back names altogether. Just bringing back 0's now.
Head-scratcher for sure.

Perhaps if you attach a small, dummy workbook which illustrates the issue you're having I'll be able to help.

Regards

FYI VBA going well, if slowly. Should have something for you this weekend.

Regards

What size are ARRAY1 and ARRAY2 in actuality?

Regards

Ok, I think the full set-up might present too many permutations for Excel to handle. If the reduced, group-based version is an option for you, please clarify an upper limit on the dimensions (rows and columns) per group for ARRAY1 and ARRAY2. Any crossover would have to be handled separately.

Regards

Ok, now working on Plan C: a hybrid formula/VBA set-up.

Regards

Originally Posted by Valont

There are 140 names and 120 different jobs.

Can you just clarify the above? ARRAY1 in your original examples was 5 names and 7 jobs. Are you saying that the range to consider now has to extend across up to 120 columns? Or is a given person never allocated more than a set number of jobs?

If it must be up to 120 columns, how did you get along with implementing my formula? I would imagine that one instance of the formula I provided would take quite a long time to calculate over a 120x140 range (i.e. 16,800 cells), never mind 140 instances of that formula.

Still, it would get there in the end, I believe. I just want to understand that these dimensions are correct before I start testing over such ranges myself.

Regards

Apologies. I've tried everything I can, though I can't generate a solution which will guarantee correct results for a dataset of the dimensions you have.

I have managed to create a VBA set-up which always works, yet only for datasets significantly smaller than that which you have. The hybrid formula/VBA set-up led to some interesting attempts, though in the end the stumbling block for all solutions I've thus far attempted is that, despite there being several criteria in place which help to reduce the required computations, with a dataset in the region of 140x120, you are always left with an astronomical number of permutations to calculate (for example, even assuming fully one half of the 140 rows can be disregarded by virtue of the known criteria, for the remaining 70 rows, if on average just 2 of the people are eligible choices for each of those rows, that still necessitates computing 2^70 (one sextillion!) permutations in order to guarantee success.

Of course, the original formula set-up I gave you will work fine over such large ranges, but then that set-up is not looking at all permutations and, as such, although it may well offer an 80% success rate (or even higher, perhaps), it can never be guaranteed to produce 100% correct results.

I hope that you have better luck than I have and all the best with a very interesting project!

Regards

The attached file contains both formula results (column C) and, once run via clicking the target icon, VBA results (column D).

The macro brings in randomly generated data for ARRAY1 and ARRAY2 from the Random Entries Generator tab. If this is not desired then go into the VBA window and remove the line StringsforTesting from Sub Main().

Conditional Formatting has been applied to the output range to aid inspection of results. Also, several defined names have been created within Name Manager which apply to various ranges within the workbook and also to subsections of the main formula.

Regards

Noticed small error in previous file. Attached v2.

Regards

And here's a 15-column-by-140-row version.

Whether this takes 30 seconds to run or 3 days all depends upon the particulars of your data.

Regards