possible permutations?

biodand · May 21st, 2002, 05:55 AM

Anyone know how I can look at values in two columns with values from 1 to 5 in both columns and assign a letter (in a third column) to each of the possible 25 permutations. This also needs to work for a continuously expanding number of rows as the data is updated. I intend to use this third column to determine how many times any permutation occurs in the list of data rows with a COUNTIF statement.

Ex. column D is the output I desire.

B2 C2 Output(D2)
1 1 A
2 1 B
3 1 C
4 1 D
5 1 E
1 2 F
2 2 G
3 2 H
4 2 I
5 2 J
1 3 K
2 3 L

etc...

Thanks in advance.

sen_edp · May 21st, 2002, 06:27 AM

take a look

http://www.mrexcel.com/board/viewtop...c=8223&forum=2

it might help

eliW · May 21st, 2002, 06:37 AM

Hello biodand,
Isn't it more simple to count the result of concatenated cells? like this:
Eli

Microsoft Excel - PermutationCount.xls_______________Running: xl97 : OS = Windows (32-bit) 4.10
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
I7		=
	A	B	C	D	E
1		1	1	:alert("=B1&C1")>11
2		2	1	:alert("=B2&C2")>21
3		3	1	:alert("=B3&C3")>31
4		4	1	:alert("=B4&C4")>41
5		5	1	:alert("=B5&C5")>51
6		1	2	:alert("=B6&C6")>12
7		2	2	:alert("=B7&C7")>22
8		3	2	:alert("=B8&C8")>32
9		4	2	:alert("=B9&C9")>42
10		5	2	:alert("=B10&C10")>52
11		1	3	:alert("=B11&C11")>13
12		2	3	:alert("=B12&C12")>23
13		3	3	:alert("=B13&C13")>33
14		4	3	:alert("=B14&C14")>43
15		5	3	:alert("=B15&C15")>53
16		1	4	:alert("=B16&C16")>14
17		2	4	:alert("=B17&C17")>24
18		3	4	:alert("=B18&C18")>34
19		4	4	:alert("=B19&C19")>44
20		5	4	:alert("=B20&C20")>54
21		1	5	:alert("=B21&C21")>15
22		2	5	:alert("=B22&C22")>25
23		3	5	:alert("=B23&C23")>35
24		4	5	:alert("=B24&C24")>45
25		5	5	:alert("=B25&C25")>55
26
27			CountIf	:alert("=COUNTIF(D1:D25,44)")>1
28
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.23]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

biodand · May 21st, 2002, 08:25 AM

Thanks Eli,
this worked fine, I was not aware of this function.
bioDanD

May 21st, 2002, 06:27 AM	#2
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	take a look http://www.mrexcel.com/board/viewtop...c=8223&forum=2 it might help __________________ Best Regards Andreas

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 05:55 AM	#1
biodand New Member Join Date: May 2002 Posts: 5	Anyone know how I can look at values in two columns with values from 1 to 5 in both columns and assign a letter (in a third column) to each of the possible 25 permutations. This also needs to work for a continuously expanding number of rows as the data is updated. I intend to use this third column to determine how many times any permutation occurs in the list of data rows with a COUNTIF statement. Ex. column D is the output I desire. B2 C2 Output(D2) 1 1 A 2 1 B 3 1 C 4 1 D 5 1 E 1 2 F 2 2 G 3 2 H 4 2 I 5 2 J 1 3 K 2 3 L etc... Thanks in advance.

May 21st, 2002, 08:25 AM	#4
biodand New Member Join Date: May 2002 Posts: 5	Thanks Eli, this worked fine, I was not aware of this function. bioDanD