Excel chord finder for Ukulele - finding valid notes combinations!

[potroveio]

Hi Excel Masters!

I need some help from you regarding to a sheet I am doing to make for a cavaquinho, a brazilian relative to the hawaiian ukulele!
I am stucked in this part: I have a mini table with the notes that compound a chord, a I want to find the valid combinations that result consequently a valid chord. Let me show the problem to you. Here is the table with the notes of a certain chord, F7M, whose notes are F, A, C and E, and their fret positions in each string (d, G, B, D):

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 26px;"><col style="width: 22px;"><col style="width: 26px;"><col style="width: 24px;"><col style="width: 26px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td>
</td><td>F</td><td>A</td><td>C</td><td>E</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td>d</td><td style="background-color: rgb(192, 192, 192); text-align: right;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">11</td><td style="background-color: rgb(192, 192, 192); text-align: right;">3</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>B</td><td style="background-color: rgb(192, 192, 192); text-align: right;">7</td><td style="background-color: rgb(192, 192, 192); text-align: right;">11</td><td style="background-color: rgb(192, 192, 192); text-align: right;">2</td><td style="background-color: rgb(192, 192, 192); text-align: right;">6</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td>G</td><td style="background-color: rgb(192, 192, 192); text-align: right;">11</td><td style="background-color: rgb(192, 192, 192); text-align: right;">3</td><td style="background-color: rgb(192, 192, 192); text-align: right;">6</td><td style="background-color: rgb(192, 192, 192); text-align: right;">10</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td>D</td><td style="background-color: rgb(192, 192, 192); text-align: right;">4</td><td style="background-color: rgb(192, 192, 192); text-align: right;">8</td><td style="background-color: rgb(192, 192, 192); text-align: right;">11</td><td style="background-color: rgb(192, 192, 192); text-align: right;">3</td></tr></tbody></table>
What I need to implement, is a magic formula that sweeps through the whole table to find the possibilities of chords.
The sequence that represents a valid chord:

-must contain at least one of the compounding nothes - F, A, C or E (in the table it means having one and no more than one value in each column)

-of course, no more than one note in the same string (=no more than one value at each line)

-the notes no more than five frets away from each other (thats a physical limitation from the player`s hand.

In this case, one of the solutions would be:

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 26px;"><col style="width: 22px;"><col style="width: 26px;"><col style="width: 24px;"><col style="width: 26px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td>
</td><td>F</td><td>A</td><td>C</td><td>E</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td>d</td><td style="background-color: rgb(192, 192, 192); text-align: right;">4</td><td style="text-align: right;">8</td><td style="text-align: right;">11</td><td style="text-align: right;">3</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td><td>B</td><td style="text-align: right;">7</td><td style="text-align: right;">11</td><td style="background-color: rgb(192, 192, 192); text-align: right;">2</td><td style="text-align: right;">6</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td><td>G</td><td style="text-align: right;">11</td><td style="background-color: rgb(192, 192, 192); text-align: right;">3</td><td style="text-align: right;">6</td><td style="text-align: right;">10</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td>D</td><td style="text-align: right;">4</td><td style="text-align: right;">8</td><td style="text-align: right;">11</td><td style="background-color: rgb(192, 192, 192); text-align: right;">3</td></tr></tbody></table>
What I need its something similar to the for loops in C language. But I wanted to do this without macros...
Anyone has any suggestion?
I am really thankful in advance for your comments.

Thanks,
Martin

 

[wolverineb]

OK. This isn't very straightforward without macros.

The only thing I could figure out was to list all of the 24 permutations using an INDEX formula (with a table of permutations of the numbers {1,2,3,4}) and then have a formula check which ones met the "no more than five frets" limitation. It works, but not sure that's what you meant.

 

[wolverineb]

My rudimentary table now looks like this. The permutations are listed below the columns where they are used. I have conditional formatting in place to highlight viable chord combinations.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
9	*	F	A	C	E	*	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE	TRUE	TRUE	FALSE	FALSE	TRUE	TRUE	FALSE	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	TRUE
10	d	4	8	11	3	*	4	4	4	4	4	4	8	8	8	8	8	8	11	11	11	11	11	11	3	3
11	B	7	11	2	6	*	11	11	2	2	6	6	7	7	2	2	6	6	7	7	11	11	6	6	7	7
12	G	11	3	6	10	*	6	10	3	10	3	6	6	10	11	10	11	6	3	10	11	10	11	3	3	6
13	D	4	8	11	3	*	3	11	3	8	11	8	3	11	3	4	11	4	3	8	3	4	8	4	11	8
14	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	1	1	1	1	1	1	2	2	2	2	2	2	3	3	3	3	3	3	4	4
16	*	*	*	*	*	*	2	2	3	3	4	4	1	1	3	3	4	4	1	1	2	2	4	4	1	1
17	*	*	*	*	*	*	3	4	2	4	2	3	3	4	1	4	1	3	2	4	1	4	1	2	2	3
18	*	*	*	*	*	*	4	3	4	2	3	2	4	3	4	1	3	1	4	2	4	1	2	1	3	2
Sheet2

 

[potroveio]

Hi Wolverine!
thank you really really much! thats exactly what I wanted to do. your solution is very ellegant. now I can go further with my sheet.
Just another doubt: with macros, how would you do it?

Martin

 

[wolverineb]

I'd have to think about it. Probably the same idea, but I'd loop through the possiblities and only display the valid ones. In hindsight now that the spreadsheet is set up, it might be easier than a macro.

I'm glad it's working for you.