Excel chord finder for Ukulele - finding valid notes combinations!

potroveio

New Member
Joined
Jan 25, 2010
Messages
10
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

wolverineb

Well-known Member
Joined
Jan 26, 2005
Messages
690
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

Well-known Member
Joined
Jan 26, 2005
Messages
690
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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
9*FACE*FALSEFALSETRUEFALSEFALSETRUETRUETRUEFALSEFALSETRUETRUEFALSETRUEFALSEFALSETRUEFALSEFALSETRUE
10d48113*44444488888811111111111133
11B71126*111122667722667711116677
12G113610*610310366101110116310111011336
13D48113*3113811831134114383484118
14**************************
15******11111122222233333344
16******22334411334411224411
17******34242334141324141223
18******43423243413142412132
Sheet2
 

potroveio

New Member
Joined
Jan 25, 2010
Messages
10
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

Well-known Member
Joined
Jan 26, 2005
Messages
690
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.
 

Forum statistics

Threads
1,136,341
Messages
5,675,207
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top