• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recent content by Kevin@Radstock

  1. K

    Filter with three criteria

    Hi What version of excel are you using, if you have 365 or 2019, take advantage of DA's =FILTER(B17:P30,(H17:H30=B11)*(F17:F30=B13)*(I17:I30=B15),"") format "General;;" or for 4 columns =FILTER(CHOOSE({1,2,3,4},C17:C30,F17:F30,H17:H30,I17:I30),(H17:H30=B11)*(F17:F30=B13)*(I17:I30=B15))
  2. K

    How to generate numbers 0,1,2,3 in adjacent cells

    Hi If you have office 365. For the horizontal numbers: =SEQUENCE(,8,0,1) For the vertical numbers: =SEQUENCE(11,,0,1)
  3. K

    IF condition is not met, then check on next row

    Hi If you do away with columns C & D, you can use the following. =FILTER(A3:A10&"-"&B3:B10,A3:A10="Apples")
  4. K

    24 hour format time, calculate total hours

    Hi Try the following in F14 =E14-D14+(E14<D14)
  5. K

    Convert into figure

    Use Text to Columns on the Data Tab
  6. K

    Array function

    Maybe a bit faster and non CSE! =IFERROR(INDEX($A$2:$A$199,AGGREGATE(15,6,ROW($A$2:$A$199)-ROW($A$1)/($B$2:$B$199=E$1),ROWS(E$2:E2))),"")
  7. K

    Round a date to previous Saturday

    Or another solution is using the FLOOR function. =FLOOR(A1+7,7)-7
  8. K

    Multiple Value Look up

    Hi nishad See the attached file or look into pivot tables.
  9. K

    Work days formula

    Perhaps you need to post the version of excel you are using, if you are using Excel 2010>. The you can use the NETWORKDAYS.INTL function. =NETWORKDAYS.INTL(B3,B23,"0000001")
  10. K

    Formula to convert text hh:mm:ss to time

    Perhaps
  11. K

    Flip names [SOLVED]

    Hi Try the following, assuming the data is in A1. =RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(",",A1)-1)
  12. K

    Need help extracting numbers from a string that has mix of numbers and letters.

    Hi Try the following. In B1:=LOOKUP(20^20,1*MID(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))) and copy down. Kevin
  13. K

    Array for counting total number of items in list, less duplicates, by month

    Hi Absolution Assuming your data is in A1:B10 including the column headers. Then to retrieve the unique months, in say D2: =IFERROR(INDEX($B$2:$B$10,MATCH(0,INDEX(COUNTIF($D$1:D1,$B$2:$B$10),0,0),0)),"") And copy down. Then in E2 to retrieve the number of the unique values per month...
  14. K

    HOW TO SEPARATE TEXT AND NUMBERS ?

    @ bobhc Perhaps something like this, in A1: BLR5658DTDC6785DDSWL =SUM(MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1)) CTRL + SHIFT + ENTER
  15. K

    HOW TO SEPARATE TEXT AND NUMBERS ?

    Hi Suri M Assuming your data is in A1:A2. In B1 and copy down this non array formula to extract the numbers. =LOOKUP(20^20,1*MID(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1))))) Then if you require the text values, in C1 & copy down...
See more
Back
Top