Search results

1. Formula Challenge 008 - return distinct/duplicate /unique strings as an array

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...
2. Formula Challenge 017 – Conditional Unique Nth Value

@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...
3. Formula Challenge 017 – Conditional Unique Nth Value

Hi Jeff =LARGE(IFERROR(IF(MATCH(l&d,i&d,)=ROW(i),d),0),n) - 51 Characters Cheers Sam
4. Formula Challenge 008 - return distinct/duplicate /unique strings as an array

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
5. Formula Challenge 013 - Turn non-contiguous cells into a 1D range

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)
6. Formula Challenge 012 - Turn a mixed 2-d array into a 1-d array.

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)
7. Formula Challenge 012 - Turn a mixed 2-d array into a 1-d array.

@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,&#34;a&#34;,&#34;b&#34;,&#34;c&#34;,4,5,6,&#34;d&#34;,&#34;e&#34;,&#34;f&#34;} How I wish Data...
8. Formula Challenge 012 - Turn a mixed 2-d array into a 1-d array.

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
9. Formula Challenge 006 - What's the largest number you can confirm is a prime?

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.....:-)
10. Formula Challenge 009 -The Difference between the Latest and Earliest instance

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...
11. Formula Challenge 009 -The Difference between the Latest and Earliest instance

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...
12. Formula Challenge 009 -The Difference between the Latest and Earliest instance

@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...
13. Formula Challenge 009 -The Difference between the Latest and Earliest instance

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...