# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Can you give more details about logic and show expected result?
0
Author 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
Commented:
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
Author 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
Author 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
Commented:
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

Experts Exchange Solution brought to you by