#num!...

[How_Do_I]

Can anyone see what is going wrong here please?

Excel Workbook
	B	C	D	E	F	G	H	I	J
1	HT	H	A	FT		Count	7
2	169	100	75	40		Large	169	100	75
3	82	0	1	49			169	99	5
4	24	0	2	20			82	#NUM!	#NUM!
5	3	0	3	6			58	#NUM!	#NUM!
6	2	0	4	5			24	#NUM!	#NUM!
7	0	0	5	1			3	#NUM!	#NUM!
8	0	0	6	1
9	169	99	5	57
10	58	1	1	86
Sheet1

 

[Special-K99]

Isnt it the column part of your INDEX?
Arent H, HT,A and FT all anges that are 1 column wide?
Yet your formula in I2, J2 and downthose columns, ie INDEX(H... ROWS(I$2,I2))
if its greater than 1 produces a #NUM error

 

[How_Do_I]

I don't understand what you mean, sorry...

 

[jasonb75]

Isnt it the column part of your INDEX?
Arent H, HT,A and FT all anges that are 1 column wide?
Yet your formula in I2, J2 and downthose columns, ie INDEX(H... ROWS(I$2,I2))
if its greater than 1 produces a #NUM error

This needs to count the occurrences of each value in HT, the original formula will work for repeated instances of the largest value in the array (try changing other values in column B to 169 and this will become more obvious), once the value in HT to be evaluated changes the formula fails.

These revied formulae should do the trick, note that I've removed = from the H2 formula as it was blanking the last result.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">169</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">100</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">75</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H2</TD><TD>=IF(ROWS(H$2:H2)>$H$1,"",LARGE($B$2:$B$35,ROWS(H$2:H2)))</TD></TR><TR><TD>I2</TD><TD>{=IF(ROWS(I$2:I2)<=$H$1,INDEX(H,SMALL(IF(HT=$H2,ROW(HT)-ROW($B$2)+1),COUNTIF($H$2:H2,H2))),"")}</TD></TR><TR><TD>J2</TD><TD>{=IF(ROWS(J$2:J2)<=$H$1,INDEX(A,SMALL(IF(HT=$H2,ROW(HT)-ROW($B$2)+1),COUNTIF($H$2:H2,H2))),"")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

 

[How_Do_I]

That is strange, I was thinking about people who have helped me with this project last night and I did think I haven't seen Jason for a while...

Thanks for that, I hope things are well with you.