- Joined
- Dec 16, 2012
- Messages
- 2,319
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
In the attached is a Power Query solution to getting all the combinations of 6 columns of numbers.
The original problem is from msg#27 at http://www.vbaexpress.com/forum/sho...s-(no-repeats)&p=407983&viewfull=1#post407983
To keep the file small I've deleted the results which will need refreshing; it takes several minutes (more than 10).
[I realise the results don't fit on the sheet, and it's easy enough to split that table which I'll do later]
My questions:
1. Will this benefit from the likes of Table.Buffer statements? If so where should they be?
2. The code I've used may be awful; is there a better way?
For cross checking, there is a macro solution included which takes about 5 seconds to put the 6.25 million results into an array, and a further 40 secs to write them to a sheet.
Ancillary question: Is there a way to get a vba-created array into Power Query without writing to the sheet first?
The original problem is from msg#27 at http://www.vbaexpress.com/forum/sho...s-(no-repeats)&p=407983&viewfull=1#post407983
To keep the file small I've deleted the results which will need refreshing; it takes several minutes (more than 10).
[I realise the results don't fit on the sheet, and it's easy enough to split that table which I'll do later]
My questions:
1. Will this benefit from the likes of Table.Buffer statements? If so where should they be?
2. The code I've used may be awful; is there a better way?
For cross checking, there is a macro solution included which takes about 5 seconds to put the 6.25 million results into an array, and a further 40 secs to write them to a sheet.
Ancillary question: Is there a way to get a vba-created array into Power Query without writing to the sheet first?
Attachments
Last edited: