Link to home
Start Free TrialLog in
Avatar of TouteRouge
TouteRouge

asked on

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
Avatar of als315
als315
Flag of Russian Federation image

Can you give more details about logic and show expected result?
Avatar of TouteRouge
TouteRouge

ASKER

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
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
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?
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?
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect.  Thank you very much!
This works great.  Thanks for the quick help.