Generate different lists

%3CLINGO-SUB%20id%3D%22lingo-sub-1767649%22%20slang%3D%22en-US%22%3EGenerate%20different%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1767649%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%202%20separate%20lists.%20The%20number%20of%20objects%20in%20the%20list%20can%20vary%20or%20be%20the%20same.%20All%20Objects%20can%20be%20divided%20into%204%20categories.%20I%20have%20a%20set%20of%20constraints%20which%20need%20to%20be%20followed%20(%3CSTRONG%3EExcel%20Attached%3C%2FSTRONG%3E).%20I%20now%20want%20to%20generate%20a%20new%20set%20of%20lists%20(it%20can%20be%20any%20number%20of%20lists)%20of%2010%20objects%20from%20the%20above%20two%20lists%20based%20on%20the%20criteria%20%2F%20constraints.%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20added%20functionality%20which%20(if%20possible)%20is%20to%20filter%20the%20generated%20lists%20based%20on%20inclusion%2Fexclusion%20of%20certain%20objects.%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1767649%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1767928%22%20slang%3D%22en-US%22%3ERe%3A%20Generate%20different%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1767928%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F545848%22%20target%3D%22_blank%22%3E%40Jodhvir%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20description%20of%20what%20you're%20trying%20to%20do%2C%20I%20have%20to%20say%2C%20is%20one%20of%20the%20more%20cryptic%20I've%20seen.%20I'm%20sure%20it's%20totally%20clear%20in%20your%20own%20mind%2C%20but%20you'll%20need%20to%20do%20some%20more%20explaining....perhaps%20give%20an%20actual%20example%20of%20an%20outcome%20that%20you'd%20like%20to%20see.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20any%20event%2C%20let%20me%20ask%20if%20you're%20at%20all%20familiar%20with%20the%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%20function.%20It's%20only%20been%20fairly%20recently%20available%20with%20the%20newest%20versions%20of%20Excel.%20Based%20on%20your%20last%20paragraph%2C%20I%20think%20it%20might%20do%20what%20you%20want.%20Give%20it%20a%20try.%20If%20it%20doesn't%20work%2C%20by%20all%20means%20come%20back%20and%20clarify%20your%20request.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1769149%22%20slang%3D%22en-US%22%3ERe%3A%20Generate%20different%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1769149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F545848%22%20target%3D%22_blank%22%3E%40Jodhvir%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20spelling%20out%20the%20possible%20circumstances--was%20that%20real%2C%20or%20still%20a%20parallel%20or%20hypothetical%20example%3F--but%20what%20I'm%20also%20looking%20for%20(as%20the%20example%20I%20requested)%20is%20that%20you%20take%20your%20spreadsheet%20with%20its%20very%20abstract%20data%2C%20and%20give%20an%20example%20of%20an%20outcome%2C%20stating%20the%20criteria%20you%20use%20to%20get%20there.%20I'm%20not%20asking%20for%20the%20Excel%20solution%20(that's%20what%20YOU%20are%20asking%20for)%2C%20but%20rather%20for%20a%20verbal%20description%20of%20the%20actual%20outcome%20possible%20given%20certain%20selection%20criteria.%20T%5C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20short%2C%20talk%20me%20(us)%20through%20the%20process%20verbally.%20You've%20set%20the%20stage%3B%20let's%20play%20out%20the%20drama.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1781952%22%20slang%3D%22en-US%22%3ERe%3A%20Generate%20different%20lists%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1781952%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F545848%22%20target%3D%22_blank%22%3E%40Jodhvir%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20that%20SOLVER%20can%20do%20this.%20But%20I'd%20have%20to%20just%20go%20in%20and%20play%20around%20with%20it%20to%20figure%20out%20how.%20I've%20used%20it%20maybe%20twice%20in%20my%20life%3B%20the%20kinds%20of%20situations%20I%20deal%20with%20generally%20don't%20require%20that%20tool.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20guess%2C%20from%20what%20you've%20said%2C%20is%20that%20you%20could%20do%20that--go%20in%20and%20play%20with%20SOLVER%20yourself--just%20as%20well%20as%20I%20could.%20Give%20it%20a%20try.%20Come%20back%20and%20tell%20us%20how%20or%20IF%20it%20has%20worked.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I have 2 separate lists. The number of objects in the list can vary or be the same. All Objects can be divided into 4 categories. I have a set of constraints which need to be followed (Excel Attached). I now want to generate a new set of lists (it can be any number of lists) of 10 objects from the above two lists based on the criteria / constraints. 

An added functionality which (if possible) is to filter the generated lists based on inclusion/exclusion of certain objects.

Thank you.

 

7 Replies
Highlighted

@Jodhvir 

 

Your description of what you're trying to do, I have to say, is one of the more cryptic I've seen. I'm sure it's totally clear in your own mind, but you'll need to do some more explaining....perhaps give an actual example of an outcome that you'd like to see.

 

In any event, let me ask if you're at all familiar with the FILTER function. It's only been fairly recently available with the newest versions of Excel. Based on your last paragraph, I think it might do what you want. Give it a try. If it doesn't work, by all means come back and clarify your request.

Highlighted
Thank you sir for your reply. I apologize if my explanation and the excel attached are not too clear. If 1 may give an example of the end result it would be something like this..
There are 2 classrooms. Both can have different/same number of students. Now the students can have various attributes like smart, tall, athletic, boy, girl etc. Suppose the school wants to take a team 10 students from these two classes to a multi-staged competition (a competition which will involve writing tests, sports tests, general knowledge etc) in another city so they have to select students from these two classes based on their attributes. What are the possible combinations of the team/teams that can be formed which fulfill all the basic criteria .
Once all possible combinations are formed, the school has to filter down the lists based on their certain preferences (like student will A definitely go, student B will definitely not go etc).
Hope i am able to give a better explanation this time around.
Thank you once again.
Highlighted

@Jodhvir 

 

Thank you for spelling out the possible circumstances--was that real, or still a parallel or hypothetical example?--but what I'm also looking for (as the example I requested) is that you take your spreadsheet with its very abstract data, and give an example of an outcome, stating the criteria you use to get there. I'm not asking for the Excel solution (that's what YOU are asking for), but rather for a verbal description of the actual outcome possible given certain selection criteria.

 

In short, talk me (us) through the process verbally. You've set the stage; let's play out the drama.

Highlighted

@mathetes 

Hello Sir.
Apologies for the late reply. Actually this scenario is one of the many that i can think of. I wanted to know if the solver tool / or its some advanced version can solve these kinds of problems which we solve in engineering. I have made a rough excel about the end result i imagine. Hope this would explain in a better way what i imagine the output can be. I would like to reiterate that there can be N number of teams. I have just made 3 as an example. 
And a big thank you once again sir for showing such enthusiasm to this abstract question. 

Highlighted

@Jodhvir 

 

I suspect that SOLVER can do this. But I'd have to just go in and play around with it to figure out how. I've used it maybe twice in my life; the kinds of situations I deal with generally don't require that tool.

 

My guess, from what you've said, is that you could do that--go in and play with SOLVER yourself--just as well as I could. Give it a try. Come back and tell us how or IF it has worked.

Highlighted
I tried playing around with Solver / Goal Seek tools as you suggested sir, but to no fruition. I am now trying to do the same with Power Query. It seems possible and may take me some time to arrive at the final results. Could you have a look at the problem from the Power Query point of view? Thank you.
Highlighted

@Jodhvir 

 

To my knowledge, my (Mac) systems do not have Power Query. I've never been able to activate it, at any rate.

 

In my (career) background I have worked fairly extensively with SQL (Structured Query Language) queries on mainframe databases, and I have been given the impression that Power Query bears a lot of resemblance to that process. As such, I doubt that it would be suitable to your particular challenge. It's great for joining various tables in a relational database context, to produce focused and comprehensive  outputs, but it's not really like Solver in being given conditions and lists and asked to come up with various solutions that meet those conditions.

 

I'd welcome others here to contribute to helping you. Right now I don't have the time to play with Solver myself, but I would persist. Have you tried with a less demanding set of conditions, just to get the procedure under your belt?