How to adapt a formula?

[john.bedford3]

I am using the following formula, copied across and down the sheet, which I
arrived at with help from this group. (Thanks once again for that).

=IF(ISERROR(SMALL('Input List'!$AR:$AR,ROWS($A$1:$B1))),"",INDEX('Input
List'!$B:$B,MATCH(SMALL('Input List'!$AR:$AR,ROWS($A$1:$B1)),'Input
List'!$AR:$AR,0)))

This selects entries for a specific month and returns the following result.
A B C D E F
G
Species Date Place lbs ozs drms Kilos
Chub 01-Mar-1981 R. Roding 1 4 00.0 0.566
Chub 01-Mar-1981 R. Roding 1 3 00.0 0.538
Perch 08-Mar-1981 Furnace Pond 3 4 00.0 0.963
Perch 09-Mar-1981 Furnace Pond 2 5 00.0 0.566

The 'Input List' is as follows (sorted by Species ascending and Kilos
descending ) and already has additional columns indicating the row numbers
for each Species and for each month.
B C D E F G
H
Species Date Place lbs ozs drms Kilos
Barbel 25-Aug-1984 River Kennet 2 13 0.0 1.275
Chub 01-Jan-1980 Luxborough Pit 1 5 0.0 0.595
Chub 01-Mar-1981 R. Roding 1 4 0.0 0.566
Chub 01-Mar-1981 R. Roding 1 3 0.0 0.538
Carp 07-Oct-1984 Lockwood 20 8 0.0 9.298
Carp 27-Oct-1984 Maynard Res 19 10 0.0 8.901
Carp 05-Sep-1982 Furnace Pond 14 3 0.0 6.435
Perch 03-Jan-1982 Furnace Pond 3 9 0.0 1.615
Perch 08-Mar-1981 Furnace Pond 3 4 0.0 1.474
Perch 09-Mar-1981 Furnace Pond 2 5 8.0 1.063

I now want to add an extra condition to the formula to show in a list on a
separate sheet only the first line for each Species in each month but have
not been able to work out how to adapt it.

Is there a way of adapting this formula to do this or should I be looking
for a different approach?

 

[Bernd Plumhoff]

Hello John,

A macro could easily solve this, for example:
Option Explicit

Sub a()
Dim i As Long, j As Long

Sheets("Sheet1").Select
Range("A1").Resize(65536, 256).ClearContents

With Sheets("Input")
.Range("A1").EntireRow.Copy
Range("A1").Insert
.Range("A2").EntireRow.Copy
Range("A2").Insert

i = 3
j = 3
Do While Not IsEmpty(.Range("B" & i))
If .Range("B" & i).Text <> .Range("B" & i - 1).Text Then
.Range("A" & i).EntireRow.Copy
Range("A" & j).Insert
j = j + 1
Else
If Month(.Range("C" & i).Value) <> Month(.Range("C" & i -
1).Value) And _
Year(.Range("C" & i).Value) <> Year(.Range("C" & i -
1).Value) Then
.Range("A" & i).EntireRow.Copy
Range("A" & j).Insert
j = j + 1
End If
End If
i = i + 1
Loop
End With

End Sub

Press ALT+F11, insert module if necessary, copy program above into module,
finally run it (press F5).

HTH,
Bernd

 

[john.bedford3]

Thanks Bernd, I have in the meantime found a solution without using VBA but
I will keep this to use when I am more familiar with VBA.