Hi All,

I am using a dynamic range called "Data", spanning many rows and columns. The
range holds TEXT data and starts at row number 12, column "H". The oldest
data is in row 12, the start / top of my range; the most recent data is at
the end / bottom of my range.

The value to be returned is numeric and held in a single column, dynamic
range called "ID", adjacent to dynamic range "Data". The return value will be
returned down a single column.
I would like the Formula to be flexible using Input cells to hold the varying
criteria.

The MATCH sequence will be:
1) Match specific Text

2) Match varying sequential numbers of EMPTY TEXT rows (could be anything
from 0 (zero) EMPTY TEXT rows to 100+ EMPTY TEXT rows in sequential row order)
. NB: When the match of EMPTY TEXT is 0: there should be two sequential row
matches of the same TEXT (as found in number 1 above).

3) Return previous / penultimate MATCH of the above (1 & 2).

I've tried a few variations but still NO eureka!

=IF(AW7="","",INDEX(MATCH(2,(INDEX(1/((OFFSET('Site Lond'!Appraisal,0,ROWS($1:
1)-1,,1)=TEXT(AW7,0)))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS
(AZ7)&""),0,1)))+OFFSET('Site Lond'!ID,,,,1),0,1))-1

=IF(AW7="","",INDEX(MATCH(1,(OFFSET('Site Lond'!Appraisal,0,ROWS($1:1)-1,,1)
=TEXT(AW7,0))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS(AZ7)&""))
+OFFSET('Site Lond'!ID,,,,1),0,1))-1

Thanks
Sam

--
Message posted via http://www.officekb.com

Hi All,

Just to clarify.

The Criteria to Match:
The MATCH sequence will be:
1) Match specific Text

In Column1 - Single occurrence of Text value zero "0". (changing criteria)

2) Match varying sequential numbers of EMPTY TEXT rows (could be anything
from 0 (zero) EMPTY TEXT rows to 100+ EMPTY TEXT rows in sequential row order)
NB: When the match of EMPTY TEXT is 0: there should be two sequential row
matches of the same TEXT (as found in number 1 above).

In Column1 - Match one EmptyText row after / below single occurrence of Text
value zero "0". (changing criteria).

3) Return previous / penultimate MATCH of the above (1 & 2).

Return previous / penultimate match of single instance of Text value zero
"0" with one EmptyText row below it.

Expected Result ID 1304.

In the non-working formula below:
Cell AW7 holds the Text value of interest.
Cell AZ7 holds the number of EmptyText rows to match.
The Formula needs to filldown a single column.

=IF(AW7="","",INDEX(MATCH(2,(INDEX(1/((OFFSET('Site Lond'!Appraisal,0,ROWS($1:
1)-1,,1)=TEXT(AW7,0)))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS
(AZ7)&""),0,1)))+OFFSET('Site Lond'!ID,,,,1),0,1))-1

Sample Data Layout:
ID, Col1, Col2, Col3,
1300, EmptyText,1, EmptyText,
1301, 00, EmptyText,EmptyText,
1302, 0, 1, EmptyText,
1303, 0, 1, EmptyText,
1304, EmptyText,1, 2,
1305, 00, EmptyText,2,
1306, 0, EmptyText,EmptyText,
1307, 0, EmptyText,EmptyText,
1308, 0, 1, EmptyText,
1309, EmptyText,1, 22,

NB: Commas separate the columns.

1) Match specific Text for Column2
- Single occurrence of Text value "1"

2) Match varying sequential numbers of EMPTY TEXT rows
- Match zero EmptyText row after / below single occurrence of Text value one
"1".
this means two sequential instances of text value "1".

3) Return previous / penultimate MATCH of the above (1 & 2).
- Return previous / penultimate match of single instance of Text value one
"1" with zero EmptyText row below it.

Expected Result ID 1304.

Cheers
Sam

Sam wrote:
Hi All,

I am using a dynamic range called "Data", spanning many rows and columns. The
range holds TEXT data and starts at row number 12, column "H". The oldest
data is in row 12, the start / top of my range; the most recent data is at
the end / bottom of my range.

The value to be returned is numeric and held in a single column, dynamic
range called "ID", adjacent to dynamic range "Data". The return value will be
returned down a single column.
I would like the Formula to be flexible using Input cells to hold the varying
criteria.

The MATCH sequence will be:
1) Match specific Text

2) Match varying sequential numbers of EMPTY TEXT rows (could be anything
from 0 (zero) EMPTY TEXT rows to 100+ EMPTY TEXT rows in sequential row order)
. NB: When the match of EMPTY TEXT is 0: there should be two sequential row
matches of the same TEXT (as found in number 1 above).

3) Return previous / penultimate MATCH of the above (1 & 2).

I've tried a few variations but still NO eureka!

=IF(AW7="","",INDEX(MATCH(2,(INDEX(1/((OFFSET('Site Lond'!Appraisal,0,ROWS($1:
1)-1,,1)=TEXT(AW7,0)))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS
(AZ7)&""),0,1)))+OFFSET('Site Lond'!ID,,,,1),0,1))-1

=IF(AW7="","",INDEX(MATCH(1,(OFFSET('Site Lond'!Appraisal,0,ROWS($1:1)-1,,1)
=TEXT(AW7,0))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS(AZ7)&""))
+OFFSET('Site Lond'!ID,,,,1),0,1))-1

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200709/1

Try...

=IF(AW7<"",INDEX(ID,MATCH(2,1/((OFFSET(INDEX(Data,0,2),,,ROWS(Data)-1)=T
EXT(AW7,0))*(OFFSET(INDEX(Data,0,2),1,,ROWS(Data)-1)="")))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <78e69a99cec71@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Sample Data Layout:
ID, Col1, Col2, Col3,
1300, EmptyText,1, EmptyText,
1301, 00, EmptyText,EmptyText,
1302, 0, 1, EmptyText,
1303, 0, 1, EmptyText,
1304, EmptyText,1, 2,
1305, 00, EmptyText,2,
1306, 0, EmptyText,EmptyText,
1307, 0, EmptyText,EmptyText,
1308, 0, 1, EmptyText,
1309, EmptyText,1, 22,

NB: Commas separate the columns.

1) Match specific Text for Column2
- Single occurrence of Text value "1"

2) Match varying sequential numbers of EMPTY TEXT rows
- Match zero EmptyText row after / below single occurrence of Text value one
"1".
this means two sequential instances of text value "1".

3) Return previous / penultimate MATCH of the above (1 & 2).
- Return previous / penultimate match of single instance of Text value one
"1" with zero EmptyText row below it.

Expected Result ID 1304.

Cheers
Sam

Hi Domenic,

Thank you very much for reply and assistance.

The first cell returned the LAST ID occurrence of the matched text pattern. I
require the previous / penultimate occurrence of the matched pattern. The
following cells returned error value #N/A.

The Formula:
1) Where you've hard coded the INDEX column with 2, I've changed that to ROWS
($1:1) , so that I can access the correct columns when the Formula is filled
down.

2) I cannot see where in the Formula the varied number of EmptyText rows is
matched. This varies for each cell row and also needs to be filled down. In
my non-working version of the Formula, I was using cell AZ7 to hold the
number of emptyText rows I need to match after the Text value.

3) The Text value and the number of EmptyText rows to match are both variable
criteria.

4) Amending the Formula as in 1 above, all cells return the LAST matched
occurrence, but I require the previous / penultimate occurrence of the
matched pattern (Text Value = cell AW7 and varied EmptyText rows = cell AZ7).
These criteria will fill down with each row.

Further assistance appreciated.

Cheers,
Sam

Domenic wrote:
Try...

=IF(AW7<"",INDEX(ID,MATCH(2,1/((OFFSET(INDEX(Data,0,2),,,ROWS(Data)-1)=T
EXT(AW7,0))*(OFFSET(INDEX(Data,0,2),1,,ROWS(Dat a)-1)="")))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200709/1

Assuming that the formula will be entered in BA7 and copied down, try
the following...

1) Select cell BA7

2) Define the following...

Insert Name Define

Name: Range

Refers to:

=INDEX(Data,0,ROWS($BA$7:$BA7))

Click Add

Name: Arrray1

Refers to:

=TRANSPOSE(ROW(INDIRECT("1:"&$AZ7)))

Click Add

Name: Array2

Refers to:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Range= TEXT($AX7,0),ROW(Range)
+Array1,"")),IF(Range="",ROW(Range)),0))+0)

Click Ok

3) Enter the following formula in BA7, and copy down:

=INDEX(ID,LARGE(IF(Array2=$AZ7,TRANSPOSE(ROW(ID)-MIN(ROW(ID))+1)),2))

....confirmed with CONTROL+SHIFT+ENTER

**Note that if cell AX7 is formatted as 'Text', TEXT($AX7,0) can be
replaced with $AX7.

Hope this helps!

In article <78e8c043e0a20@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thank you very much for reply and assistance.

The first cell returned the LAST ID occurrence of the matched text pattern. I
require the previous / penultimate occurrence of the matched pattern. The
following cells returned error value #N/A.

The Formula:
1) Where you've hard coded the INDEX column with 2, I've changed that to ROWS
($1:1) , so that I can access the correct columns when the Formula is filled
down.

2) I cannot see where in the Formula the varied number of EmptyText rows is
matched. This varies for each cell row and also needs to be filled down. In
my non-working version of the Formula, I was using cell AZ7 to hold the
number of emptyText rows I need to match after the Text value.

3) The Text value and the number of EmptyText rows to match are both variable
criteria.

4) Amending the Formula as in 1 above, all cells return the LAST matched
occurrence, but I require the previous / penultimate occurrence of the
matched pattern (Text Value = cell AW7 and varied EmptyText rows = cell AZ7).
These criteria will fill down with each row.

Further assistance appreciated.

Cheers,
Sam

Domenic wrote:
Try...

=IF(AW7<"",INDEX(ID,MATCH(2,1/((OFFSET(INDEX(Data,0,2),,,ROWS(Data)-1)=T
EXT(AW7,0))*(OFFSET(INDEX(Data,0,2),1,,ROWS(Dat a)-1)="")))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi Domenic,

Thank you for your time and assistance.

1) The Formula returns incorrect results with some #REF! errors. I think the
#REF! errors may be from =TRANSPOSE(ROW(INDIRECT("1:"&$AZ7))). Not sure?

2) Also, I think the Formula is trying to return the penultimate ID that
matches the specific TEXT criteria above the EmptyText rows. I require the
penultimate ID that matches first, Text criteria and then the specific number
of variable EmptyText rows below the Text criteria. The penultimate ID
returned should match with the last EmptyText row criteria.

Further assistance most appreciated.

Cheers,
Sam

Domenic wrote:
Assuming that the formula will be entered in BA7 and copied down, try
the following...

1) Select cell BA7

2) Define the following...

Insert Name Define

Name: Range

Refers to:

=INDEX(Data,0,ROWS($BA$7:$BA7))

Click Add

Name: Arrray1

Refers to:

=TRANSPOSE(ROW(INDIRECT("1:"&$AZ7)))

Click Add

Name: Array2

Refers to:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Range =TEXT($AX7,0),ROW(Range)
+Array1,"")),IF(Range="",ROW(Range)),0))+0)

Click Ok

3) Enter the following formula in BA7, and copy down:

=INDEX(ID,LARGE(IF(Array2=$AZ7,TRANSPOSE(ROW(ID )-MIN(ROW(ID))+1)),2))

...confirmed with CONTROL+SHIFT+ENTER

**Note that if cell AX7 is formatted as 'Text', TEXT($AX7,0) can be
replaced with $AX7.

Hope this helps!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200709/1

Let's assume the following...

A2:A22 contains the ID, and named ID

B2:B22 contains the data, and named Data

AX7 contains the text value to match

AZ7 contains the the number of empty text values to match

A2:B22 contains the following...

1300 1
1301 Empty Text
1302 1
1303 2
1304 Empty Text
1305 1
1306 Empty Text
1307 Empty Text
1308 1
1309 Empty Text
1310 Empty Text
1311 Empty Text
1312 1
1313 Empty Text
1314 Empty Text
1315 1
1316 2
1317 Empty Text
1318 Empty Text
1319 Empty Text
1320 1

AX7 contains 1

AZ7 contains 2

So if we're trying to find the penultimate ID where the text value 1 is
followed by 2 empty text values, the formula should return 1310. If
this is correct, try the following...

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&Sheet1!$AZ7)))

Array2:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Data=T EXT(Sheet1!$AX7,0),ROW(
Data)+Array1,"")),IF(Data="",ROW(Data)),0))+0)

BA7:

=INDEX(ID,LARGE(IF(Array2=$AZ7,TRANSPOSE(ROW(ID)-MIN(ROW(ID))+1)),2)+$AZ7
)

....confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

In article <7901866312afb@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thank you for your time and assistance.

1) The Formula returns incorrect results with some #REF! errors. I think the
#REF! errors may be from =TRANSPOSE(ROW(INDIRECT("1:"&$AZ7))). Not sure?

2) Also, I think the Formula is trying to return the penultimate ID that
matches the specific TEXT criteria above the EmptyText rows. I require the
penultimate ID that matches first, Text criteria and then the specific number
of variable EmptyText rows below the Text criteria. The penultimate ID
returned should match with the last EmptyText row criteria.

Further assistance most appreciated.

Cheers,
Sam

Hi Domenic,

Thanks for further assistance.

Using your example below, my expected and required result would be ID 1307.
The number of EmptyText cell rows must be an explicit match. Thereby, a match,
if the number of EmptyText rows is an exact sequential match. If looking for
2 EmptyText cell rows then, there should be exactly 2 EmptyText rows below
the Text for it to be a match.

Cheers,
Sam

Domenic wrote:
Let's assume the following...

A2:A22 contains the ID, and named ID

B2:B22 contains the data, and named Data

AX7 contains the text value to match

AZ7 contains the the number of empty text values to match

A2:B22 contains the following...

1300 1
1301 Empty Text
1302 1
1303 2
1304 Empty Text
1305 1
1306 Empty Text
1307 Empty Text
1308 1
1309 Empty Text
1310 Empty Text
1311 Empty Text
1312 1
1313 Empty Text
1314 Empty Text
1315 1
1316 2
1317 Empty Text
1318 Empty Text
1319 Empty Text
1320 1

AX7 contains 1

AZ7 contains 2

So if we're trying to find the penultimate ID where the text value 1 is
followed by 2 empty text values, the formula should return 1310. If
this is correct, try the following...

No, using your example above, my expected and required result would be ID
1307.
The number of EmptyText cell rows must be an explicit match. Thereby, a match,
if the number of EmptyText rows is an exact sequential match.

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&Sheet1!$AZ7)))

Array2:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Data= TEXT(Sheet1!$AX7,0),ROW(
Data)+Array1,"")),IF(Data="",ROW(Data)),0))+0)

BA7:

=INDEX(ID,LARGE(IF(Array2=$AZ7,TRANSPOSE(ROW(ID )-MIN(ROW(ID))+1)),2)+$AZ7
)

...confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

--
Message posted via http://www.officekb.com

In that case, try the following instead...

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&Sheet1!$AZ7)))

Array2:

=ISNUMBER(MATCH(IF(Data=TEXT(Sheet1!$AX7,0),ROW(Da ta)+Array1,""),IF(Data=
"",ROW(Data)),0))+0

Array3:

=MMULT(Array2,ROW(INDIRECT("1:"&Sheet1!$AZ7))^0)

Array4:

=ISNA(MATCH(IF(Data=TEXT(Sheet1!$AX7,0),ROW(Data)+ Sheet1!$AZ7+1,""),IF(Da
ta="",ROW(Data)),0))

BA7:

=INDEX(ID,LARGE(IF(Array3=$AZ7,IF(Array4,ROW(ID)-MIN(ROW(ID))+1)),2)+$AZ7
)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <7903cd033b3d1@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thanks for further assistance.

Using your example below, my expected and required result would be ID 1307.
The number of EmptyText cell rows must be an explicit match. Thereby, a match,
if the number of EmptyText rows is an exact sequential match. If looking for
2 EmptyText cell rows then, there should be exactly 2 EmptyText rows below
the Text for it to be a match.

Cheers,
Sam

Hi Domenic,

Thank you for all your help.

Using the solution below:
1st cell returns #VALUE! error,
2nd cell #REF! error,
3rd cell #REF! error,
4th cell #NA error.

I've tried parts of the solution in separate cells:

Array 3 returns #VALUE! error.

Array3:
=MMULT(Array2,ROW(INDIRECT("1:"&Sheet1!$AZ7))^0 )

Formula returns #VALUE! error.
BA7:
=INDEX(ID,LARGE(IF(Array3=$AZ7,IF(Array4,ROW(ID )-MIN(ROW(ID))+1)),2)+$AZ7
)
...confirmed with CONTROL+SHIFT+ENTER

Further help appreciated.

Cheers,
Sam

Domenic wrote:
In that case, try the following instead...

Array1:
=TRANSPOSE(ROW(INDIRECT("1:"&Sheet1!$AZ7)))

Array2:

=ISNUMBER(MATCH(IF(Data=TEXT(Sheet1!$AX7,0),ROW(D ata)+Array1,""),IF(Data=
"",ROW(Data)),0))+0

Array3:

=MMULT(Array2,ROW(INDIRECT("1:"&Sheet1!$AZ7))^0 )

Array4:

=ISNA(MATCH(IF(Data=TEXT(Sheet1!$AX7,0),ROW(Data) +Sheet1!$AZ7+1,""),IF(Da
ta="",ROW(Data)),0))

BA7:

=INDEX(ID,LARGE(IF(Array3=$AZ7,IF(Array4,ROW(ID )-MIN(ROW(ID))+1)),2)+$AZ7
)

...confirmed with CONTROL+SHIFT+ENTER.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200710/1

Sample file sent...

In article <790dc05454656@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thank you for all your help.

Using the solution below:
1st cell returns #VALUE! error,
2nd cell #REF! error,
3rd cell #REF! error,
4th cell #NA error.

I've tried parts of the solution in separate cells:

Array 3 returns #VALUE! error.

Array3:
=MMULT(Array2,ROW(INDIRECT("1:"&Sheet1!$AZ7))^0 )

Formula returns #VALUE! error.
BA7:
=INDEX(ID,LARGE(IF(Array3=$AZ7,IF(Array4,ROW(ID )-MIN(ROW(ID))+1)),2)+$AZ7
)
...confirmed with CONTROL+SHIFT+ENTER

Further help appreciated.

Cheers,
Sam

Hi Domenic,

Thanks for sample file.

If I try to match zero (0) EmptyText rows Formula returns #REF! error.

Also range "Data" is dynamic with many rows and columns. ID is a single
column dynamic range.

Further help appreciated.

Cheers,
Sam

Domenic wrote:
Sample file sent...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200710/1

The error probably has to do with the reference for the appropriate
range within your named range 'Data', which spans a number of rows and
columns. So you'll need to replace the named range 'Data' in my
formula, which just refers to a single column, with the appropriate
reference for your worksheet.

You had mention that you will be copying the formula down the column.
If you want to pick up an new column each time the formula is copied to
the next cell below, I think you'll need to replace 'Data' in my formula
with INDEX(Data,0,ROWS(BA$7:BA7)). If, however, you want to pick up a
new column each time the formula is copied across instead of down,
replace 'Data' with INDEX(Data,0,COLUMNS($BA7:BA7)).

Does this help?

In article <790f6dd708df7@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thanks for sample file.

If I try to match zero (0) EmptyText rows Formula returns #REF! error.

Also range "Data" is dynamic with many rows and columns. ID is a single
column dynamic range.

Further help appreciated.

Cheers,
Sam

Hi Domenic,

I made what I thought to be the necessary changes (amended Array2 and Array4)
but getting #Value! error in 1st cell and #REF! error in 2nd cell.

Could you possibly provide a note of the necessary amendments.

Also, using sample file with single column of data to match zero (0)
EmptyText rows, Formula returns #REF! error.

Further assistance appreciated.

Cheers,
Sam

Domenic wrote:
The error probably has to do with the reference for the appropriate
range within your named range 'Data', which spans a number of rows and
columns. So you'll need to replace the named range 'Data' in my
formula, which just refers to a single column, with the appropriate
reference for your worksheet.

You had mention that you will be copying the formula down the column.
If you want to pick up an new column each time the formula is copied to
the next cell below, I think you'll need to replace 'Data' in my formula
with INDEX(Data,0,ROWS(BA$7:BA7)).

Yes, picking up new column each time the formula is copied to
the next cell below.

If, however, you want to pick up a
new column each time the formula is copied across instead of down,
replace 'Data' with INDEX(Data,0,COLUMNS($BA7:BA7)).

--
Message posted via http://www.officekb.com

At this point, I think it would be easier if you sent me a sample file
and I'll make the necessary adjustments. Otherwise, for clarity, you'll
have to recap where all the relevant data lies, provide the defined
names and their references, provide the actual formula you're using, and
specify in which cell the formula will first be entered.

In article <791bbc6834d38@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

I made what I thought to be the necessary changes (amended Array2 and Array4)
but getting #Value! error in 1st cell and #REF! error in 2nd cell.

Could you possibly provide a note of the necessary amendments.

Also, using sample file with single column of data to match zero (0)
EmptyText rows, Formula returns #REF! error.

Further assistance appreciated.

Cheers,
Sam

Hi Domenic,

Thank you. The file's linked to multiple files. I'll try and get sample file
to you tomorrow.
However, I may have to recap where all the data lies.

Cheers,
Sam

Domenic wrote:
At this point, I think it would be easier if you sent me a sample file
and I'll make the necessary adjustments. Otherwise, for clarity, you'll
have to recap where all the relevant data lies, provide the defined
names and their references, provide the actual formula you're using, and
specify in which cell the formula will first be entered.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200710/1

Hi Domenic,

Apologies for delay in replying to Post. Your time and help is very much
appreciated.

I will recap where all the data lies to clarify.

2 worksheets involved:
sheet 'Site Lond'!Data - houses the Text data from which to pull the results.
sheet 'Update Appraisal'! - sheet to return Formula results.

sheet 'Site Lond'! - 2 dynamic named ranges (ID & Data):
"ID" - single column, starts at row 12, column "A". Houses numeric values.
Refers To:
=OFFSET('Site Lond'!$A$12,0,0,COUNT('Site Lond'!$A:$A),1)

"Data" - fifty-one columns, starts at row 12, columns "H" to "BF". Houses
Text data.
Refers To:
=OFFSET('Site Lond'!$H$12:INDEX('Site Lond'!$H$12:$H$2000,MATCH(REPT("z",255),
'Site Lond'!$H$12:$H$2000)),0,0,51)

Layout for sheet 'Update Appraisal'! to return Formula results - the column
references are now:
Column AX7 - first column of Text Data to MATCH on sheet 'Site Lond'!Data
(fill down $AX7:$AX57)
Column AZ7 - return first result of formula (fill down $AZ7:$AZ57)
Column BB7 - first column of EmptyText to MATCH on sheet 'Site Lond'!Data
(fill down $BB7:$BB57)

Array1:
=TRANSPOSE(ROW(INDIRECT("1:"&'Update Appraisal'!$BB7)))

Array2:
=ISNUMBER(MATCH(IF('Site Lond'!Data=TEXT('Update Appraisal'!$AX7,0),
ROW('Site Lond'!Data!)+Array1,""),IF('Site Lond'!Data="",ROW('Site Lond'!Data!
)),0))+0

Array3:
=MMULT(Array2,ROW(INDIRECT("1:"&'Update Appraisal'!$BB7))^0)

Array4:
=ISNA(MATCH(IF('Site Lond'!Data=TEXT('Update Appraisal'!$AX7,0),ROW('Site
Lond'!Data)+
'Update Appraisal'!$BB7+1,""),IF('Site Lond'!Data="",ROW('Site Lond'!Data)),0)
)

In AZ7 first row of formula result:
=INDEX('Site Lond'!ID,LARGE(IF(Array3='Update Appraisal'!$BB7,
IF(Array4,ROW('Site Lond'!ID)-MIN(ROW('Site Lond'!ID))+1)),2)+'Update
Appraisal'!$BB7)

...confirmed with CONTROL+SHIFT+ENTER

I think the lengths of Array2 and Array4 have exceeded the character limit in
the Refers To box.

You had mention that you will be copying the formula down the column.
If you want to pick up an new column each time the formula is copied to
the next cell below, I think you'll need to replace 'Data' in my formula
with INDEX(Data,0,ROWS(BA$7:BA7)).

Yes, picking up new column from sheet 'Site Lond'!Data each time the result
formula on 'Update Appraisal'!$AZ7 is copied to the next cell below.

Cheers,
Sam

Sam wrote:
Hi Domenic,

Thank you. The file's linked to multiple files. I'll try and get sample file
to you tomorrow.
However, I may have to recap where all the data lies.

Cheers,
Sam

At this point, I think it would be easier if you sent me a sample file
and I'll make the necessary adjustments. Otherwise, for clarity, you'll
have to recap where all the relevant data lies, provide the defined
names and their references, provide the actual formula you're using, and
specify in which cell the formula will first be entered.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200710/1

It looks like there are a few problems with the references for your
defined names. The reference for Data needs to be corrected so that a
reference for 51 columns is returned. As it stands, it returns a 51 row
by 1 column reference.

Also, the references need to be adjusted to ensure that both arrays
returned by the references have the same number of rows. So change the
references for the defined names as follows...

ID:

=OFFSET('Site Lond'!$A$12,0,0,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536))

Data:

=OFFSET('Site Lond'!$H$12:INDEX('Site
Lond'!$H$12:$H$65536,MATCH(9.99999999999999E+307,' Site
Lond'!$A$12:$A$65536)),,,,51)

I haven't look further into this, but I think this should solve the
problem. If not, let me know and I'll continue to investigate.

Hope this helps!

In article <7930fcac716aa@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Apologies for delay in replying to Post. Your time and help is very much
appreciated.

I will recap where all the data lies to clarify.

2 worksheets involved:
sheet 'Site Lond'!Data - houses the Text data from which to pull the results.
sheet 'Update Appraisal'! - sheet to return Formula results.

sheet 'Site Lond'! - 2 dynamic named ranges (ID & Data):
"ID" - single column, starts at row 12, column "A". Houses numeric values.
Refers To:
=OFFSET('Site Lond'!$A$12,0,0,COUNT('Site Lond'!$A:$A),1)

"Data" - fifty-one columns, starts at row 12, columns "H" to "BF". Houses
Text data.
Refers To:
=OFFSET('Site Lond'!$H$12:INDEX('Site Lond'!$H$12:$H$2000,MATCH(REPT("z",255),
'Site Lond'!$H$12:$H$2000)),0,0,51)

Layout for sheet 'Update Appraisal'! to return Formula results - the column
references are now:
Column AX7 - first column of Text Data to MATCH on sheet 'Site Lond'!Data
(fill down $AX7:$AX57)
Column AZ7 - return first result of formula (fill down $AZ7:$AZ57)
Column BB7 - first column of EmptyText to MATCH on sheet 'Site Lond'!Data
(fill down $BB7:$BB57)

Array1:
=TRANSPOSE(ROW(INDIRECT("1:"&'Update Appraisal'!$BB7)))

Array2:
=ISNUMBER(MATCH(IF('Site Lond'!Data=TEXT('Update Appraisal'!$AX7,0),
ROW('Site Lond'!Data!)+Array1,""),IF('Site Lond'!Data="",ROW('Site Lond'!Data!
)),0))+0

Array3:
=MMULT(Array2,ROW(INDIRECT("1:"&'Update Appraisal'!$BB7))^0)

Array4:
=ISNA(MATCH(IF('Site Lond'!Data=TEXT('Update Appraisal'!$AX7,0),ROW('Site
Lond'!Data)+
'Update Appraisal'!$BB7+1,""),IF('Site Lond'!Data="",ROW('Site Lond'!Data)),0)
)

In AZ7 first row of formula result:
=INDEX('Site Lond'!ID,LARGE(IF(Array3='Update Appraisal'!$BB7,
IF(Array4,ROW('Site Lond'!ID)-MIN(ROW('Site Lond'!ID))+1)),2)+'Update
Appraisal'!$BB7)

..confirmed with CONTROL+SHIFT+ENTER

I think the lengths of Array2 and Array4 have exceeded the character limit in
the Refers To box.

You had mention that you will be copying the formula down the column.
If you want to pick up an new column each time the formula is copied to
the next cell below, I think you'll need to replace 'Data' in my formula
with INDEX(Data,0,ROWS(BA$7:BA7)).

Yes, picking up new column from sheet 'Site Lond'!Data each time the result
formula on 'Update Appraisal'!$AZ7 is copied to the next cell below.

Cheers,
Sam

Sam wrote:
Hi Domenic,

Thank you. The file's linked to multiple files. I'll try and get sample file
to you tomorrow.
However, I may have to recap where all the data lies.

Cheers,
Sam

At this point, I think it would be easier if you sent me a sample file
and I'll make the necessary adjustments. Otherwise, for clarity, you'll
have to recap where all the relevant data lies, provide the defined
names and their references, provide the actual formula you're using, and
specify in which cell the formula will first be entered.

Hi Domenic,

Thank you very much for reply.

Domenic wrote:
It looks like there are a few problems with the references for your
defined names. The reference for Data needs to be corrected so that a
reference for 51 columns is returned. As it stands, it returns a 51 row
by 1 column reference.

Typo, I missed a comma out in the Refers To box. The range does in fact
reference 51 columns.

Also, the references need to be adjusted to ensure that both arrays
returned by the references have the same number of rows. So change the
references for the defined names as follows...

ID:

=OFFSET('Site Lond'!$A$12,0,0,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536))

When I click on the formula in the Refers To box, the expected number of rows
are referenced. This matches the exact same number of rows as "Data".

Data:

=OFFSET('Site Lond'!$H$12:INDEX('Site
Lond'!$H$12:$H$65536,MATCH(9.99999999999999E+307, 'Site
Lond'!$A$12:$A$65536)),,,,51)

I previously tried the MATCH(9.99999999999999E+307 for the "Data" range but
it did not find my last row of Text.

When I click on the formula in the Refers To box, the expected number of rows
are referenced. This matches the exact same number of rows as "ID".

Will referencing all 65536 rows impact on the speed of the worksheet?

I haven't look further into this, but I think this should solve the
problem. If not, let me know and I'll continue to investigate.

Further assistance appreciated.

Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200710/1

In article <7933d3a7f2901@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thank you very much for reply.

Domenic wrote:
It looks like there are a few problems with the references for your
defined names. The reference for Data needs to be corrected so that a
reference for 51 columns is returned. As it stands, it returns a 51 row
by 1 column reference.

Typo, I missed a comma out in the Refers To box. The range does in fact
reference 51 columns.

Also, the references need to be adjusted to ensure that both arrays
returned by the references have the same number of rows. So change the
references for the defined names as follows...

ID:

=OFFSET('Site Lond'!$A$12,0,0,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536))

When I click on the formula in the Refers To box, the expected number of rows
are referenced. This matches the exact same number of rows as "Data".

Data:

=OFFSET('Site Lond'!$H$12:INDEX('Site
Lond'!$H$12:$H$65536,MATCH(9.99999999999999E+307, 'Site
Lond'!$A$12:$A$65536)),,,,51)

I previously tried the MATCH(9.99999999999999E+307 for the "Data" range but
it did not find my last row of Text.

Notice that the lookup range for MATCH is Column A, which contains
numerical values, not Column H. This ensures that the arrays for both
ID and Data contain the same number of rows.

When I click on the formula in the Refers To box, the expected number of rows
are referenced. This matches the exact same number of rows as "ID".

Will referencing all 65536 rows impact on the speed of the worksheet?

No, only the relevant data will be referenced and evaluated, not the
entire worksheet.

I haven't look further into this, but I think this should solve the
problem. If not, let me know and I'll continue to investigate.

Further assistance appreciated.

It looks like you'll have to adjust the references for the other defined
names which refer to Data. So, for example, if the first cell in which
the formula will be entered is BA7, first define the name Range as
follows...

=INDEX(Data,0,ROWS($BA$7:BA7))

Then replace any reference to Data or 'Site Lond'!Data with Range. See
if this helps. In the meantime, I'll continue investigating...

Also, make sure that you select BA7 (actually, I see that you'll now be
entering the formula in AZ7 and copying it down) first, before defining
your named ranges.

With the layout as you described in your previous post, try the
following...

1) Select 'Update Appraisal'!AZ7 and define the following:

ID:

=OFFSET('Site Lond'!$A$12,0,0,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536))

Data:

=OFFSET('Site Lond'!$H$12:INDEX('Site
Lond'!$H$12:$H$65536,MATCH(9.99999999999999E+307,' Site
Lond'!$A$12:$A$65536)),,,,51)

Range:

=INDEX(Data,0,ROWS('Update Appraisal'!$AZ$7:$AZ7))

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&'Update Appraisal'!$BB7)))

Array2:

=ISNUMBER(MATCH(IF(Range=LEFT('Update Appraisal'!$AX7,LEN('Update
Appraisal'!$AX7)),ROW(Range)+Array1,""),IF(Range=" ",ROW(Range)),0))+0

Array3:

=MMULT(Array2,ROW(INDIRECT("1:"&'Update Appraisal'!$BB7))^0)

Array4:

=ISNA(MATCH(IF(Range=LEFT('Update Appraisal'!$AX7,LEN('Update
Appraisal'!$AX7)),ROW(Range)+'Update
Appraisal'!$BB7+1,""),IF(Range="",ROW(Range)),0))

2) Then enter the following formula in 'Update Appraisal'!AZ7 and copy
down:

=INDEX(ID,LARGE(IF(Array3=$BB7,IF(Array4,ROW(ID)-MIN(ROW(ID))+1)),2)+$BB7
)

Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi Domenic,

I am now getting most of the expected ID results. However, I still have a
problem returning the correct ID result where zero "0" EmptyText rows needs
to be matched.

It looks like you'll have to adjust the references for the other defined
names which refer to Data. So, for example, if the first cell in which
the formula will be entered is BA7, first define the name Range as
follows...

=INDEX(Data,0,ROWS($BA$7:BA7))

Then replace any reference to Data or 'Site Lond'!Data with Range. See
if this helps.

This has helped.

Further help appreciated.

Cheers,
Sam

--
Message posted via http://www.officekb.com

Hi Domenic,

I have not tried the solution below as yet. We may be at cross-purposes.

The only problem with the existing solution is, if I wish to locate an
EmptyText row, that is; when the match of EmptyText is 0: there should be two
sequential row matches of the same TEXT.

For example, to find the ID of the penultimate EmptyText row of zero (0) for
TEXT "1" should return ID 1303.

Sample Data Layout:
ID "Col1"
1300 1
1301
1302 1
1303 1
1304
1305 1
1306
1307
1308 1
1309
1310 1
1311
1312 1
1313
1314
1315
1316 1
1317
1318
1319 1
1320 1

I am now getting most of the expected ID results. However, I still have a
problem returning the correct ID result where zero "0" EmptyText rows needs
to be matched.

Cheers,
Sam


Domenic wrote:
With the layout as you described in your previous post, try the
following...

1) Select 'Update Appraisal'!AZ7 and define the following:

ID:

=OFFSET('Site Lond'!$A$12,0,0,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536))

Data:

=OFFSET('Site Lond'!$H$12:INDEX('Site
Lond'!$H$12:$H$65536,MATCH(9.99999999999999E+307, 'Site
Lond'!$A$12:$A$65536)),,,,51)

Range:

=INDEX(Data,0,ROWS('Update Appraisal'!$AZ$7:$AZ7))

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&'Update Appraisal'!$BB7)))

Array2:

=ISNUMBER(MATCH(IF(Range=LEFT('Update Appraisal'!$AX7,LEN('Update
Appraisal'!$AX7)),ROW(Range)+Array1,""),IF(Range= "",ROW(Range)),0))+0

Array3:

=MMULT(Array2,ROW(INDIRECT("1:"&'Update Appraisal'!$BB7))^0)

Array4:

=ISNA(MATCH(IF(Range=LEFT('Update Appraisal'!$AX7,LEN('Update
Appraisal'!$AX7)),ROW(Range)+'Update
Appraisal'!$BB7+1,""),IF(Range="",ROW(Range)),0 ))

2) Then enter the following formula in 'Update Appraisal'!AZ7 and copy
down:

=INDEX(ID,LARGE(IF(Array3=$BB7,IF(Array4,ROW(ID )-MIN(ROW(ID))+1)),2)+$BB7
)

Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200710/1

To clarify, if the data contained the following...

ID "Col1"
1300 1
1301
1302 1
1303 1
1304
1305 1
1306 1
1307
1308 1
1309 1
1310 1
1311 1
1312 1
1313
1314
1315
1316 1
1317
1318
1319 1
1320

....what would you expect as the result?

Hi Domenic,

I see what you mean, a bit confusing. I did have to pause for thought. I
would expect ID 1311.

My qualification or rule of thumb for a penultimate zero (0) EmptyText row
was not made clear. I should have said AT LEAST 2 sequential or consecutive
TEXT rows. In your example, ID 1312 would be the LAST and ID 1311 would be
the penultimate.

Cheers,
Sam

Domenic wrote:

To clarify, if the data contained the following...
ID "Col1"
1300 1
1301
1302 1
1303 1
1304
1305 1
1306 1
1307
1308 1
1309 1
1310 1
1311 1
1312 1
1313
1314
1315
1316 1
1317
1318
1319 1
1320

...what would you expect as the result?

ID 1311

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200710/1

First, select AZ7 and define the following...

Range:

=INDEX(Data,0,ROWS('Update Appraisal'!$AZ$7:$AZ7))

Txt:

=LEFT('Update Appraisal'!$AX7,LEN('Update Appraisal'!$AX7))

Array1:

=IF('Update Appraisal'!$BB70,TRANSPOSE(ROW(INDIRECT("1:"&'Upd ate
Appraisal'!$BB7))),1)

Array2:

=ISNUMBER(MATCH(IF(Range=Txt,ROW(Range)+IF('Update
Appraisal'!$BB70,Array1,1),""),IF(Range=IF('Updat e
Appraisal'!$BB70,"",Txt),ROW(Range)),0))+0

Array3:

=MMULT(Array2,IF('Update Appraisal'!$BB70,ROW(INDIRECT("1:"&'Update
Appraisal'!$BB7))^0,1))

Array4:

=ISNA(MATCH(IF(Range=Txt,ROW(Range)+'Update
Appraisal'!$BB7+1,""),IF(Range="",ROW(Range),""),0 ))

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

AZ7, copied down:

=INDEX(ID,LARGE(IF(Array3=IF($BB70,$BB7,1),IF(IF( $BB70,Array4,1),ROW(ID
)-MIN(ROW(ID))+1)),2)+IF($BB70,$BB7,1))

Hope this helps!

In article <793d2271629ba@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

I see what you mean, a bit confusing. I did have to pause for thought. I
would expect ID 1311.

My qualification or rule of thumb for a penultimate zero (0) EmptyText row
was not made clear. I should have said AT LEAST 2 sequential or consecutive
TEXT rows. In your example, ID 1312 would be the LAST and ID 1311 would be
the penultimate.

Cheers,
Sam

Domenic wrote:

To clarify, if the data contained the following...
ID "Col1"
1300 1
1301
1302 1
1303 1
1304
1305 1
1306 1
1307
1308 1
1309 1
1310 1
1311 1
1312 1
1313
1314
1315
1316 1
1317
1318
1319 1
1320

...what would you expect as the result?

ID 1311

Hi Domenic,

That's done it. Absolutely Superb! Thank you very much for all your time and
patience.

Very much appreciated.

Cheers,
Sam

Domenic wrote:
First, select AZ7 and define the following...

Range:

=INDEX(Data,0,ROWS('Update Appraisal'!$AZ$7:$AZ7))

Txt:

=LEFT('Update Appraisal'!$AX7,LEN('Update Appraisal'!$AX7))

Array1:

=IF('Update Appraisal'!$BB70,TRANSPOSE(ROW(INDIRECT("1:"&'Upd ate
Appraisal'!$BB7))),1)

Array2:

=ISNUMBER(MATCH(IF(Range=Txt,ROW(Range)+IF('Updat e
Appraisal'!$BB70,Array1,1),""),IF(Range=IF('Upda te
Appraisal'!$BB70,"",Txt),ROW(Range)),0))+0

Array3:

=MMULT(Array2,IF('Update Appraisal'!$BB70,ROW(INDIRECT("1:"&'Update
Appraisal'!$BB7))^0,1))

Array4:

=ISNA(MATCH(IF(Range=Txt,ROW(Range)+'Update
Appraisal'!$BB7+1,""),IF(Range="",ROW(Range),""), 0))

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

AZ7, copied down:

=INDEX(ID,LARGE(IF(Array3=IF($BB70,$BB7,1),IF(IF ($BB70,Array4,1),ROW(ID
)-MIN(ROW(ID))+1)),2)+IF($BB70,$BB7,1))

Hope this helps!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200710/1