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

Search results

  1. A

    Search and move data to other sheet

    Dear Aamir See if the attached file help. I have added a helper column to simplify the formula. Regards Amritansh
  2. A

    Calculate total working hours based rule

    Hi Could you please explain how the first week working hours comes to 147? Regards Amritansh
  3. A

    Pull data from most recent of record with same values

    Please use the below array formula INDEX($D$2:$D$15,MATCH(MAX(--($C$2:$C$15=C2)*($B$2:$B$15)),--($C$2:$C$15=C2)*($B$2:$B$15),0)) Press Ctrl+Shift+Enter after entering the formula.
  4. A

    Pull data from most recent of record with same values

    Dear Hayley Please share the sample data. Amritansh
  5. A

    Find Top 3 Applications using formula

    Hi Ajoshi Can you use helper columns? Amritansh
  6. A

    Reverse HLOOKUP [SOLVED]

    Hi The following formula can be used for exact match. =INDEX($A$2:$H$2,0,MAX(--($A$1:$H$1="Raj")*COLUMN($A$1:$H$1))) The data is arranged as follws: 'A1:H1 - Aman Raj Akshay Raj Amit Raj Rajsawant Akshay 'A2:H2 - 5 6 7 2 5 7 9 11 Amritansh
  7. A

    Chart Range to be fixed

    Hi Khushboo Please see the below link. http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/ Amritansh
  8. A

    Making a Mortgage Calculator

    Hi hjane This can be done in one sheet using dependent validation list. Could you please give more details how you want the sheet to look or better if you can share a sample worksheet. Amritansh
  9. A

    Formula to extract only numbers from a cell

    Hi mohammedkhan I have entered the data provided by you in range A1:A4. In the range E1:E10 enter numbers 0,1,2,3,4,5,6,7,8,9. In the range F1:F26 enter alphabets a,b,c....z. Enter the formula in B1 = MIN(IFERROR(FIND($E$1:$E$10,A1),100000)) and press Ctrl+Shift+Enter. In C1 =...
  10. A

    find out the names alone from a line

    Hi Suresh Looking at the number of posts from you it does not seem to be so simple, but in case the data is in single cell and the format remains tha same, you can use this in B column, assuming your data is in column A. =LEFT(A1,FIND("working",A1)-2)
  11. A

    If aused several times

    Hi Len Could you please tell on what logic you are getting the values in Col D? Amritansh
  12. A

    Stock Movements

    Hi DJW According to my understanding you are trying to move the stock from location A200 to A100. So you do as follows: A200 -100 A100 +100 Instead of this, to capture the information in single line, you can do as follows: Source Destination Qty A200 A100 100 Hope...
  13. A

    extract number with decimal from a sentence or words

    Hi jeevankg09 concrete pouring for 1 part (3.4m3) concrete pouring for 2 part (4.1m3) Can there be only 2 lines in a cell or more? Amritansh
  14. A

    Highlight Duplicate Values

    Hi Jitendra Use the following formula in Conditional formatting. =COUNTIF($A$1:$A$34,A1)>1 Amritansh
  15. A

    Look Up with multiple conditions with two sets of date ranges

    Thanks for the explanation Luke.. :)
  16. A

    Look Up with multiple conditions with two sets of date ranges

    Hi Luke I am unable to understand what I did wrong :o Since MAX will return the single value, why have you used the SUMPRODUCT? Amritansh
  17. A

    Look Up with multiple conditions with two sets of date ranges

    Hi Emo Try the modified version of formula from Luke. = MAX(--(Sheet2!A$2:A$20=A2)*--(Sheet2!B$2:B$20<=B2)*--(Sheet2!C$2:C$20>=C2)*(Sheet2!D$2:D$20)) Amritansh
  18. A

    Look Up with multiple conditions with two sets of date ranges

    Hi Emo Please name the columns in the Data Table as follows using the Name Manager. Items, Start_Date, End_Date and Discount_Amount Put the following formula in the File Needed To Populate. =sumproduct(--(Items=A2),--(Start_Date=B2),--(End_Date=C2),Discount_Amount) The data has been...
  19. A

    Can someone help me understand this formula please?

    Hi Excel_Kid_663 Your understanding for the use of '+' sign is absolutely correct. It is being used to evaluate OR condition. The -- is being used to convert the array returned as True/False to 1/0. Hope the explaination helps. Amritansh
  20. A

    Find 1st, 2nd, 3rd values from a list and return adjacent cell

    Hi Lee In the column D, I have put data as follows: D 1 2 3 The formula in column E is =INDEX($A$1:$A$6,MATCH(LARGE($B$1:$B$6,D1),$B$1:$B$6,0)) The range A1:B6 contains the data provided by you. Amritansh
  21. A

    Conditional Formatting Formula help

    Hi Daniel Can you please try the below formula in CF. If your data is in range A1:A10, use the following formula =A1>=average(A1:A10)+30000 and =A1<=average(A1:A10)-30000 Amritansh
  22. A

    SUMPRODUCT question

    Hi 3G Can you try this formula in Column C. =IF((IFERROR(SEARCH("Software",B2),0)*(A2="Approved"))>0,"TRUE","FALSE") Amritansh
  23. A

    Copy/extract text from a cell based on defined keywords

    Hi Tango From your post, it is not clear what you want to achieve. Can you please tell on what basis you are extracting the words from strings. Amritansh
  24. A

    List/Drop downs and using a custom formula

    Hi mmaffe23 You can use the if formula in the Data Validation->List. For eg. Name the ranges as A and B. Then the formula goes as =if(Cell = "International",A,B) Amritansh
  25. A

    VLOOKUP Returning Multiple Values

    Hi Mike I was unable to check your file as I am in office and file sharing services are blocked here. But just wanted to suggest you can use a helper column year in the sheet and use pivot to achieve what you want. Amritansh
Back
Top