Partition data range by an indicator variable

[Arne]

The formula =OFFSET(Data!$R$1,1,0,COUNTA(Data!$R:$R)-1) returns the array of
data in column R. As I add data points, the size of the array changes to
match all of the data entered.

I would like to subset the data range into non-overlaping ranges by using an
indicator variable (1, 2, ...) in a different column.

Thanks,

Arne

 

[Doug Glancy]

Arne,

I think this is what you want. It assumes you are looking for the indicator
variable 1 on column A:

=INDEX(Sheet1!$A:$A,MATCH(1,Sheet1!$A:$A,-1)):OFFSET(INDEX(Sheet1!$A:$A,MATCH(1,Sheet1!$A:$A,-1)),COUNTIF(Sheet1!$A:$A,1)-1,0)

hth,

Doug

 

[Arne]

Doug,

I couldn't get the formula you suggested to work, but realized that I was
interpreting the syntax to match incorrectly. The expressions I came up with
were:

=IF(ISNA(MATCH(1,Data!$B:$B)),OFFSET(Col_Date,1,0,COUNTA(Data!$A:$A)-1),OFFSET(Col_Date,1,0,MATCH(1,Data!$B:$B)-1))


=IF(ISNA(MATCH(2,Data!$B:$B,0)),IF(ISNA(MATCH(1,Data!$B:$B,0)),#N/A,OFFSET(Col_Date,MATCH(1,Data!$B:$B,0),0,COUNTA(Data!$A:$A)-MATCH(1,Data!$B:$B,0))),OFFSET(Col_Date,MATCH(1,Data!$B:$B,0),0,MATCH(2,Data!$B:$B)-MATCH(1,Data!$B:$B)))


=IF(ISNA(MATCH(2,Data!$B:$B,0)),#N/A,OFFSET(Col_Date,MATCH(2,Data!$B:$B,0),0,COUNTA(Data!$A:$A)-MATCH(2,Data!$B:$B,0)))


This returns arrays of the date stored in column A, where Col_Date is the
location of the column header. The indicator variable is in column B. The
possible indicator variables are none, 1, 2. This yields a maximum of three
partitions.

Thanks,

Arne