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
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
Can you give more details about logic and show expected result?
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
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
Result is in qry1 (it is sorted and take time to be completed - 4782969 possible combinations)
DBtmp.accdb
ASKER
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?
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?
ASKER
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?
Would a report be the best way to do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. Thank you very much!
ASKER
This works great. Thanks for the quick help.