Grouping by part of text

[zakasnak]

I have a spreadsheet that I would like to group the information by the part of the text that matches another cell.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 115px"><COL style="WIDTH: 270px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Microsoft Sans Serif; TEXT-ALIGN: center">TV Grouping</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Title</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">8</TD><TD>DVD-1000 PLACES TO SEE BEFORE </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">9</TD><TD>DVD-1000 PLACES TO SEE COLL 2 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">10</TD><TD>DVD-101 TIMELESS TV CLASSICS </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">11</TD><TD>DVD-18 KIDS AND COUNTING S2 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">12</TD><TD>DVD-1ST AMENDMENT S4 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">13</TD><TD>DVD-200 FAMILY CARTOONS 4 DVD </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">14</TD><TD>DVD-2057 FUTURE CAR (WS) </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">15</TD><TD>DVD-21 JUMP STREET S1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">15</TD><TD>DVD-21 JUMP STREET S1 V1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">15</TD><TD>DVD-21 JUMP STREET S1 V2 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">15</TD><TD>DVD-21 JUMP STREET S1 V3 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">15</TD><TD>DVD-21 JUMP STREET S1 V4 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">16</TD><TD>DVD-21 JUMP STREET S2 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">16</TD><TD>DVD-21 JUMP STREET S2 V1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">16</TD><TD>DVD-21 JUMP STREET S2 V2 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">16</TD><TD>DVD-21 JUMP STREET S2 V3 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: center">16</TD><TD>DVD-21 JUMP STREET S2 V4 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">16</TD><TD>DVD-21 JUMP STREET S2 V5 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">16</TD><TD>DVD-21 JUMP STREET S2 V6 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">17</TD><TD>DVD-21 JUMP STREET S3 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: center">17</TD><TD>DVD-21 JUMP STREET S3 V1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">17</TD><TD>DVD-21 JUMP STREET S3 V2 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">17</TD><TD>DVD-21 JUMP STREET S3 V3 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: center">17</TD><TD>DVD-21 JUMP STREET S3 V4 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: center">17</TD><TD>DVD-21 JUMP STREET S3 V5 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: center">17</TD><TD>DVD-21 JUMP STREET S3 V6 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD> </TD><TD>DVD-21 JUMP STREET S4 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD> </TD><TD>DVD-24 REDEMPTION </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD> </TD><TD>DVD-24 S1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD> </TD><TD>DVD-24 S1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD> </TD><TD>DVD-24 S1 (SE) </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD> </TD><TD>DVD-24 S1 V1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD> </TD><TD>DVD-24 S1 V2 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD> </TD><TD>DVD-24 S1 V3 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD> </TD><TD>DVD-24 S1 V4 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD> </TD><TD>DVD-24 S1 V5 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD> </TD><TD>DVD-24 S1 V6 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">39</TD><TD> </TD><TD>DVD-24 S1-5 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">40</TD><TD> </TD><TD>DVD-24 S2 V1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">41</TD><TD> </TD><TD>DVD-24 S2 V2 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">42</TD><TD> </TD><TD>DVD-24 S2 V3 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">43</TD><TD> </TD><TD>DVD-24 S2 V4 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">44</TD><TD> </TD><TD>DVD-24 S2 V5 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">45</TD><TD> </TD><TD>DVD-24 S2 V6 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">46</TD><TD> </TD><TD>DVD-24 S3 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">47</TD><TD> </TD><TD>DVD-24 S3 V1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">48</TD><TD> </TD><TD>DVD-24 S3 V1 (FG) </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">49</TD><TD> </TD><TD>DVD-24 S3 V2 </TD></TR></TBODY></TABLE>

Each individual, unique value in Col B, would receive it's own number in Col A. The seasons with volumes attached each need the same number as the parent season label. Duplicates will ultimately be deleted, but in the interim, should be considered a volume.

Numbers should be consecutive, if at all possible. Can anyone help me get started?

 

[pbornemeier]

There seems to be a contradiction between, "Each individual, unique value in Col B, would receive it's own number in Col A" and "The seasons with volumes attached each need the same number as the parent season label".

I assume that you would want these:
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD></TD><TD>DVD-24 S1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD></TD><TD>DVD-24 S1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">32</TD><TD></TD><TD>DVD-24 S1 (SE) </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD></TD><TD>DVD-24 S1 V1 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD></TD><TD>DVD-24 S1 V2 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD></TD><TD>DVD-24 S1 V3 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">36</TD><TD></TD><TD>DVD-24 S1 V4 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">37</TD><TD></TD><TD>DVD-24 S1 V5 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">38</TD><TD></TD><TD>DVD-24 S1 V6 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">39</TD><TD></TD><TD>DVD-24 S1-5 </TD></TR></TBODY></TABLE>

To all have the same number in column A
C2 =returnvx(B2)
D2 =TRIM(SUBSTITUTE(B2,C2,""))
E2 =IF(ISERROR(FIND("(",B2)),"",MID(B2,FIND("(",B2),100))
F2 =TRIM(SUBSTITUTE(D2,E2,""))
G2 8
G3 =IF(F3=F2,G2,G2+1)

Drag-Copy C2:F2 down; Drag-Copy G3 down

Procedure:
Applt formulas as shown above
Manually fix the oddities in column F (as in Row 39 in your example)

Put this code in the ThisWorkbook codepage

Function ReturnVx(rngCellAddress As Range)
    Dim lLVPos As Long
    lLVPos = InStrRev(UCase(rngCellAddress.Value), "V")
    If lLVPos > 0 And lLVPos <= Len(rngCellAddress.Value) - 2 Then
        If IsNumeric(Mid(rngCellAddress.Value, lLVPos + 1, 2)) Then
            ReturnVx = Mid(rngCellAddress.Value, lLVPos, 3)
        Else
            ReturnVx = ""
        End If
    Else
         ReturnVx = ""
    End If
 
    If lLVPos > 0 And lLVPos <= Len(rngCellAddress.Value) - 1 Then
        If IsNumeric(Mid(rngCellAddress.Value, lLVPos + 1, 1)) Then
            ReturnVx = Mid(rngCellAddress.Value, lLVPos, 2)
        Else
            ReturnVx = ""
        End If
    Else
         ReturnVx = ""
    End If
 
End Function

It would be preferable (although time consuming) to modify the worksheet with additional multiple columns:
Base title, Sx, Vx, Special Features, Comments

I don't believe the data in your current column a is in a uniform enough format to allow completely automated sorting to your requirements