Search results

Hi All, I recently had to make use of the formula in Challenge008 – Return Usable list of Distinct items =T(OFFSET(A1,SMALL(IF(MATCH(t,t,)=ROW(t),ROW(t)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(t,t)))))-1,)) It works fine in most cases but fails due to the sensitivity of the OFFSET's Integer...

@Haseeb, That is brilliant My attempt with AGGREGATE that I had e-mailed to Jeff was as below =AGGREGATE(14,6,(MATCH(l&d,i&d,)=ROW(i))*d,n) (45 Characters) - Of course needing a Row(i)-Min(Row(i)+1 to make it work for any range and increasing the length to 59...

Hi Jeff =LARGE(IFERROR(IF(MATCH(l&d,i&d,)=ROW(i),d),0),n) - 51 Characters Cheers Sam

Hi Jeff, Here is my take on returning a “usable” list of Distinct items in a list – 93 Characters !! =T(OFFSET(A1,SMALL(IF(MATCH(t,t,0)=ROW(t),ROW(t)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(t,t)))))-1,)) Cheers Sam

Jeff, The below formula can be array entered across 6 cells - 157 Characters =INDEX((x,y,z),1,IFERROR(1/MOD(COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))),2),2), (MOD(COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))),2)+COLUMN(OFFSET(A1,,,,COUNTA(x,y,z))))/2)

Oops...Cant Hard code the 2 =OFFSET(a,TRUNC((COLUMN(OFFSET(A1,,,,COLUMNS(a)*ROWS(a)))-1)/COLUMNS(a)),MOD(COLUMN(OFFSET(A1,,,,COLUMNS(a)*ROWS(a)))-1,COLUMNS(a)),1,1)

@Jeff A more Generic Formula for any X, Y grid =OFFSET(a,TRUNC((COLUMN(OFFSET(A1,,,,COLUMNS(a)*2))-1)/COLUMNS(a)),MOD(COLUMN(OFFSET(A1,,,,COLUMNS(a)*2))-1,COLUMNS(a)),1,1) Returns ={1,2,3,"a","b","c",4,5,6,"d","e","f"} How I wish Data...

Jeff Dont tell me you managed to shorten this further ! =CHOOSE({1;2},INDEX(a,1,),INDEX(a,2,)) - Will maintain the Datatype of the original data or =REPT(List1,{1;0})&REPT(List2,{0;1}) - But will return the numbers as strings sam

Folks, I dont want to steal Jeff's thunder by posting the solution...but I am sure he wont mind if I give a small hint - No Rows*Tranpose(No Rows) is a very Large number.....:-)

Hi Sajan I just wanted the difference - Negative values are fine - so ABS is not required. Sorry if my initial post was not very clear about this. Jeff, The Lookup Function - Always performs a binary search and needs data to be sorted and behaves like the 1 option of match The only...

Sajan...Wow... This was my formula with 62 characters array entered =SUM(N(OFFSET(B1,MATCH({1,2},1/(A2:A30=D1),{-1,1}),))*{-1,1}) Do you know of a faster way of producing the array of {#DIV/0! and 1} that we get from 1/(A2:A30=D1). If we can find a faster way of producing this array...

@Sandeep : If I put back range references in to your formula it becomes =ABS(LOOKUP(9^9,SEARCH(D1,A1:A30),B1:B30)-VLOOKUP(D1,A1:B30,2,0)) it becomes 65 Character @Dan : If I replace whole column references with data ranges =INDEX(B1:B30,MAX((A1:A30=D1)*ROW(A1:A30)))-VLOOKUP(D1,A1:B30,2,0) it...

Client Amt IBM 49 TYCO 46 E&Y 82 ORACLE 88 TVS 24 BHEL 79 ABB 29 ORACLE 89 CGL 95 AREVA 49 ABB 63 E&Y 58 IBM 61 AREVA 81 ORACLE 13 ORACLE 100 TYCO 89 CGL 22 SIEMENS 10 ACES 21 AREVA 29 TVS 78 ACES 86 SIEMENS 45 TVS 16 BHEL 69 BHEL 43 IBM 56 ABB 56 Column A contains Clients in Random order...