Creating cross-references

[couriol]

Hi,

I have a list of items which belong to common sets (A, B, C...).

<table style="border-collapse: collapse; width: 114pt;" border="0" cellpadding="0" cellspacing="0" width="152"><col style="width: 66pt;" width="88"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 66pt;" height="20" width="88">Item</td> <td class="xl65" style="width: 48pt;" width="64">Set</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Necklace_a</td> <td class="xl66">A</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Ring_a</td> <td class="xl66">A</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Earring_a</td> <td class="xl66">A</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Necklace_b</td> <td class="xl66">B</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Ring_b</td> <td class="xl66">B</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Earring_b</td> <td class="xl66">B</td> </tr> </tbody></table>
I want to create a list associating each item with its siblings
This would look like this

<table style="border-collapse: collapse; width: 125pt;" border="0" cellpadding="0" cellspacing="0" width="167"><col style="width: 77pt;" width="103"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 77pt;" height="20" width="103">Item</td> <td class="xl65" style="width: 48pt;" width="64">Siblings</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Necklace_a</td> <td>Ring_a</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Necklace_a</td> <td>Earring_a</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Ring_a</td> <td>Necklace_a</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Ring_a</td> <td>Earring_a</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Earring_a</td> <td>Ring_a</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Earring_a</td> <td>Necklace_a</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Necklace_b</td> <td>Ring_b</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Necklace_b</td> <td>Earring_b</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Ring_b</td> <td>Necklace_b</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Ring_b</td> <td>Earring_b</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Earring_b</td> <td>Necklace_b</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Earring_b</td> <td>Ring_b</td> </tr> </tbody></table>

Do you have any idea of what the right code would be?

Thank you

 

[number kruncher]

Because there are two results for each Type of Jewelry, first create all the possibilities with the following, in Cols A to D

Necklace_ 1 Necklace_1 Ring_
Necklace_ 2 Necklace_2 Earring_
Ring_ 1 Ring_1 Necklace_
Ring_ 2 Ring_2 Earring_
Earring_ 1 Earring_1 Ring_
Earring_ 2 Earring_2 Necklace_

Col B shows 2 options for each test
Formulae in Col C =A1&B1

In the following tests, in Cols F to G

Necklace_A Ring_A
Necklace_A Earring_A

Formulae in Col G =VLOOKUP(LEFT(F1,FIND("_",F1))&COUNTIF(F$1:F1,F1),C$1:D$6,2,FALSE)&RIGHT(F1,1)

Increase the last RIGHT string length to cater for your descriptions if not A or B