Lookup based on multiple criteria including a greater / less than condition

[Zee1984]

Hi,

This is my very first post on this. I'm an above average Excel user. I have a list of test references in column A (A1-A2000). I also have an interaction table listing the outcomes of interaction between each of the tests with each other (2000 x 2000 matrix). I have attached 2 sample files with a reduced list of entries (10 test references and a 10 x 10 matrix).

I am trying to maximize the up time of lab equipment. After running one test, say A1, which starts at 5:00 and ends at 5:15, I want the excel sheet to find the next possible test based on 2 conditions.

Condition 1 - The start time of the new experiment should be after the end time of the current experiment (A1 is the current experiment which ends at 5 :15)

Condition 2 - The 2 way lookup result of A1 and the next possible test in the matrix should be equal to or less than 7.

In this instance A2 was selected.

Now the next possible test after A2 is to be assigned, which is A4.

Condition 3
Once selected as the next test, that test should not be picked up in the following rows.


I have tried an approach with INDEX and Match using multiple criteria and a decreasing range, which did not work.

I am looking to create something without using VBA.

Can somebody help me with this?


Thanks
Zee

 

[vletm]

Zee1984
Could You check few details before start to do more?
1) A2's End Time < Start Time ... is it matter or not?
2) D2:D6 ... and my sample chart in file?
If Start and End Times are fixed,
> Could A5 do while A4 is still running?
> how A10 could do before A7?
> why not order eg A1 > A2? > A3 > A5 > A9 > A7 > A8 > A10?
(A2 is red because ... backwards)

... and why not with VBA?

 

[Zee1984]

Hi Vletm,

First of all thank you for responding. I do agree I was a little impatient. I apologize for it once again.

1) You were right to point out the mistake in A2's end time. I had made a manual sample with some manual entries to illustrate my problem. I am not permitted to actually share the original file and hence the need to create a different file.

I have attached an amended file.

2) My organization's goal is to maintain the optimum/ minimum possible inventory on test equipment. The idea behind the project is to ensure that no test is missed, all tests conducted on time and with the minimum possible test equipment. Wherever possible, I'll have to reuse equipment without sacrificing another test. If reusing equipment is not possible, then we look at getting another set of equipment.

Your chart is very close to the output I'm looking for. But the only difference being I need to roll-up tests and assign them to individual test equipment sets without overlaps.So based on your graph I can technically roll - up A1, A2, A3, A5,A9,A7,A8 and A10.

The only exceptions being A4 and A6 which would overlap with the first set and each other. This would mean that I can run all tests in set 1 with one set of equipment, but I will need two separate sets for A4 and A6 (as they also will overlap).

However, it is still a major win for me if I can demonstrate that you may not need 7-8 sets of equipment as it is the case right now.


As an offshoot, Could you help me in setting up something that would eventually roll up everything and help in assigning the test equipment sets?

> how A10 could do before A7?
Maybe the way I manually calculated was wrong. But the solutions should be picked based on the rules I've described in my initial problem statement.

I have amended the sheet (removed all possible answers in column D)

> why not order eg A1 > A2? > A3 > A5 > A9 > A7 > A8 > A10?
(A2 is red because ... backwards)

My end goal is to build a rolled up schedule for the equipment. Which is in effect a sequence as you've stated. Can this be done for a huge data set? My sheet will have at least 2000 entries on average and sometimes max out at 4000 entries. The operational team is considering to add more conditions (which is another worry).

... and why not with VBA?
I want to use VBA to a minimum as I feel that it will take too long to churn out the results for large data sets. But I have not completely rules it out either. If more conditions are to be added as constraints then I see VBA as the easier option.

Finally, thank you once again for the response.

 

[vletm]

Zee1984
Have You explained term 'test set' in this case?
I also would use same 'test set' all the time, if possible.
Many things are possible, but I need clear rules,
eg if that '10x10' has some connection with 'test sets',
even maybe with those conditions 2 & 3.
I could help to set up something after I know enough details to figure Your needs.
How would this take care 'skipped' 'references'; my way, those A4 & A6?
PS1. VBA and feeling won't have any connection to each others; I would use VBA.
PS2. How long would be too long?

 

[Zee1984]

Hi Vletm,

I currently do not have an inventory list of test equipment sets created. The excel would eventually tell me the optimum number of test equipment sets required. For now I'd be happy if the tests are sequenced into one line; which can then be assigned to a value called TE1 and so on.
We currently have 5 different types of test equipment. Each type can have any number of test equipment sets. This example is for one type of test equipment only.

So the fully qualified reference of TE1 will be Type1- TE1, Type1-TE2 and so on.

It is like saying I have 2 types of cutlery - forks and chopsticks (Test equipment type) . Assume that chopsticks are reusable.
Chopsticks can only be used for chinese food. Chinese food can be many (tests)

This set of tests A1...A10 is the equivalent of Chinese food varieties. These can only be used with chopsticks. I can get any number of sets of chopsticks. I call the first chopstick set TE1. The second TE2 and so on.Right now I don't have an inventory of chopsticks. My inventory will be determined by the peak number of chinese dishes I have to serve.
Forgive me for the confusing analogy.

I was planning to do the sequencing for each set type (chopsticks and spoons) individually. Determining the test set type is also governed by some rules. I have already segregated the test types and the respective tests.

Do you think your approach will make the model more efficient? In that case we'll have to introduce one more condition to check the test and categorize.

The 10x10 I've described is for one type of test type category, say Type1. Each category will have it's own matrix. Eventually ill have 5 files with one matrix each for individually scheduling/ sequencing each of the test set types.

The skipped ones A4 and A6 cannot be rescheduled (this is another constraint). So I will have to run those tests simultaneously through other test equipment (of the same type). In our example TE2 and TE3 will be assigned. And the total number of test equipment I'll need to ensure smooth operation will be 3. These 3 will be of the same type (Type1-TE1, Type1-TE2, Type1-TE3).

Maybe VBA is the way to do this. I am not totally against it

As for the duration for running VBA code, up to 1 hour would be acceptable. Anything beyond that puts us on a very reactive stance from a business perspective.

I hope i have given you enough info.

Thanks again for your time

Cheers
Zee

 

[vletm]

Zee1984
Have You explained term 'test set' in this case?
> Yes ... and no. Yes, there that part You wrote.
( ps. I uses other country 'base' chopsticks , not challenge )
but ...
Where is 'test set' in that file?
The file has A1..A10 with term 'Reference' - used with term 'experiment'.
There is matrix with 'marks' A1..A10, which You use term 'test' and
'type of test type category' - hmm?
I should know which is which and please use same terms later.

How would this take care 'skipped' 'references'; my way, those A4 & A6?
hmm.. cannot ... hmm?
But if eg start from A4 (skip A1..A3). There could be another combination.
> I am trying to maximize the up time of lab equipment. <
= no matter how many 'tests' ... usage time matters ... hmm?

You've used to use 'Chinese Food', Chopsticks, Forks ...
... those are Okay ... if later You can 'only' change those to real ones
I am not permitted to actually share the original file and hence the need to create a different file.
Means ... even Sample should be as close as possible with real world!
I do not need to know that kind of details,
eg I need to figure that eg ChopStick is A1 or where ever.
how large space something needs A1...A2000, but how wide?
and so on.

I should get clear image of everything which would be part of this.
There would be challenge to add later any details.
But the 1st, is to clear terms - that we would write about same thing.

I'll make questions,
which would help You to solve Your challenge
(some use term 'problem', no me)

 

[Zee1984]

Hi vletm
i have attached a new file to make it easier. I was initially trying to create a lookup with multiple conditions where condition 1 was to check the end time of the current test (ex. A1) against the start time of every test listed in column (excluding the current one. so check end time of A1 against start time of A2, A3,A4,A5,A6,A7,A8,A9 and A10) - Maybe it is an array formula that was required to be created. I'm not sure how.

The lookup will also check the intersecting value of the tests (that meet condition 1- All 9 tests meet condition 1) with the current test in the test interaction matrix and pick the one with the smallest value of interaction.

Is it clearer now?

Thanks

Regards
Zee