Permutations of Data Set in Excel

I need to determine all permutations of a data set organized like this please:

                        Place 1     Place 2       .... through ...         Place 14
Row 1      Amount A          Amount B                                Amount C
Row 2      Amount D          Amount D                                Amount E
Row3       Amount F          Amount G                                Amount H

Where the output has one 'Amount' from each Place, but not necessarily each Row.  

(e.g. A result could have the Row 1 'Amount' for Place 1-13, and Row 2 'Amount' for Place 14, with no result from Row 3)

See attached for example data set to work with.

I'm comfortable importing/using Macros, just not very good at writing them.

If it would be better to use Access, please advise on a proposed solution for that as well please.

Thank you!
Example.xlsx
TouteRougeAsked:
Who is Participating?
 
als315Commented:
Permutations quantity is calculated as 3^14 (3 row, 14 columns).
You can add sum of columns to query and citeria (700000 in example).

DBtmp.accdb
0
 
als315Commented:
Can you give more details about logic and show expected result?
0
 
TouteRougeAuthor Commented:
I would like to be able to compare every possible permutation of numbers from each column.  The result does not require a number from each row.

Please see attachment for example of result format.

Thanks!
Example---incl-output-format.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
als315Commented:
This is Access with imported table from excel.
Result is in qry1 (it is sorted and take time to be completed - 4782969 possible combinations)

DBtmp.accdb
0
 
TouteRougeAuthor Commented:
This is fantastic!  Thank you.

I didn't realize there would be so many results.  Is there a way to include in the query a parameter for the sum of the rows to be less than or equal to a certain sum, and only display those results?
0
 
TouteRougeAuthor Commented:
Let me clarify that question.  Is there a way to sum the rows of results, compare that with a defined value, and only show the rows of results that have a sum less than or equal to the defined value?

Would a report be the best way to do this?
0
 
TouteRougeAuthor Commented:
Perfect.  Thank you very much!
0
 
TouteRougeAuthor Commented:
This works great.  Thanks for the quick help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.