Tricky array formula

[Bob Phillips]

Replied before I saw this

Sub RunTimeData()
Dim iLastRow As Long
Dim iRow As Long
Dim i As Long, j As Long
Dim iStartRow As Long
Dim iPos As Long
Dim oWs2 As Worksheet
Dim oWs3 As Worksheet

Set oWs2 = Worksheets("Sheet2")
Set oWs3 = Worksheets("Sheet3")
oWs3.Cells.ClearContents

With Worksheets("Sheet1")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
iRow = iRow + 1
oWs3.Cells(iRow, "A").Value = .Cells(i, "A").Value
iStartRow = iRow + 1
For j = 1 To oWs2.Cells(Rows.Count, "A").End(xlUp).Row
If oWs2.Cells(j, "B").Value = .Cells(i, "A").Value Then
iRow = iRow + 1
With oWs3.Cells(iRow, "A")
.NumberFormat = "@"
iPos = InStr(1, oWs2.Cells(j, "D").Value, "/")
.Value = Trim(Left(oWs2.Cells(j, "D").Value, iPos -
1))
End With
With oWs3.Cells(iRow, "B")
.NumberFormat = "mm:ss"
.Value = oWs2.Cells(j, "C").Value
End With
With oWs3.Cells(iRow, "C")
.NumberFormat = "d mmm yyyy"
.Value = oWs2.Cells(j, "A").Value
End With
End If
Next j
If iStartRow < j Then
oWs3.Range("A" & iStartRow & ":A" & j).Sort _
key1:=oWs3.Range("A" & iStartRow), _
header:=xlNo
End If
iRow = iRow + 1
Next i
End With

oWs3.Activate

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Carl Lindmark]

Thank you very much for your help, Bob!

Unfortunately, a new problem has arisen. Evidently I wasn't quite finished
with the code for my "Sheet 2" page, because the named ranges I've created
are not automatically expanded when I add a new row with data on Sheet 2.

I tried to use one of the suggested solutions on this page:
www.xldynamic.com/source/xld.LastValue.html (created in part by you, I
think? ), but I guess I must have messed up somewhere, because this does
not seem to work in a dynamic fashion:

=OFFSET(Sheet2!$B$2,0,0,MATCH(LOOKUP(REPT("Z",255),Sheet2!$B:$B),Sheet2!$B:$
B),1)

Any suggestion what I could use instead of the above code for my dynamic
name range (where the range consist of text entries and includes empty rows
in between data)?

Thanks again!
/Carl

 

[Bob Phillips]

Carl,

I don't know what problem you have encountered, but your formula creates a
dynamic range nicely for me, embedded blanks and all.

The only thing to note is that the match returns the row number, of the last
item, but you start your range at B2, so if the last item is in B16 say, the
dynamic range will be B2:B17.

Other than that, everything looks cool.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Carl Lindmark]

Bob,

Well, no - for me, the named range does not seem expand automatically when I
add more entries.

The following formula is used for my named range "Courses" (the course name
reside in column B):

=OFFSET(Sheet2!$B$2,0,0,MATCH(LOOKUP(REPT("Z",255),Sheet2!$B:$B),Sheet2!$B:$
B),1)

The following formula is used for my named range "Times" (the different
times reside in column C):

=OFFSET(Sheet2!$C$2,0,0,MATCH(LOOKUP(REPT("Z",255),Sheet2!$B:$B),Sheet2!$B:$
B),1)

(as you see, I've intentionally used column B here too when counting the
number of used rows - just to make sure that the two named ranges are
exactly the same size, which I've checked that they are. Everything looks
great up to this point.)

Then, for my ranking column, column D, I've put:

=SUMPRODUCT(--(Courses=B4),--(Times<C4))+1&" / "&COUNTIF(Courses,B4)

And it works beautifully - for all the rows of data that were already
entered before I wrote the formula, but it doesn't work for new entries that
I add, because the named ranges do not seem to automatically expand.
*confused*

Sincerely,
Carl

 

[Carl Lindmark]

Hello,

I just thought I'd post here to say that I figured out what was wrong.

I changed the named range from:

=OFFSET(Sheet2!$B$2,0,0,MATCH(LOOKUP(REPT("Z",255),Sheet2!$B:$B),Sheet2!$B:$
B),1)

to:

=OFFSET(Sheet2!$B$2,0,0,MATCH(REPT("Z",255),Sheet2!$B:$B,1),1)

In the earlier, faulty, expression, the LOOKUP function worked great, but
the MATCH function did not always find THE LAST occurrence of the value that
LOOKUP gave. Sometimes, when the same data was present in several cells,
MATCH pointed to one of the other occurrences of the data - instead of the
last cell in the list, which I wanted.

Anyway, now I know how to do it right.

Take care, and thanks to everyone who answered in this thread!

Sincerely,
Carl

 

[Carl Lindmark]

I just realized that I never thanked you for the "RunTimeData()" function...

Anyway, it works great, and I'm very thankful for it! *unfortunately, it
takes WAY too long to execute (a couple of minutes), but, I guess that can't
be helped...*


Sincerely,
Carl