Hi Domenic,

Thank you for all your help.

Your Formula does provide the expected results for the first Row - Row8 on
the Sheet I refer to as NewSheet (for the expected results). However, I get
the REF Error message on all subsequent rows.

I may have confused the sheet names in the various formulas, so, I'll just
run through what I've done:

1) Select cell B8 = NewSheet - expected results

2) Define the following references (Insert Name Define):
Name: BigText
Refers to: =REPT("z",255)
Click Add - Defined as stated above

Name: Range
Refers to:
=Sheet1!$B$18:INDEX(Sheet1!$B$18:$B$65536,MATCH(Bi gText,Sheet1!$B$18:$B$65536)
)
Click Add - My data actually starts in Row no.19 but there are text labels in
Row no.18.

Name: NumCount
Refers to:
=COUNTIF(Range,Sheet1!$A8)+(Sheet1!$B$19<Sheet1!$ A8)
Click Ok - I've taken Sheet1 in the COUNTIF Range to mean my NewSheet with
the expected results.
And this (Sheet1!$B$19<Sheet1!$A8) - to mean (SourceDataSheet!$B
$19<NewSheet!$A8)

Name: Array1
Refers to:
=LARGE(IF((Range=Sheet1!$A8)+(ROW(Range)=IF(Sheet1 !$B$19<Sheet1!$A8,18,19)),
ROW(Range)),ROW(INDIRECT("2:"&NumCount)))
Click Add - Sheet1 reference to $A8 is my NewSheet with the expected results,
Sheet1 reference to $B$19 is my original Source Data.

Name: Array2
Refers to:
=LARGE(IF((Range=Sheet1!$A8)+(ROW(Range)=IF(Sheet1 !$B$19<Sheet1!$A8,18,19)),
ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))
Click Ok - Sheet1 reference to $A8 is my NewSheet with the expected results,
Sheet1 reference to $B$19 is my original Source Data.

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8 - completed as suggested.

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:
=TRANSPOSE(Array2-Array1)-1

I'm not sure what I may have done incorrectly? I do get the correct results
for the first row on the NewSheet.

Any further help very much appreciated.

Thanks
Sam

Domenic wrote:
Try the following...

1) Select cell B8

2) Define the following references (Insert Name Define):

Name: BigText

Refers to: =REPT("z",255)

Click Add

Name: Range

Refers to:
=Sheet1!$B$18:INDEX(Sheet1!$B$18:$B$65536,MATCH(B igText,Sheet1!$B$18:$B$6
5536))

Click Add

Name: NumCount

Refers to:
=COUNTIF(Range,Sheet1!$A8)+(Sheet1!$B$19<Sheet1! $A8)

Click Ok

Name: Array1

Refers to:
=LARGE(IF((Range=Sheet1!$A8)+(ROW(Range)=IF(Sheet 1!$B$19<Sheet1!$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("2:"&NumCount)))

Click Add

Name: Array2

Refers to:
=LARGE(IF((Range=Sheet1!$A8)+(ROW(Range)=IF(Sheet 1!$B$19<Sheet1!$A8,18,1
9)),ROW(Range)),ROW(INDIRECT("1:"&NumCount-1)))

Click Ok

3) Select/highlight the range of cells in row 8 that will contain the
results, for example, B8:AE8

4) With the range of cells selected/highlighted, enter the following
formula and confirm with CONTROL+SHIFT+ENTER:

=TRANSPOSE(Array2-Array1)-1

5) Copy the formula to other rows. To do so, select B8:AE8 first, then
Copy/Paste.

6) Use conditional formatting to hide the error values that will occur:

a) Select B8

b) Format Conditional Formatting Formula Is

c) Enter the following formula:

=ISERROR(B8)

d) Choose 'White' as your font

e) Click Ok

f) Copy the format to other cells using the 'Format Painter' or 'Copy
Paste Special Formats'.

Hope this helps!

Hi All,

[quoted text clipped - 81 lines]
Thanks
Sam


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