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
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