Spy Journal 3.0 Filtering the web for you

I posted an article about Permutations in Excel once before, back in 2005. I actually made a mistake in my example, and was corrected by a reader in a comment- thanks MathBoss.

I have since had another reader, Dam ask a question that I am not sure of the answer of. So I am opening it up to you Excel experts out there.

Here is the question as Dan wrote it.

How in Excel can I create ALL permutations of a given data set. for example, imagine 5 columns. Each column has a differnet (sic) number of unique entries. Column 1 can be A, B. Column 2 could be A, B, C, D, E and so on. I want to combine 1 item of each 5 columns and keep doing it until I create all possible combinations. The number of permutations (PERMUT) may be very large so I need to automate this. I'd like to avoid building a macro since I am sick of building them and I like the static spreadsheet challenge more.

Add your answers in the comments.

Share this post:

Has your Excel application slowed down lately? Have you installed the Google Desktop Application?

They may be related. Dick from Daily Dose of Excel reports this article taken from Charles Williams web page.

This report comes from Charles Williams of DecisionModels.com fame. Charles knows a thing or two about performance in Excel.

For the last few weeks I have been trying to find out
why clearing a large range of cells caused Excel to hang on some PCs
but not on others. This does not happen with Excel 97, but does with
Excel 2000, 2002, 2003 and 2007.

Chad Rothschiller, a program manager on the Excel team has written a lengthy article discussing using formulas to 'clean up' data in Excel. It is very indepth and covers these topics

Jean Philippe Bagel works for Microsoft. He has written some great tips for Excel 2007.

Of course the right click on the sheet tab navigation buttons will work in older versions of Excel and is a great way of navigating rapidly through large workbooks. Navipane provides a great alternative that is not as limited as Excels built in display of available sheet tabs.

Excel 2007 has some very advanced and easy to use conditional formatting and Jean explains one very well. However please be very careful with using this feature in any Excel 2007 workbooks that need to be viewed or edited on a Excel 2003 or lower machine. The conditional formatting in 2007 inst backward compatible. In fact it is quite different in both how it works and how it displays to previous versions of Excel.