Ordering a List

[Steve 1962]

Hi

I am attempting to have random entries placed in an ordered summary. Column "A" has the random names. Column "C" has the ordered summary that I need. I will be adding continuously to column "A" so the formulas in column "C" must be dynamic in nature to cater for the additions.

Thanks

Steve

Excel Workbook
	A	B	C
1	LIST		ORDERED
2	Black Dog - 1		Black Dog - 1
3	Red Cat - 1		Black Dog - 2
4	Blue Fish - 1		Black Dog - 3
5	Red Cat - 2		Blue Fish - 1
6	Yellow Rat - 1		Green Bird - 1
7	Red Cat - 3		Red Cat - 1
8	Black Dog - 2		Red Cat - 2
9	Yellow Rat - 2		Red Cat - 3
10	Green Bird - 1		Red Cat - 4
11	Red Cat - 4		Yellow Rat - 1
12	Black Dog - 3		Yellow Rat - 2
13	Yellow Rat - 3		Yellow Rat - 3
Sheet1

Excel 2007

 

[Steve 1962]

Format will be constant.

I do need the last row of totals.

Thanks.

 

[taurean]

I have sent the updated workbook. Please look into it.

In the column B, there's formula so the better event was worksheet calculate. It was good that you sent the workbook over. Here is the updated code.

Private Sub Worksheet_Calculate()
Dim lLastRow As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
lLastRow = Range("B" & Rows.Count).End(xlUp).Row - 1
On Error GoTo ResetSettings
If Target.Column = 2 Then
Range("B2:B" & lLastRow).Copy
Range("R2").PasteSpecial xlPasteValues
Range("R2:R" & lLastRow).Sort Key1:=Range("R2"), Order1:=xlAscending, _
Header:=xlNo, MatchCase:=False, dataoption1:=xlSortNormal, Orientation:=xlSortColumns, _
SortMethod:=xlPinYin
Range("R2:R" & lLastRow).Replace What:="#N/A", Replacement:=""
End If
ResetSettings:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

 

[Steve 1962]

Thanks Shrivallabha

Just one more question –

If I was to add one more row at the top and now make the first entry on row 3 and not row 2, how would I need to change the code ?

Thanks

Steve

 

[taurean]

I ended up adding a button as otherwise the sheet would have bogged you down at each calculation.

Private Sub UpdateCol_Click()
Dim lLastRow As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
'-------------------------------------------------------------------------
'Help Comment #1
'This should refer to your current column where your formula resides
'Currently its column B change it to suitable column letter as you change
'sheet layout
'-------------------------------------------------------------------------
lLastRow = Range("B" & Rows.Count).End(xlUp).Row - 1
On Error GoTo ResetSettings
'-------------------------------------------------------------------------
'Help Comment #2
'The number 3 in all rows below refers to row number which you have changed
'from 2 to 3
'-------------------------------------------------------------------------
Range("B3:B" & lLastRow).Copy 'Change Column letter in accordance with Help Comment #1
'-------------------------------------------------------------------------
'Help Comment #3
'If the Sheet Layout changes then change the Column Number R to the corres
'ponding new column number
'-------------------------------------------------------------------------
Range("R3").PasteSpecial xlPasteValues 'Refer Help Comment #2 & #3
For i = lLastRow To 3 Step -1
    If IsNumeric(Range("R" & i).Value) Then
    Range("R" & i).Delete Shift:=xlShiftUp
    End If
Next i
Range("R3:R" & lLastRow).Sort Key1:=Range("R3"), Order1:=xlAscending, _
Header:=xlNo, MatchCase:=False, dataoption1:=xlSortNormal, Orientation:=xlSortColumns, _
SortMethod:=xlPinYin 'Refer Help Comment #2 & #3
Range("R3:R" & lLastRow).Replace What:="#N/A", Replacement:="" 'Refer Help Comment #2 & #3
ResetSettings:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

PS: I have sent you updated workbook Test2 with detail explanation. Please check your email.

 

[Steve 1962]

Hi Shrivallabha

Thank you for your patience and understanding and effort. I will go away and try to use and understand your work.

Nice to know that we have experts out there that we can call on.

All the best

Steve

 

[taurean]

Oh, I am just another guy in the crowd. I come here to see their (experts) tips. Keep trolling here and you will learn a lot more as people come in with their practical situations and difficulties.

 

[Excelestial]

It appears that your orders are rearranged in simple alphabetical order. If so...and since you prefer formula...

=IF(ISERROR(INDEX(Orders,MATCH(SMALL(COUNTIF(Orders,"<="&Orders),ROW()-4), COUNTIF(Orders,"<="&Orders),0))),"",INDEX(Orders,MATCH(SMALL(COUNTIF(Orders,"<="&Orders),ROW()-4), COUNTIF(Orders,"<="&Orders),0)))

Note: You will need to name the range of the input column and use the OFFSET to make your list dynamic as it will surely grow. also...assign your row (blue text) according to the number of rows above your list (my example shows that my list started on row 5....which means there were 4 rows above my list)

 

[Excelestial]

PostScript: Highlight the number of rows in the column the maximum number you feel you'll need and enter the formula as an array (CRTL+SHIFT+ENTER)

 

[Excelestial]

<TABLE style="WIDTH: 571pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=762 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" span=3 width=91><COL style="WIDTH: 319pt; mso-width-source: userset; mso-width-alt: 15542" width=425><TBODY><TR style="HEIGHT: 34.5pt" height=46><TD class=xl63 id=td_post_2840986 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 34.5pt; BACKGROUND-COLOR: transparent" width=64 height=46> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=91> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=91> </TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=91> </TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 319pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4" width=425> {=IF(ISERROR(INDEX(Orders,MATCH(SMALL(COUNTIF(Orders,"<="&Orders),ROW()-4), COUNTIF(Orders,"<="&Orders),0))),"",INDEX(Orders,MATCH(SMALL(COUNTIF(Orders,"<="&Orders),ROW()-4), COUNTIF(Orders,"<="&Orders),0)))}</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Black Dog - 1

</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Black Dog - 1</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Black Dog - 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>3</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Red Cat - 1</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Black Dog - 2</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Black Dog - 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>4</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Blue Fish - 1</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Black Dog - 3</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Black Dog - 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>5</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Red Cat - 2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Blue Fish - 1</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Blue Fish - 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>6</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Yellow Rat - 1</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Green Bird - 1</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Green Bird - 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>7</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Red Cat - 3</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Red Cat - 1</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Red Cat - 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>8</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Black Dog - 2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Red Cat - 2</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Red Cat - 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>9</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Yellow Rat - 2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Red Cat - 3</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Red Cat - 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>10</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Green Bird - 1</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Red Cat - 4</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Red Cat - 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>11</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Red Cat - 4</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Yellow Rat - 1</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Yellow Rat - 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>12</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Black Dog - 3</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Yellow Rat - 2</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Yellow Rat - 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>13</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Yellow Rat - 3</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=91>Yellow Rat - 3

</TD><TD class=xl66 id=td_post_2837830 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fcd5b4">Yellow Rat - 3</TD></TR></TBODY></TABLE>

 

[Steve 1962]

Hi Excelestial

Thanks very much for the alternative you have provided - I will take some time to try and understand it. I will use this formula.

I'll let you know if I have any trouble.

Your time is much appreciated.

Thanks

Steve