Re : Excel Range of Values Amidst Characteristic Transitions

[TKT-Tang]

Re : Excel Range of Values Amidst Characteristic Transitions

Enter an Excel worksheet ; now that the tabulation is prepared for
(preferential) presentation, Column A is shown as follows :-

01 8.0

02 8.0

03 <A Blank Row>

04 8.0

05 <A Blank Row>

06 <A Blank Row>

07 9.0

08 <A Blank Row>

09 9.0

10 9.0

11 <A Blank Row>

12 6.5

13 6.5

14 <A Blank Row>

15 <A Blank Row>

16 <A Blank Row>

17 8.0

18 <A Blank Row>

19 8.0

20 <A Blank Row>

21 8.0

22 <A Blank Row>

23 6.5

24 <A Blank Row>

25 6.5

26 <A Blank Row>

27 9.6

28 <List continues ...>

The range of values as given above shows the following characteristics
:-

1. The values transition from one to another (such as 8.0 to 9.0 to 6.5
to 8.0 to 6.5 to 9.6 etc) at irregular intervals.
2. The values are interspersed with Blank Rows and the number of Blank
Rows (amidst transitions or without transitions thereof) varies
unevenly (as well).

Please devise a formula that will return an array consisting of the row
numbers corresponding to the transition of values within the given
range namely, {01, 07, 12, 17, 23, 27}.

Please share your experience. Regards.

 

[Tom Hutchins]

Here is a possibility:
In cell B1, enter =ROW()
In cell B2, enter =IF(LEN(A2)=0,B1,IF(A2<>INDIRECT("A" &B1),ROW(),B1))
Copy B2 down through all rows of data

Here are 3 different VBA functions. RowArray1 returns a range of cells (the
cells where the values change in the input range). RowArray2 returns a string
with the row numbers separated by commas. RowArray3 returns a variant
containing an array of long integers (row numbers). The parameter for all
three is the range of cells in column B as described above.

Public Function RowArray1(Rng As Range) As Range
'Returns an array of cells
Dim Curr As Range, Prev As Long
Prev& = 0
For Each Curr In Rng
If Curr.Value <> Prev& Then
Prev& = Curr.Value
If RowArray1 Is Nothing Then
Set RowArray1 = Curr
Else
Set RowArray1 = Union(RowArray1, Curr)
End If
End If
Next Curr
End Function

Public Function RowArray2(Rng As Range) As String
'Returns a string with comma-delimited row numbers
Dim Curr As Range, Prev As Long
Prev& = 0
For Each Curr In Rng
If Curr.Value <> Prev& Then
Prev& = Curr.Value
RowArray2$ = RowArray2$ & ", " & Curr.Value
End If
Next Curr
RowArray2$ = "{" & Right(RowArray2$, Len(RowArray2$) - 2) & "}"
End Function

Public Function RowArray3(Rng As Range) As Variant
'Returns a variant containing an array of long integers
Dim Curr As Range, Prev As Long
Dim RA() As Long, x As Long
x& = 0
Prev& = 0
For Each Curr In Rng
If Curr.Value <> Prev& Then
x& = x& + 1
ReDim Preserve RA(x&)
RA(x&) = Curr.Value
Prev& = Curr.Value
End If
Next Curr
RowArray3 = RA
End Function


Hope this helps,

Hutch

 

[Tom Hutchins]

Or, we could skip all the column B stuff and use a function like the
following (with a sample sub to call it):

Sub AAAAAA()
Dim i, ZZZ
ZZZ = RowArray4(Range("A1:A27"))
For i = 1 To 6
MsgBox ZZZ(i)
Next i
End Sub

Public Function RowArray4(Rng As Range) As Variant
'Returns a variant containing an array of long integers
Dim Curr As Range, Prev
Dim RA() As Long, x As Long
x& = 0
Prev = 0
For Each Curr In Rng
If Len(Curr.Value) > 0 And Curr.Value <> Prev Then
x& = x& + 1
ReDim Preserve RA(x&)
RA(x&) = Curr.Row
Prev = Curr.Value
End If
Next Curr
RowArray4 = RA
End Function

Regards,

Hutch

 

[Harlan Grove]

TKT-Tang wrote...
....

1. The values transition from one to another (such as 8.0 to 9.0 to 6.5
to 8.0 to 6.5 to 9.6 etc) at irregular intervals.
2. The values are interspersed with Blank Rows and the number of Blank
Rows (amidst transitions or without transitions thereof) varies
unevenly (as well).

Please devise a formula that will return an array consisting of the row
numbers corresponding to the transition of values within the given
range namely, {01, 07, 12, 17, 23, 27}.

Name your range LST and define these additional names:

LST.next
=INDEX(LST,2,1):INDEX(LSTsheet!$1:$65536,MAX(ROW(LST))+1,COLUMN(LST))

seq
=ROW(LST)-MIN(ROW(LST))+1

Then you could generate a list of transitions with these formulas
beginning in D1.

D1 [array formula]:
=MIN(ROW(LST))

D2 [array formula]:
=SMALL(IF(ISNUMBER(LST.next)*(MMULT(--(SMALL(IF(ISNUMBER(LST),seq),
MMULT(--(seq>=TRANSPOSE(seq)),--ISNUMBER(LST)))=TRANSPOSE(seq)),
IF(ISNUMBER(LST),LST,0))<>LST.next),seq+1),ROWS(D$22))

Fill D2 down as far as needed. This is more flexible because the column
D list can grow or shrink as needed. If you really want a single array
formula, with all the hassle and inflexibility it entails, you could
use the array formula

=SMALL(IF(ISNUMBER(LST.next)*(MMULT(--(SMALL(IF(ISNUMBER(LST),seq),
MMULT(--(seq>=TRANSPOSE(seq)),--ISNUMBER(LST)))=TRANSPOSE(seq)),
IF(ISNUMBER(LST),LST,0))<>LST.next)+(seq=MAX(seq)),MOD(seq,MAX(seq))+1),
ROW($A$1:INDEX($A:$A,1+SUM(ISNUMBER(LST.next)
*(MMULT(--(SMALL(IF(ISNUMBER(LST),seq),MMULT(--(seq>=TRANSPOSE(seq)),
--ISNUMBER(LST)))=TRANSPOSE(seq)),IF(ISNUMBER(LST),LST,0))<>LST.next)))))