List all combinations in Excel

[Andy Nguyen]

The table contains the pair and data for different terms (5,7,10y).
The task is to LIST all combination of and dump their data into X, Y coordinates.
The example of X,Y coordinates is for pair 3%-6% (5Y) for X coordinate versus 12%-22% (10Y) for Y coordinates.

The pair will be in increasing order i.e 1%-2% and 23%-29% are acceptable while 3%-3% and 15%-9% are not.
Same pair of same terms should be eliminated i.e we don't need to do 3-4% (5Y) vs 3-4% (5Y).

Anyone knows if Excel has a function or trick I can accomplish this?
Or VBA here I comes?

 

[charlesdwright]

will you have, for example, 5y(1%,2%) = X, 7y(1%,2%) = Y, or will identical terms not be counted?

Also, is it correct that (x, y) will include (5,7), (5,10), & (7,10)?

I know of no function, but of course it's doable in VBA and then you'll have your function .

 

[Adam]

The other important question is: Is this a one-time process you need to complete now, or is this a process you'll need to repeat over and over with new sets of data each time? How you go about solving this problem (and how much time it's worth dedicating to it) will depend heavily on this.

Assuming you need to repeat the process multiple times, will you always have the same data dimensions? (ie. 30 percentage rows and three year columns) Must the process be push-button easy, or are you OK relying on the ultimate user actually doing some work?

FWIW, I agree with Charles, there's no native one-step way in Excel to do this. If you want a repeatable, push-button data manipulation process like this, you're talking macro. If you just need to do this once, however, you're probably better off not coding it, gathering the data by hand, and building the charts yourself. Shouldn't take more than ten minutes at most, depending on your comfortability with Excel.

 

[doug reich]

Seems about right -- mostly a matter of creative cut-and-paste and "fill" if you're doing a one-time exercise. Otherwise, varying levels of macro automation will cut out steps.

Either way, functions like MATCH and VLOOKUP are key.

 

[Andy Nguyen]

@Chas,
You can also compare (5,5), (7,7),(10,10) as long as you don't compare the same pair with the same term.
@Adam,
The data will change everyday. The dimension will be fixed.
If someone thinks they can generate all the possible combination by hand in less than 10 minutes, please be my guest
For starter, a combination table will do. I can use lookup,match to fill in for the X,Y data.
I manually fill in the table by hand and I thought there must be a better way to generate all the combination
Included is the same sheet with the combolist.

Now to think about it, how do we generate this table in C++, C#?

 

[charlesdwright]

andy,

are the following permissible?

5(.03,.04) 7(.01,.02)
5(.03,.09) 7(.04,.05)

 

[Andy Nguyen]

Chas,
They are perfectly OK. What made you ask that?
The smallest pair is 0-1% and the biggest pair is 0-30%
All of these pairs compares within the term and across the terms.

 

[doug reich]

Does Excel 2007 have a column limit? If not, then what I have so far is almost there, and could easily be finished there. Otherwise, well... have to think a little more.

I basically did what I would have in C++; I generated a list of the triples you want (Rate 1, Rate 2, Term), which is 30 x 30 x 3 = 2700 cells. See this in column AP on Sheet1. I have provided comments in column J and column AP to give an indication of my method.

To finish, I would again cross it with itself (assuming you can have 2700 columns!) and use the Autofilter to remove the duplicates. (You can do this by sorting the list, and then comparing cell i with cell i-1, and using autofilter to show only those that are NOT distinct from the prior cell, and delete those rows.)

If excel can't handle this, you certainly can do the crossing in C++ or otherwise, and dump the results back into Excel to make it output only the uniques.

Is it correct that you want on the order of 3 million combinations?

 

[charlesdwright]

does 4,336,080 data points sound right?

 

[doug reich]

does 4,336,080 data points sound right?

Isn't it 2701 choose 2 ? (tex doesn't like \choose here, I guess). I calculate that as 3,646,350. I could be leaving out the diagonals by mistake, but I dont' think so.

 

[Andy Nguyen]

So how many combination are there in total, not counting duplicates
If we count pair one as (1%,2%,5Y) versus (2%,3%,7Y) then we don't need to count (2%,3%,7Y) versus (1%,2%,5Y) AGAIN because they are the same.

Isn't it 2701 choose 2 ? (tex doesn't like \choose here, I guess).

In a native latex environment, you would use \(\binom{2701}{2}\).

 

[doug reich]

I see that I do (will) have some duplicates, but I maintain that it's easier to create the whole long list and then delete the unacceptable ones. For example, I created (A,B) and (B,A). We can delete one of those before proceeding to reduce space. However, the method I outlined will pretty much give you what you want.

 

[doug reich]

OK, I thought about it a little more, and you can do the crossing I was describing in excel using nothing special. See attached. Columns AU and AW should have all possible pairs if you fill the sheet down 3 million rows (or however many pairings there are). Excel 2007 can handle millions of rows, right? I do recommend you copy paste-as-values as you go, though...

Then it's a matter of filtering the list down (or redo some of my steps to filter incorrect pairs as you go).

 

[charlesdwright]

I can't seem to upload an .xla, Andy. Can I email one to you?

 

[charlesdwright]

Does Excel 2007 have a column limit? If not, then what I have so far is almost there, and could easily be finished there. Otherwise, well... have to think a little more.

I basically did what I would have in C++; I generated a list of the triples you want (Rate 1, Rate 2, Term), which is 30 x 30 x 3 = 2700 cells. See this in column AP on Sheet1. I have provided comments in column J and column AP to give an indication of my method.

To finish, I would again cross it with itself (assuming you can have 2700 columns!) and use the Autofilter to remove the duplicates. (You can do this by sorting the list, and then comparing cell i with cell i-1, and using autofilter to show only those that are NOT distinct from the prior cell, and delete those rows.)

If excel can't handle this, you certainly can do the crossing in C++ or otherwise, and dump the results back into Excel to make it output only the uniques.

Is it correct that you want on the order of 3 million combinations?

Doug,

being the brute force type, that's the approach I took -- and I'm not sure if I have overlap.

I would like to understand yours better. I think your output table is much more user friendly than mine, providing as it does an intuitive lookup. I suppose I could rearrange the outuput of mine to do the same, but it was past my bedtime when I shut down last night.

How do you envision the user interacting with the table -- i.e. getting the relevant info from it?

 

[Andy Nguyen]

Chas,
For file type not in QN allowed list, the solution is to zip it and upload it.

 

[doug reich]

Once you have this list, the data I have created just serves as a hash table for any data you'll populate it with; what I have done is a one-time exercise.

When you have new data, make a much smaller, but similar, 90 element hash table (30 rates x 3 terms), which you can query to populate our much larger hash table (we break out our hash table to be able to search another table with (RATE, TERM) pairs). I assume you'll populate our large hash table with Andy's original 4-variables (START1, TERM1), (END1, TERM1), (START2, TERM2), (END2, TERM2).

Then you can go to town on that huge array manipulating those 4 numbers row-by-row or across rows, and run a vlookup on any pair you want to get the result. These last two paragraphs (populating and manipulating) are probably worth automating after one or two times by hand...

 

[Sam Harris]

allrighty

Chas,
For file type not in QN allowed list, the solution is to zip it and upload it.

Andy:

Try this:

1. Open the attached "Examples(1)." (I rearranged it a bit).
2. download the attached XLA and install it. It should give you a toolbar w/1 button.
3. Select cells "A10: D39" on Examples(1).Sheet1 -- this is the data range minus the header.
4. Push the button.
5. Get a cup of coffee.

 

[Andy Nguyen]

Chas,
When you type : and D here, it will display :D so I didn't really realize it. It's running now, stuck at 1% for a few minutes. It crashed my Excel 2007
Chazcombine is really powerful!
I need to rearrange the output to be more useful.
I added the 0% on top of 1% and have 0 in all 5,7,10Y and the code now giving me duplicates of 0 | 0 | 0.01 | 0.61
When this is fixed, I'll save the code in your xla and convert it to C# for later use.

 

[charlesdwright]

Chas,
When you type : and D here, it will display :D so I didn't really realize it. It's running now, stuck at 1% for a few minutes. It crashed my Excel 2007
Chazcombine is really powerful!
I need to rearrange the output to be more useful.
I added the 0% on top of 1% and have 0 in all 5,7,10Y and the code now giving me duplicates of 0 | 0 | 0.01 | 0.61
When this is fixed, I'll save the code in your xla and convert it to C# for later use.

Give me an idea of what your ideal output would look like. I'll look into the dup tonight.

Also, it was done on 2003. I have yet to hear anything good about 2007. '03 is a rock: can you run on that?

Ranges can be converted to arrays and sent to a C++ dll.